使用 LEFT JOIN 和 CASE 语句的 SQL QUERY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14732500/
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 QUERY using LEFT JOIN and CASE Statement
提问by StackOverflowUser
here is an example of my two table.
这是我的两个表的示例。
PROBLEM:How can I create SQL Query using left join?
问题：如何使用左连接创建 SQL 查询？
HERE IS THE SCENARIO
这是场景
As I've said earlier, I have two table (TABLE1 and TABLE2), I tried to use left join so that i can combine both UserID in one table
正如我之前所说，我有两个表（TABLE1 和 TABLE2），我尝试使用左连接，以便我可以将两个 UserID 合并到一个表中
so here is the code
所以这是代码
select * from table1 a left join table2 on a.userid = b.userid
so two tables are now combined.
所以现在合并了两个表。
what i need to do is this:
if the status is all completethen 'complete'
then if status contains complete and incompletethen 'incomplete'
else 'no status'
我需要做的是：
如果状态全部完成然后“完成”
然后如果状态包含完整和不完整然后“不完整”
否则“没有状态”
it should be look like this.
它应该是这样的。
NOTE:
since UserID = 1(table1) contains complete and incompletestatus (table2)
then it display 'incomplete'(new column)
since UserID = 4(table1) contains all completestatus (table 2)
then it display 'completed'(new column)
注意：
因为UserID = 1(table1) 包含完整和不完整状态 (table2)
然后它显示“不完整”（新列）
因为UserID = 4（table1）包含所有完整状态（表 2）
然后它显示“已完成”（新列）柱子）
-----------------------------------
---------------------
WHAT IF I CHANGE THE STATUS TO INTEGER?
如果我将状态更改为整数会怎样？
same procedure. thanks
相同的程序。谢谢
回答by John Woo
SELECT a.*,
CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
WHEN totalCount IS NULL THEN ''
ELSE 'Incomplete'
END STatus
FROM table1 a
LEFT JOIN
(
SELECT UserID,
COUNT(DISTINCT STATUS) totalCount,
SUM(CASE WHEN status = 'Incomplete' THEN 1 ELSE 0 END) totalINC
FROM table2
GROUP BY UserID
) b ON a.UserID = b.UserID
UPDATE 1
更新 1
the only thing you'll change is the CASE
你唯一会改变的是 CASE
SELECT a.*,
CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
WHEN totalCount IS NULL THEN ''
ELSE 'Incomplete'
END STatus
FROM table1 a
LEFT JOIN
(
SELECT UserID,
COUNT(DISTINCT STATUS) totalCount,
SUM(CASE WHEN status <> 100 THEN 1 ELSE 0 END) totalINC
FROM table2
GROUP BY UserID
) b ON a.UserID = b.UserID;
回答by Rapha?l Althaus
Easy, but tricky solution :
简单但棘手的解决方案：
as INCOMPLETE is greater (for a db) than COMPLETE, you can simply do
因为 INCOMPLETE 比 COMPLETE 更大（对于 db），你可以简单地做
SELECT a.UserID,
LOWER(COALESCE(MAX(b.status) , 'NO STATUS'))
FROM table1 a
LEFT JOIN table2 b on a.userid = b.userid
GROUP BY a.UserID
SqlFiddle(with Andomar's better solution as well)
SqlFiddle（还有 Andomar 更好的解决方案）
回答by Andomar
select a.UserID
, case
when sum(case when b.status = 'Incomplete' then 1 end) > 0
then 'Incomplete'
when sum(case when b.status = 'Complete' then 1 end) > 0
then 'Complete'
else 'No Status'
end
from table1 a
left join
table2 b
on a.userid = b.userid
group by
a.UserID
