SQL 单表查询多个计算的值

1.查询男女生各有多少人

SELECT Sex,COUNT(ID) as Count FROM Students GROUP BY Sex

2.

统计男女生中未成年、成年的人数

结果如下：

性别	未成年	成年
男	3	13
女	2	18

 

 

 

SQL语句:

SELECT CASE WHEN Sex=0 THEN '男' ELSE '女' END AS '性别',
SUM(CASE WHEN Age<18 THEN 1 ELSE 0 END) AS '未成年', 
SUM(CASE WHEN Age>=18 THEN 1 ELSE 0 END) AS '成年'
FROM Students
GROUP BY Sex

3.

 

（1）.第一种写法

SELECT PoliceNumber ,
SUM(CASE WHEN RentableArea !=0 and location ='南区' THEN RentableArea ELSE 0 END) AS HaveRent,
SUM(CASE WHEN Area!=0 and location ='南区' THEN Area ELSE 0 END) AS TotalArea,
(SUM(CASE WHEN Area!=0 and location ='南区' THEN Area ELSE 0 END)-SUM(CASE WHEN RentableArea !=0 THEN RentableArea ELSE 0 END)) as HaveNotRent
FROM ShopInfo
GROUP BY PoliceNumber

（2）.第二种写法

select t1.PoliceNumber,t1.TotalArea,t2.HaveNotRent, ISNULL(t3.HaveRent,0) as HaveRent from
(
select PoliceNumber,sum(area) as TotalArea
from FactoryInfo where location ='南区' group by PoliceNumber
) as t1 left join (
select PoliceNumber, sum(isnull(cast(RentableArea as float),'0')) as HaveNotRent from FactoryInfo
where location ='南区' group by PoliceNumber
) as t2 on t1.PoliceNumber=t2.PoliceNumber left join (
select PoliceNumber, sum(isnull(
cast(Area as float)-Cast(RentableArea as float),'0')) as HaveRent from FactoryInfo
where location ='南区' group by PoliceNumber
) as t3 on t3.PoliceNumber=t2.PoliceNumber
order by t1.PoliceNumber,cast(dbo.GetNoFromStr(t1.PoliceNumber) as int) asc