sql 多 case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7123704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql Multiple case Statement
提问by anna
Hi I'm trying to write multiple case statements to preset the priority of a job. There are a few tables and ID s that are involved in this but basically I want to check whether the job exists if it does if the questionid related to it is 80 and then have multiple case statemnts so that if the jobs minor category is 1 then the answer will be the id for High. The code i ve done so far may explain better...
嗨，我正在尝试编写多个 case 语句来预设作业的优先级。这涉及到一些表和 ID，但基本上我想检查工作是否存在，如果与它相关的问题 ID 是 80，然后有多个案例陈述，那么如果工作次要类别是 1，那么答案将是 High 的 id。到目前为止我所做的代码可能解释得更好......
ALTER Procedure [dbo].[usp_CreatePresetPriority]
@HelpdeskID int,
@MinorCategoryID int
As
BEGIN
IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
BEGIN
UPDATE TicketInformation
SET AnswerInput = Null,
AnswerID = CASE @MinorCategoryID WHEN 87 THEN 129 END
WHERE TicketID = @HelpdeskID And QuestionID = 80
END
ELSE
BEGIN
INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 87 THEN 129 END, Null)
END
So this works but only for one option - where @MinorCategoryID = 87 I want to have more than 1 statements that set @MinorCategoryID WHEN 91THEN 130 etc...
所以这有效，但仅适用于一个选项 - 其中 @MinorCategoryID = 87 我想要有超过 1 个语句设置 @MinorCategoryID WHEN 91THEN 130 等...
I have tried...
我试过了...
IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
UPDATE TicketInformation SET AnswerInput = Null, AnswerID = CASE @MinorCategoryID WHEN 87 THEN 129 WHERE TicketID = @HelpdeskID And QuestionID = 80
ELSE INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput) VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 87 THEN 129 END, Null)
IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
UPDATE TicketInformation SET AnswerInput = Null, AnswerID = CASE @MinorCategoryID WHEN 91 THEN 130 WHERE TicketID = @HelpdeskID And QuestionID = 80
ELSE INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput) VALUES (@HelpdeskID, 80, CASE @MinorCategoryID WHEN 91 THEN 130 END, Null)
Any help appreciated - sorry if its not clear i've got a friday fuzzy head!
任何帮助表示赞赏 - 对不起，如果不清楚我有一个星期五模糊的头！
回答by Carlos Quintanilla
You can assign it to a variable and then just insert the variable.
您可以将它分配给一个变量，然后只需插入该变量。
ELSE
BEGIN
SELECT @MinorCategoryID = CASE
WHEN @MinorCategoryID = 87 THEN 129
WHEN @MinorCategoryID = 91 THEN 130
-- more cases here
ELSE NULL
END
INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
VALUES (@HelpdeskID, 80, @MinorCategoryID, Null)
END
or change the values() by a select
或通过选择更改 values()
ELSE
BEGIN
INSERT TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
SELECT @HelpdeskID,
80,
CASE
WHEN @MinorCategoryID = 87 THEN 129
WHEN @MinorCategoryID = 91 THEN 130
-- more cases here
ELSE NULL
END,
Null
END
回答by James Johnson
Give this a try:
试试这个：
CASE WHEN @MinorCategoryID = 87 THEN 129 ELSE NULL END
Obviously, replace the NULL in the ELSE case with another value if needed.
显然，如果需要，将 ELSE 情况下的 NULL 替换为另一个值。
回答by Brent
Try creating a list table for those possible scenarios. So when new cases pop up all you have to do is update that table.
尝试为那些可能的场景创建一个列表。因此，当新案例出现时，您所要做的就是更新该表。
Here is the code
这是代码
if(OBJECT_ID('_MinorCategoryList') > 0)
drop table _MinorCategoryList
create table _MinorCategoryList(
id int identity(1,1),
[when] int,
[then] int
)
insert into _MinorCategoryList ([when],[then])
values (87,129),(91,130)
select * from _MinorCategoryList
go
alter Procedure [dbo].[usp_CreatePresetPriority]
@HelpdeskID int,
@MinorCategoryID int
As
BEGIN
IF EXISTS(SELECT * FROM TicketInformation WHERE TicketID = @HelpdeskID AND QuestionID = 80)
BEGIN
UPDATE TicketInformation
SET AnswerInput = Null,
AnswerID = (SELECT [then] FROM _MinorCategoryList WHERE [when] = @MinorCategoryID)
WHERE TicketID = @HelpdeskID And QuestionID = 80
END
ELSE
BEGIN
INSERT INTO TicketInformation (TicketID, QuestionID, AnswerID, AnswerInput)
VALUES (@HelpdeskID, 80, (SELECT [then] FROM _MinorCategoryList WHERE [when] = @MinorCategoryID), Null)
END
END
go
