表
id name birthday
1 张三 1950-01-01
2 李四 1955-09-12
3 王五 1982-09-23
4 AAA 1960-01-01
5 BBB 1975-09-12
6 CCC 1972-09-23要得到的结果为:年龄段 人数 百分比 对象id
1950-01-01~1970-01-01 3 50.00 1_2_4
1970-01-02~1980-01-01 2 33.33 5_6
1980-01-02~2010-11-14 1 16.66 3
我写了一条sql
select
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end xm,
count(*) xyrs , cast(count(*)*100.00/(select count(*) from wyxx ) as decimal(18,2)) bfb
from wyxx
group by
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end
结果:
年龄段 人数 百分比
0-30 1 .20
31-40 39 7.75
41-50 216 42.94
50 以上 247 49.11
这样得到了不同年龄段分组的人数、百分比,但是id的累加要怎么做。。求解!
id name birthday
1 张三 1950-01-01
2 李四 1955-09-12
3 王五 1982-09-23
4 AAA 1960-01-01
5 BBB 1975-09-12
6 CCC 1972-09-23要得到的结果为:年龄段 人数 百分比 对象id
1950-01-01~1970-01-01 3 50.00 1_2_4
1970-01-02~1980-01-01 2 33.33 5_6
1980-01-02~2010-11-14 1 16.66 3
我写了一条sql
select
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end xm,
count(*) xyrs , cast(count(*)*100.00/(select count(*) from wyxx ) as decimal(18,2)) bfb
from wyxx
group by
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end
结果:
年龄段 人数 百分比
0-30 1 .20
31-40 39 7.75
41-50 216 42.94
50 以上 247 49.11
这样得到了不同年龄段分组的人数、百分比,但是id的累加要怎么做。。求解!
insert into wyxx select 1,'张三','1950-01-01'
insert into wyxx select 2,'李四','1955-09-12'
insert into wyxx select 3,'王五','1982-09-23'
insert into wyxx select 4,'AAA','1960-01-01'
insert into wyxx select 5,'BBB','1975-09-12'
insert into wyxx select 6,'CCC','1972-09-23'
go
select id,
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end xm
into #
from wyxx
select xm,stuff((select '_'+convert(varchar,id) from # where xm=a.xm for xml path('')),1,1,'') from # a group by xm
go
drop table wyxx,#这个给你参考:
AS
(
SELECT '1950-01-01~1970-01-01' as 年龄段,id,count(*)cnt
FROM #tb t
WHERE cast(birthday AS VARCHAR(20)) BETWEEN '1950-01-01' AND '1970-01-01'
GROUP BY t.id
UNION ALL
SELECT '1970-01-02~1980-01-01' as 年龄段,id,count(*)cnt
FROM #tb t
WHERE cast(birthday AS VARCHAR(20)) BETWEEN '1970-01-02' AND '1980-01-01'
GROUP BY t.id
UNION ALL
SELECT '1980-01-02~2010-11-14' as 年龄段,id,count(*)cnt
FROM #tb t
WHERE cast(birthday AS VARCHAR(20)) BETWEEN '1980-01-02' AND '2010-11-14'
GROUP BY t.id
)
SELECT 年龄段 ,CNT=CAST((SELECT COUNT(*)FROM CTE WHERE 年龄段=C.年龄段)*100.00/(SELECT COUNT(*) FROM CTE)AS DECIMAL(5,2))
,id=stuff((select '_'+ cast(id as VARCHAR)from cte where c.年龄段=年龄段 for xml path('')),1,1,'')
FROM cte c
GROUP BY 年龄段年龄段 CNT id
---------------------- --------------------------------------- ----------------------------
1950-01-01~1970-01-01 50.00 1_2_4
1970-01-02~1980-01-01 33.33 5_6
1980-01-02~2010-11-14 16.67 3(3 row(s) affected)
insert into wyxx select 1,'张三','1950-01-01'
insert into wyxx select 2,'李四','1955-09-12'
insert into wyxx select 3,'王五','1982-09-23'
insert into wyxx select 4,'AAA','1960-01-01'
insert into wyxx select 5,'BBB','1975-09-12'
insert into wyxx select 6,'CCC','1972-09-23'
go
select id,
case when (csny<='2010-11-14' and csny>='1980-11-14') then '0-30' when (csny<='1980-11-13' and csny>='1970-11-14') then '31-40'
when (csny<='1970-11-13' and csny>='1960-11-14') then '41-50' else '50 以上' end xm
into #
from wyxx
select xm as 年龄段,count(*) as 人数,convert(decimal,count(*))/(select count(*) from #) as 百分比,stuff((select '_'+convert(varchar,id) from # where xm=a.xm for xml path('')),1,1,'') id范围 from # a group by xm
go
drop table wyxx,#
/*
年龄段 人数 百分比 id范围
------- ----------- --------------------------------------- ----------------------------
0-30 1 0.16666666666 3
31-40 2 0.33333333333 5_6
50 以上 3 0.50000000000 1_2_4(3 行受影响)
*/