根据指定的年和月查询数据
表HKB
Nanji和Suzhou均为int类型
ID Ntime Nanji Suzhou
1 2012-1-10 20 30
2 2012-1-10 24 18
3 2012-1-18 30 12
4 2012-1-19 22 32
...
31 2012-1-31 40 32
(Myday是日期 天，UNO是数量 )
要得到的结果是
UNAME Myday UNO
Nanji 1 0
...
Nanji 10 44
Nanji 18 30
Nanji 19 22
...
Nanji 31 40
Suzhou 1 0
...
Suzhou 10 48
...
Suzhou 31 32
[解决办法]
- SQL code
if not object_id('Tempdb..#test') is null drop table #testGoCreate table #test([ID] int,[Ntime] Datetime,[Nanji] int,[Suzhou] int)Insert #testselect 1,'2012-1-10',20,30 union allselect 2,'2012-1-10',24,18 union allselect 3,'2012-1-18',30,12 union allselect 4,'2012-1-19',22,32 union allselect 31,'2012-1-31',40,32Godeclare @dt varchar(7)set @dt='2012-01'select [UNAME], dateadd(day,number,@dt+'-01')[Ntime]into #tfrom master..spt_values ,(select 'Nanji'[UNAME] union all select 'Suzhou')bwhere type='P' and number<day(dateadd(day,-1,convert(char(07),dateadd(month,1,@dt+'-01'),120)+'-01'))order by [UNAME],[Ntime]goselect a.[UNAME], day(a.[Ntime])[Ntime], isnull(b.UNO,0)UNOfrom #t a left join(Select [Ntime],'Nanji' [UNAME],sum([Nanji])UNO from #test group by [Ntime] union all Select [Ntime],'SUzhou'[UNAME],sum([Suzhou]) from #test group by [Ntime])bon a.[UNAME]=b.[UNAME] and a.[Ntime]=b.[Ntime]godrop table #T/*UNAME Ntime UNO------ ----------- -----------Nanji 1 0Nanji 2 0Nanji 3 0Nanji 4 0Nanji 5 0Nanji 6 0Nanji 7 0Nanji 8 0Nanji 9 0Nanji 10 44Nanji 11 0Nanji 12 0Nanji 13 0Nanji 14 0Nanji 15 0Nanji 16 0Nanji 17 0Nanji 18 30Nanji 19 22Nanji 20 0Nanji 21 0Nanji 22 0Nanji 23 0Nanji 24 0Nanji 25 0Nanji 26 0Nanji 27 0Nanji 28 0Nanji 29 0Nanji 30 0Nanji 31 40Suzhou 1 0Suzhou 2 0Suzhou 3 0Suzhou 4 0Suzhou 5 0Suzhou 6 0Suzhou 7 0Suzhou 8 0Suzhou 9 0Suzhou 10 48Suzhou 11 0Suzhou 12 0Suzhou 13 0Suzhou 14 0Suzhou 15 0Suzhou 16 0Suzhou 17 0Suzhou 18 12Suzhou 19 32Suzhou 20 0Suzhou 21 0Suzhou 22 0Suzhou 23 0Suzhou 24 0Suzhou 25 0Suzhou 26 0Suzhou 27 0Suzhou 28 0Suzhou 29 0Suzhou 30 0Suzhou 31 32(62 row(s) affected)*/