select a.籍贯, SUM(a.man),sum(a.womwan) from ( select 籍贯,case when SEX='男' then 1 else 0 end) as man,case when SEX='女' then 1 else 0 end as woman from 学生表) a group by a.籍贯
create table #city (ID INT ,SEX VARCHAR(10),PROVINCE VARCHAR(20)) INSERT INTO #CITY VALUES(1,'MALE','NEIMENGGU') INSERT INTO #CITY VALUES(2,'FEMALE','BEIJING') INSERT INTO #CITY VALUES(3,'MALE','SHANGHAI') INSERT INTO #CITY VALUES(4,'FEMALE','SHANGHAI') INSERT INTO #CITY VALUES(5,'FEMALE','BEIJING')SELECT * FROM #CITY /* ID SEX PROVINCE ----------- ---------- -------------------- 1 MALE NEIMENGGU 2 FEMALE BEIJING 3 MALE SHANGHAI 4 FEMALE SHANGHAI 5 FEMALE BEIJING(5 row(s) affected) */1.方法一 SELECT PROVINCE,SUM(CASE SEX WHEN 'MALE' THEN 1 ELSE 0 END) AS 'MALE', SUM(CASE SEX WHEN 'FEMALE' THEN 1 ELSE 0 END) AS 'FEMALE' FROM #CITY GROUP BY PROVINCE --WITH ROLLUP 加上这句可以计算总计,如果配合group by 的栏位,可以有小计2.方法二 SELECT PROVINCE,'MALE' = (SELECT COUNT(C1.[ID]) FROM #CITY C1 WHERE C1.PROVINCE = C.PROVINCE AND C1.SEX = 'MALE'),'FEMALE'= (SELECT COUNT(C2.[ID]) FROM #CITY C2 WHERE C2.PROVINCE = C.PROVINCE AND C2.SEX = 'FEMALE') FROM #CITY C GROUP BY PROVINCE /* PROVINCE MALE FEMALE -------------------- ----------- ----------- BEIJING 0 2 NEIMENGGU 1 0 SHANGHAI 1 1(3 row(s) affected) */
INSERT INTO #CITY VALUES(1,'MALE','NEIMENGGU')
INSERT INTO #CITY VALUES(2,'FEMALE','BEIJING')
INSERT INTO #CITY VALUES(3,'MALE','SHANGHAI')
INSERT INTO #CITY VALUES(4,'FEMALE','SHANGHAI')
INSERT INTO #CITY VALUES(5,'FEMALE','BEIJING')SELECT * FROM #CITY
/*
ID SEX PROVINCE
----------- ---------- --------------------
1 MALE NEIMENGGU
2 FEMALE BEIJING
3 MALE SHANGHAI
4 FEMALE SHANGHAI
5 FEMALE BEIJING(5 row(s) affected)
*/1.方法一
SELECT PROVINCE,SUM(CASE SEX WHEN 'MALE' THEN 1 ELSE 0 END) AS 'MALE',
SUM(CASE SEX WHEN 'FEMALE' THEN 1 ELSE 0 END) AS 'FEMALE'
FROM #CITY
GROUP BY PROVINCE
--WITH ROLLUP 加上这句可以计算总计,如果配合group by 的栏位,可以有小计2.方法二
SELECT PROVINCE,'MALE' = (SELECT COUNT(C1.[ID]) FROM #CITY C1 WHERE
C1.PROVINCE = C.PROVINCE AND C1.SEX = 'MALE'),'FEMALE'=
(SELECT COUNT(C2.[ID]) FROM #CITY C2 WHERE
C2.PROVINCE = C.PROVINCE AND C2.SEX = 'FEMALE')
FROM #CITY C
GROUP BY PROVINCE
/*
PROVINCE MALE FEMALE
-------------------- ----------- -----------
BEIJING 0 2
NEIMENGGU 1 0
SHANGHAI 1 1(3 row(s) affected)
*/