--测试数据
DECLARE @t TABLE(ID int PRIMARY KEY,col decimal(10,2))
INSERT @t SELECT 1 ,26.21
UNION ALL SELECT 2 ,88.19
UNION ALL SELECT 3 , 4.21
UNION ALL SELECT 4 ,76.58
UNION ALL SELECT 5 ,58.06
UNION ALL SELECT 6 ,53.01
UNION ALL SELECT 7 ,18.55
UNION ALL SELECT 8 ,84.90
UNION ALL SELECT 9 ,95.60--统计
SELECT a.Description,
Record_count=COUNT(b.ID),
[Percent]=CASE
WHEN Counts=0 THEN '0.00%'
ELSE CAST(CAST(
COUNT(b.ID)*100./c.Counts
as decimal(10,2)) as varchar)+'%'
END
FROM(
SELECT sid=1,a=NULL,b=30 ,Description='<30' UNION ALL
SELECT sid=2,a=30 ,b=60 ,Description='>=30 and <60' UNION ALL
SELECT sid=3,a=60 ,b=75 ,Description='>=60 and <75' UNION ALL
SELECT sid=4,a=75 ,b=95 ,Description='>=75 and <95' UNION ALL
SELECT sid=5,a=95 ,b=NULL,Description='>=95'
)a LEFT JOIN @t b
ON (b.col<a.b OR a.b IS NULL)
AND(b.col>=a.a OR a.a IS NULL)
CROSS JOIN(
SELECT COUNTS=COUNT(*) FROM @t
)c
GROUP BY a.Description,a.sid,c.COUNTS
ORDER BY a.sid
/*--结果:
Description Record_count Percent
------------------- ------------------ ----------------------
<30 3 33.33%
>=30 and <60 2 22.22%
>=60 and <75 0 0.00%
>=75 and <95 3 33.33%
>=95 1 11.11%
--*/
DECLARE @t TABLE(ID int PRIMARY KEY,col decimal(10,2))
INSERT @t SELECT 1 ,26.21
UNION ALL SELECT 2 ,88.19
UNION ALL SELECT 3 , 4.21
UNION ALL SELECT 4 ,76.58
UNION ALL SELECT 5 ,58.06
UNION ALL SELECT 6 ,53.01
UNION ALL SELECT 7 ,18.55
UNION ALL SELECT 8 ,84.90
UNION ALL SELECT 9 ,95.60--统计
SELECT a.Description,
Record_count=COUNT(b.ID),
[Percent]=CASE
WHEN Counts=0 THEN '0.00%'
ELSE CAST(CAST(
COUNT(b.ID)*100./c.Counts
as decimal(10,2)) as varchar)+'%'
END
FROM(
SELECT sid=1,a=NULL,b=30 ,Description='<30' UNION ALL
SELECT sid=2,a=30 ,b=60 ,Description='>=30 and <60' UNION ALL
SELECT sid=3,a=60 ,b=75 ,Description='>=60 and <75' UNION ALL
SELECT sid=4,a=75 ,b=95 ,Description='>=75 and <95' UNION ALL
SELECT sid=5,a=95 ,b=NULL,Description='>=95'
)a LEFT JOIN @t b
ON (b.col<a.b OR a.b IS NULL)
AND(b.col>=a.a OR a.a IS NULL)
CROSS JOIN(
SELECT COUNTS=COUNT(*) FROM @t
)c
GROUP BY a.Description,a.sid,c.COUNTS
ORDER BY a.sid
/*--结果:
Description Record_count Percent
------------------- ------------------ ----------------------
<30 3 33.33%
>=30 and <60 2 22.22%
>=60 and <75 0 0.00%
>=75 and <95 3 33.33%
>=95 1 11.11%
--*/
union除了连接结果集外,还会进行排序和去重复的操作,导致流聚合,降低性能。
(
select case when age >= 20 and age < 30 then '20-30'
when age >= 30 and age < 40 then '30-40'
when age >= 40 and age < 50 then '40-50'
else '其他'
end field
from a
) t
group by field
[20-30] = (select count(*) from A where age >= 20 and age < 30) ,
[30-40] = (select count(*) from A where age >= 30 and age < 40) ,
[40-50] = (select count(*) from A where age >= 40 and age < 50)
而且,年龄的分段也是不确定的,留给用户选。
后面说的用户选择年龄分段,你是不是应该给出,年龄分段表?用户选择总要保存吧,不至于每次查,每次设吧,应该是一次性设好...
如果需求变了,用户可以更改...还有年龄段,分年几段?这些确定不?
形如:select case when a=1 then 'aa' when a=2 then 'bc' when a=3 then ... end from tb改为select b.x,a.xxx from tb a
inner join
(
select a=1,x='aa'
union all
select a=2,x='bc'
union all
..
) a --将case when 构造成行集
on a.a=b.a
分段的多少与怎么分段都是用户确定,我这边只是根据用户的选择生成SQL语句。
而且要兼容mysql,sql2000,access数据库,以后可能还要兼容其他的数据库,所以一定只能用标准SQL语句。