问题描述
- MS SQL 分组查询的疑问。
-
我突然间对数据库的分组查询好像忘得差不多了,也突然变得不太理解。(解决问题)
如,表结构:
Table Name:TESTID name sex(bit) area ----------------------------- 1 mr.a 1 CN 2 mr.b 1 USA 3 mr.c 1 CN 4 mr.d 0 USA 5 mr.e 0 JP 6 mr.f 1 USA ----------------------------- GROUP 如何查询才能得到这样的结果? area people boy girl ------------------------------- CN 2 2 0 USA 3 2 1 JP 1 0 1 ---------------------------------- 我一直想不透要怎么做才能得到这样的数据
解决方案
select count(*) from xxx group by area
如果还要区分男女,可以用嵌套select或者case when
解决方案二:
sql分组查询
SQL分组查询
SQL根据某个字段分组查询:
解决方案三:
大致上是这样的
select area,sum(name) as people ,sum(case when sex=1 then 1 else 0 end ) as boy,sum(case when sex=0 then 1 else 0 end ) as boy from test group by area
你可以试试
解决方案四:
DECLARE @t TABLE (
Id INT IDENTITY(1,1),
NAME NVARCHAR(50),
sex BIT,
area VARCHAR(20)
)
INSERT INTO @t (NAME,sex,area) VALUES('mr.a',1,'CN')
INSERT INTO @t (NAME,sex,area) VALUES('mr.b',1,'USA')
INSERT INTO @t (NAME,sex,area) VALUES('mr.c',1,'CN')
INSERT INTO @t (NAME,sex,area) VALUES('mr.d',0,'USA')
INSERT INTO @t (NAME,sex,area) VALUES('mr.e',0,'JP')
INSERT INTO @t (NAME,sex,area) VALUES('mr.f',1,'USA')
SELECT area
, COUNT(1) AS people
, SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS boy
, SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS girl
FROM @t GROUP BY area
ORDER BY area
/*
area people boy girl
CN 2 2 0
JP 1 0 1
USA 3 2 1
*/
时间: 2024-08-03 18:01:31