SQL Server：如果存在；别的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7425146/
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 Server: IF EXISTS ; ELSE
提问by Bhupinder Singh
I have a tableA:
我有一个表A：
ID value
1 100
2 101
2 444
3 501
Also TableB
还有表B
ID Code
1
2
Now I want to populate col = code of table B if there exists ID = 2 in tableA. for multiple values , get max value. else populate it with '123'. Now here is what I used:
现在，如果 tableA 中存在 ID = 2，我想填充表 B 的 col = 代码。对于多个值，获取最大值。否则用'123'填充它。现在这是我使用的：
if exists (select MAX(value) from #A where id = 2)
BEGIN
update #B
set code = (select MAX(value) from #A where id = 2)
from #A
END
ELSE
update #B
set code = 123
from #B
I am sure there is some problem in BEGIN;END or in IF EXIST;ELSE. Basically I want to by-pass the else part if select statement in IF-part exist and vice- versa. For example if select statement of IF=part is:
我确信 BEGIN;END 或 IF EXIST;ELSE 中存在一些问题。基本上，如果 IF 部分中的 select 语句存在，我想绕过 else 部分，反之亦然。例如，如果 IF=part 的 select 语句是：
(select MAX(value) from #A where id = 4)
It should just populate 123, coz ID = 4 do not exist !
它应该只填充 123，因为 ID = 4 不存在！
回答by Derek Kromm
EDIT
编辑
I want to add the reason that your IFstatement seems to not work. When you do an EXISTSon an aggregate, it's always going to be true. It returns a value even if the IDdoesn't exist. Sure, it's NULL, but its returning it. Instead, do this:
我想补充一下你的IF陈述似乎不起作用的原因。当你EXISTS在一个聚合上做 an时，它总是会是true. 即使ID不存在，它也会返回一个值。当然，它是NULL，但它返回了它。相反，请执行以下操作：
if exists(select 1 from table where id = 4)
and you'll get to the ELSEportion of your IFstatement.
你会看到ELSE你的IF陈述部分。
Now, here's a better, set-based solution:
现在，这是一个更好的基于集合的解决方案：
update b
set code = isnull(a.value, 123)
from #b b
left join (select id, max(value) from #a group by id) a
on b.id = a.id
where
b.id = yourid
This has the benefit of being able to run on the entire table rather than individual ids.
这样做的好处是能够在整个表上运行，而不是在单个 id 上运行。
回答by Charles Bretana
Try this:
尝试这个：
Update TableB Set
Code = Coalesce(
(Select Max(Value)
From TableA
Where Id = b.Id), 123)
From TableB b
回答by HBSixtySix
I know its been a while since the original post but I like using CTE's and this worked for me:
我知道距离最初的帖子已经有一段时间了，但我喜欢使用 CTE，这对我有用：
WITH cte_table_a
AS
(
SELECT [id] [id]
, MAX([value]) [value]
FROM table_a
GROUP BY [id]
)
UPDATE table_b
SET table_b.code = CASE WHEN cte_table_a.[value] IS NOT NULL THEN cte_table_a.[value] ELSE 124 END
FROM table_b
LEFT OUTER JOIN cte_table_a
ON table_b.id = cte_table_a.id
