问题描述
大家好，
我有一张桌子，其中有一些乐队被定义为
Hi all,
I have a table with some bands defined like
BandID | ScoreFrom | ScoreTo
---------------------------------------
1 | 0 | 95
2 | 96 | 97
3 | 98 | 100
我想添加一个新的分数范围和一些验证，比如它们不应该与现有范围重叠。
与添加 ScoreFrom 97和 ScoreTo 100时一样，不应该允许添加它。
同时在更新分数范围时，它应该允许 ScoreFrom 和 ScoreTo 以某种方式使范围不重叠。
请帮忙。
I would like to add a new score range with some validations like they should not get overlapped with existing ranges.
Like when adding ScoreFrom 97 and ScoreTo 100 it shouldn't allow it to be added.
Also while updating score range it should allow ScoreFrom and ScoreTo in a way so that the range not gets overlapped.
Please help.
推荐答案
CREATE PROCEDURE InsertScore
@newScoreFromValue INT,
@newScoreToValue INT
AS
BEGIN
INSERT INTO score s1 (s1.ScoreFrom, s1.ScoreTo) VALUES (@newScoreFromValue, @newScoreToValue)
WHERE @newScoreFromValue < @newScoreToValue AND NOT EXISTS
(
SELECT * FROM score s2 WHERE s2.ScoreTo >= @newScoreFromValue
)
END
如需更新，如果我理解你的问题，有3个为了不重叠而需要考虑的标准：
For updating, if I understand your question correctly, there are 3 criteria to consider in order not to "overlap":
CREATE PROCEDURE UpdateScore
@BandID INT,
@newScoreFromValue INT,
@newScoreToValue INT
AS
BEGIN
UPDATE score s1 SET s1.ScoreFrom = @newScoreFromValue, s1.ScoreTo = @newScoreToValue
WHERE s1.BandID = @BandID AND @newScoreFromValue < @newScoreToValue
AND NOT EXISTS
(
SELECT * FROM score s2 WHERE s2.BandID <> @BandID AND s2.ScoreFrom BETWEEN @newScoreFromValue AND @newScoreToValue
)
AND NOT EXISTS
(
SELECT * FROM score s3 WHERE s3.BandID <> @BandID AND s3.ScoreTo BETWEEN @newScoreFromValue AND @newScoreToValue
)
AND NOT EXISTS
(
SELECT * FROM score s4 WHERE s4.BandID <> @BandID AND s4.ScoreFrom <= @newScoreFromValue AND s4.ScoreTo >= @newScoreToValue
)
END
希望它有效。如果没有，您可能需要调整和修改。
Hope it works. If not, you may want to adapt and modify.
这篇关于如何允许在SQL中添加或更新分数范围;这样新添加或更新的分数范围不会与现有分数范围重叠。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！