笔记(根据性别，不同年龄段统计人数)

来源：互联网发布：淘宝助理如何设置方便编辑：程序博客网时间：2024/04/28 21:57

select sum(case when Gender='M' then 1 else 0 end)man,
sum(case when Gender='F' then 1 else 0 end)woman,
sum(case when FLOOR(datediff(DY,Birthday,getdate())/365.25) Between 60 and 70 then 1 else 0 end)age1,
sum(case when FLOOR(datediff(DY,Birthday,getdate())/365.25) Between 71 and 80 then 1 else 0 end)age2,
sum(case when FLOOR(datediff(DY,Birthday,getdate())/365.25) Between 81 and 90 then 1 else 0 end)age3,
sum(case when FLOOR(datediff(DY,Birthday,getdate())/365.25) Between 91 and 100 then 1 else 0 end)age4,
sum(case when FLOOR(datediff(DY,Birthday,getdate())/365.25) Between 100 and 200 then 1 else 0 end)age5,
COUNT(*)total

from Oca_OldManBaseInfo

SELECT dbo.F_NAME(QYFXPM,'QYFXPM') FF,COUNT(*) SL FROM (
SELECT CASE
WHEN ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','1') THEN 1
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','2') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','2') THEN 2
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','3') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','3') THEN 3
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','4') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','4') THEN 4
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','5') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','5') THEN 5
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','6') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','6') THEN 6
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','7') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','7') THEN 7
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','8') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','8') THEN 8
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','9') AND ThisYear_Risk < dbo.P_TableToColValue('SYS_Parameters','Other2','QYFXPM','9') THEN 9
WHEN ThisYear_Risk >= dbo.P_TableToColValue('SYS_Parameters','Other1','QYFXPM','10') THEN 10
END AS QYFXPM FROM B_EnterpriseCredit) AS A GROUP BY QYFXPM ORDER BY QYFXPM

0 0