我突然间对数据库的分组查询好像忘得差不多了，也突然变得不太理解。（解决问题）
如，表结构：
Table Name：TEST
ID 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
大致上是这样的
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
*/