create table a (consume money,date datetime)
insert into a select 1000,getdate()
insert into a select 23232,getdate()-2532
insert into a select 442,getdate()-265
insert into a select 45767,getdate()-44
insert into a select 1088900,getdate()-56954
insert into a select 8555,getdate()-122
insert into a select 1000,getdate()-256
insert into a select 2222,getdate()-466
insert into a select 3333,getdate()-89
insert into a select 5555,getdate()-8这个表按半年怎么统计啊 ?
谢谢
insert into a select 1000,getdate()
insert into a select 23232,getdate()-2532
insert into a select 442,getdate()-265
insert into a select 45767,getdate()-44
insert into a select 1088900,getdate()-56954
insert into a select 8555,getdate()-122
insert into a select 1000,getdate()-256
insert into a select 2222,getdate()-466
insert into a select 3333,getdate()-89
insert into a select 5555,getdate()-8这个表按半年怎么统计啊 ?
谢谢
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%
--*/
from(
select consume, datepart(yyyy, date) yy, datepart(mm, date) mm from a ) a
group by yy, case when mm >= 7 then 7 else 1 end结果中 1=上半年 7=下半年
where right(convert(varchar(7),date,120),2)>=6
group by convert(varchar(4),date,120)
union all
select sum(consume),convert(varchar(4),date,120)+'后半年' from a
where right(convert(varchar(7),date,120),2)<6
group by convert(varchar(4),date,120)
不过我不会
insert into a select 1000,getdate()
insert into a select 23232,getdate()-2532
insert into a select 442,getdate()-265
insert into a select 45767,getdate()-44
insert into a select 1088900,getdate()-56954
insert into a select 8555,getdate()-122
insert into a select 1000,getdate()-256
insert into a select 2222,getdate()-466
insert into a select 3333,getdate()-89
insert into a select 5555,getdate()-8
goselect sum(consume) consume ,
case when month(date) between 1 and 6 then datename(yy,date) + '上半年' else datename(yy,date) + '下半年' end date
from a
group by case when month(date) between 1 and 6 then datename(yy,date) + '上半年' else datename(yy,date) + '下半年' end drop table a/*
consume date
--------------------- ------------------------------------
1088900.0000 1853上半年
23232.0000 2002上半年
2222.0000 2007下半年
1442.0000 2008上半年
57655.0000 2008下半年
6555.0000 2009上半年(所影响的行数为 6 行)
*/