SQL 如何在同一个表中以不同的条件检索同一列两次？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16423883/
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
How to retrieve same column twice with different conditions in same table?
提问by chitrakant sahu
This is my table:
这是我的表：
Anganbadi_ID Food Month
-------------------------------------------
1165 ??? 1
1165 ??? 2
1165 ??? 4
1168 ??? 4
2032 ???? 4
2218 ???? 4
2219 ??? 4
2358 ???? 4
2546 ??? 10
there are 4 columns Anganbadi_ID, Food, Month, Yearand I want to compare Foodcolumn twice based on two different month values.
有 4 列Anganbadi_ID, Food, Month, Year，我想Food根据两个不同的月份值对列进行两次比较。
e.g. if I select Month=4 for first Food (Food-1) column and Month=10 for second Food (Food-2) column, then it should be like following::
例如，如果我为第一个 Food (Food-1) 列选择 Month=4，为第二个 Food (Food-2) 列选择 Month=10，那么它应该如下所示：
Anganbadi_ID Food-1 Food-2
------------------------------------------
1165 ??? NULL
1168 ??? NULL
2032 ???? NULL
2218 ???? NULL
2219 ??? NULL
2358 ???? NULL
2546 NULL ???
When I'm trying this code
当我尝试此代码时
SELECT
Anganbadi_ID, Food,
(SELECT Food
FROM Anganbadi AS Anganbadi_2
WHERE (Anganbadi_1.Anganbadi_ID = Anganbadi_ID)
AND (Anganbadi_1.Month = 10)
) AS 'Food(2)'
FROM Anganbadi AS Anganbadi_1
WHERE (Month = 4)
It shows following results::
它显示以下结果：
Anganbadi_ID Food-1 Food-2
--------------------------------------------
1165 ??? NULL
1168 ??? NULL
2032 ???? NULL
2218 ???? NULL
2219 ??? NULL
2358 ???? NULL
Please help me as soon as possible....
请尽快帮助我......
回答by Nalaka526
SELECT DISTINCT Anganbadi_ID,
(SELECT Food
FROM Anganbadi
WHERE (Anganbadi_ID = A.Anganbadi_ID)
AND (Month = 4)) AS Food1,
(SELECT Food
FROM Anganbadi
WHERE (Anganbadi_ID = A.Anganbadi_ID)
AND (Month = 10)) AS Food2
FROM Anganbadi AS A
WHERE A.Month = 10 OR A.Month = 4
回答by Ian P
The key here is the full outer join on ID, the conditions in each sub query can be anything, Full outer joins will give you one row where ID's match and nulls in the opposing columns where they dont. If you want all results even where there food fulfills neither conditions in either month, then do a left join inside one of the sub querys with a select ID from AllFoodsTable
这里的关键是 ID 上的全外连接，每个子查询中的条件可以是任何东西，全外连接会给你一行，其中 ID 匹配，而相反列中的空值则不匹配。如果您想要所有结果，即使食物在任一月份都不满足任何条件，请在其中一个子查询中进行左连接，并使用 AllFoodsTable 中的选择 ID
SELECT Food1, Food2, ID
FROM (
SELECT Food1, ID
FROM MyTable
WHERE Month = 4
) Con1
FULL OUTER JOIN (
SELECT Food2, ID
FROM MyTable
WHERE Month = 10
) Con2
ON Con1.ID = Con2.ID
回答by bummi
SELECT DISTINCT Anganbadi_ID
,(SELECT Food
FROM Anganbadi AS Anganbadi_2
WHERE (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) AND (Month = 4)) AS 'Food(4)'
,(SELECT Food
FROM Anganbadi AS Anganbadi_2
WHERE (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) AND (Month = 10)) AS 'Food(10)'
FROM Anganbadi AS Anganbadi_1
回答by CathalMF
SELECT Anganbadi_ID, Food as food1, null as Food2
where Month = 4
UNION ALL
SELECT Anganbadi_ID, null as food1, food as Food2
where Month = 10
回答by Ashish Negi
SELECT
Food
DISTINCT Anganbadi_ID,
(SELECT Food
FROM Anganbadi
WHERE (Anganbadi_ID = A.Anganbadi_ID)
AND (Month = 4)) AS Food1,
FROM Anganbadi AS A
WHERE A.Month = 10 OR A.Month = 4
