查询问题,在线结分
昨天晚上了个贴子http://topic.csdn.net/u/20071113/20/6abfe1f8-3115-4fed-a2de-0ec449a5c030.html在昨夜小楼和各位朋友的帮助下解决了问题
SQL语句如下
declare @b table(意见 varchar(20),日期 smalldatetime,状态 int)
insert @b select 'asdf','2007-10-11 0:15:15',0
union all select 'fdasd','2007-10-11 0:12:15' ,1
union all select 'asasd','2007-10-12 10:15:15' ,3
union all select 'asds', '2007-10-15 03:12:23' ,3
union all select 'asfd', '2007-10-17 04:32:12' ,2
declare @a table(id int identity(0,1),s smalldatetime,e smalldatetime)
declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
insert @a select top 31 null,null from syscolumns
select convert(varchar(10),aa.ss,120) 日期,aa.状态,count(意见) 意见数 from
(
select a.*,b.* from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s) <=@e)a
cross join (select 状态=0 union all select 1 union all select 2 union all select 3) b
) aa
left join @b bb
on datediff(day,aa.ss,bb.日期)=0 and aa.状态=bb.状态
group by aa.ss,aa.状态
order by aa.日期 desc,aa.状态
--result
/*
日期 状态 意见数
---------- ------- -----------
2007-10-0900
2007-10-0910
2007-10-0920
2007-10-0930
2007-10-1000
2007-10-1010
2007-10-1020
2007-10-1030
2007-10-1101
2007-10-1111
2007-10-1120
2007-10-1130
2007-10-1200
2007-10-1210
2007-10-1220
2007-10-1231
2007-10-1300
2007-10-1310
2007-10-1320
2007-10-1330
2007-10-1400
2007-10-1410
2007-10-1420
2007-10-1430
2007-10-1500
2007-10-1510
2007-10-1520
2007-10-1531
2007-10-1600
2007-10-1610
2007-10-1620
2007-10-1630
2007-10-1700
2007-10-1710
2007-10-1721
2007-10-1730
2007-10-1800
2007-10-1810
2007-10-1820
2007-10-1830
2007-10-1900
2007-10-1910
2007-10-1920
2007-10-1930
2007-10-2000
2007-10-2010
2007-10-2020
2007-10-2030
（所影响的行数为 12 行）
*/
declare @b table(意见 varchar(20),日期 smalldatetime,状态 int)insert @b select 'asdf','2007-10-11 0:15:15',0union all select 'fdasd','2007-10-11 0:12:15' ,1 union all select 'asasd','2007-10-12 10:15:15' ,3 union all select 'asds', '2007-10-15 03:12:23' ,3union all select 'asfd', '2007-10-17 04:32:12' ,2declare @a table(id int identity(0,1),s smalldatetime,e smalldatetime)declare @s smalldatetime,@e smalldatetimeselect @s='2007-10-09',@e='2007-10-20'insert @a select top 31 null,null from syscolumnsselect convert(varchar(10),aa.ss,120) 日期,aa.状态,count(意见) 意见数 ,cast(100 * count(意见)/isnull((select sum(1) from @b where datediff(d,日期,aa.ss) = 0 ),1) as varchar)+'%' 占比from ( select a.*,b.* from (select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)a cross join (select 状态=0 union all select 1 union all select 2 union all select 3) b ) aaleft join @b bbon datediff(day,aa.ss,bb.日期)=0 and aa.状态=bb.状态group by aa.ss,aa.状态order by aa.日期 desc,aa.状态/*日期 状态 意见数 占比 ---------- ----------- ----------- ------------------------------- 2007-10-20 0 0 0%2007-10-20 1 0 0%2007-10-20 2 0 0%2007-10-20 3 0 0%2007-10-19 0 0 0%2007-10-19 1 0 0%2007-10-19 2 0 0%2007-10-19 3 0 0%2007-10-18 0 0 0%2007-10-18 1 0 0%2007-10-18 2 0 0%2007-10-18 3 0 0%2007-10-17 0 0 0%2007-10-17 1 0 0%2007-10-17 2 1 100%2007-10-17 3 0 0%2007-10-16 0 0 0%2007-10-16 1 0 0%2007-10-16 2 0 0%2007-10-16 3 0 0%2007-10-15 0 0 0%2007-10-15 1 0 0%2007-10-15 2 0 0%2007-10-15 3 1 100%2007-10-14 0 0 0%2007-10-14 1 0 0%2007-10-14 2 0 0%2007-10-14 3 0 0%2007-10-13 0 0 0%2007-10-13 1 0 0%2007-10-13 2 0 0%2007-10-13 3 0 0%2007-10-12 0 0 0%2007-10-12 1 0 0%2007-10-12 2 0 0%2007-10-12 3 1 100%2007-10-11 0 1 50%2007-10-11 1 1 50%2007-10-11 2 0 0%2007-10-11 3 0 0%2007-10-10 0 0 0%2007-10-10 1 0 0%2007-10-10 2 0 0%2007-10-10 3 0 0%2007-10-09 0 0 0%2007-10-09 1 0 0%2007-10-09 2 0 0%2007-10-09 3 0 0%（所影响的行数为 48 行）*/
[解决办法]
帮顶了..
学习.
