SQL 如何选择一列中的多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22049855/
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 SELECT COUNT multiple values in one column
提问by Doctorj77
rather a newbie at SQL, so please be gentle....as I think this is a basic one.
而是 SQL 的新手，所以请保持温和....因为我认为这是一个基本的。
I'm trying to write a query with multiple (13) counts, based off Column1. The 1st Count is the over-all total. And then the 12 others are filtered by Color. I can get my results by doing multiple Counts all in one query, but this gives me 13 rows of data. The goal here is to get everything on just one row. So, almost like each count would be its own column. Here is an example of the data model
我正在尝试基于 Column1 编写具有多个 (13) 计数的查询。第一个计数是总体总数。然后其他 12 个按颜色过滤。我可以通过在一个查询中执行多个计数来获得结果，但这给了我 13 行数据。这里的目标是将所有内容都放在一行中。所以，几乎就像每个计数都是它自己的列。这是数据模型的示例
Database = CARS, Table = TYPES, Column1 = LICENSE, Column2 = COLOR
SELECT COUNT (LICENSE) AS 'Total ALL Cars'
FROM CARS.TYPES WITH (NOLOCK)
SELECT COUNT (LICENSE) AS 'Total RED Cars'
FROM CARS.TYPES WITH (NOLOCK)
WHERE COLOR = 'RED'
And on & on & on for each remaining color. This works, but again, I'm trying to streamline it all into one row of data, IF possible. Thank you in advance
并且对每个剩余的颜色打开和打开。这有效，但同样，如果可能的话，我试图将其全部简化为一行数据。先感谢您
回答by
You simply need to include color in select statement and group by it to count cars of each color.
您只需要在 select 语句中包含颜色并按它分组即可计算每种颜色的汽车。
SELECT Color, Count(*)
FROM CARS.TYPES WITH(NOLOCK)
GROUP BY Color
or
或者
SELECT COUNT(CASE WHEN Color = 'RED' THEN 1
ELSE NULL
END) AS RedCars
,COUNT(CASE WHEN Color = 'BLUE' THEN 1
ELSE NULL
END) AS BlueCars
,COUNT(*) AS AllCars
FROM CARS.TYPES WITH ( NOLOCK )
回答by Hart CO
You can do this with a conditional SUM():
您可以使用条件执行此操作SUM()：
SELECT SUM(CASE WHEN Color = 'Red' THEN 1 END) AS 'Total Red Cars'
,SUM(CASE WHEN Color = 'Blue' THEN 1 END) AS 'Total Blue Cars'
FROM CARS.TYPES
If using MySQL you can simplify further:
如果使用 MySQL，您可以进一步简化：
SELECT SUM(Color = 'Red') AS 'Total Red Cars'
,SUM(Color = 'Blue') AS 'Total Blue Cars'
FROM CARS.TYPES
回答by Martin Smith
Or with PIVOT
或与 PIVOT
SELECT RED + BLUE + GREEN AS total,
RED,
BLUE,
GREEN
FROM CARS.TYPES PIVOT (COUNT (LICENSE) FOR COLOR IN ([RED], [BLUE], [GREEN])) P
回答by Dipendu Paul
SELECT SUM(Al) AllCount, SUM(Red) RedCount, SUM(Green) GreenCount, ...
(
SELECT 1 AS Al
, CASE WHEN Color = 'Red' THEN 1 ELSE 0 END AS Red
, CASE WHEN Color = 'Green' THEN 1 ELSE 0 END AS Green
...
FROM CARS.Types
)
