根据指定的年和月查询数据

[WoCao_CSD_N]

表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

[紫竹林畔]

if not object_id('Tempdb..#test') is null
	drop table #test
Go
Create table #test([ID] int,[Ntime] Datetime,[Nanji] int,[Suzhou] int)
Insert #test
select 1,'2012-1-10',20,30 union all
select 2,'2012-1-10',24,18 union all
select 3,'2012-1-18',30,12 union all
select 4,'2012-1-19',22,32 union all
select 31,'2012-1-31',40,32
Go
declare @dt varchar(7)
set @dt='2012-01'
select [UNAME],
       dateadd(day,number,@dt+'-01')[Ntime]
into #t
from master..spt_values ,(select 'Nanji'[UNAME] 
                          union all
                          select 'Suzhou')b
where type='P' and number<day(dateadd(day,-1,convert(char(07),dateadd(month,1,@dt+'-01'),120)+'-01'))
order by [UNAME],[Ntime]
go
select a.[UNAME],
       day(a.[Ntime])[Ntime],
       isnull(b.UNO,0)UNO
from #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]
)b
on a.[UNAME]=b.[UNAME] and a.[Ntime]=b.[Ntime]
go
drop table #T
/*
UNAME  Ntime       UNO
------ ----------- -----------
Nanji  1           0
Nanji  2           0
Nanji  3           0
Nanji  4           0
Nanji  5           0
Nanji  6           0
Nanji  7           0
Nanji  8           0
Nanji  9           0
Nanji  10          44
Nanji  11          0
Nanji  12          0
Nanji  13          0
Nanji  14          0
Nanji  15          0
Nanji  16          0
Nanji  17          0
Nanji  18          30
Nanji  19          22
Nanji  20          0
Nanji  21          0
Nanji  22          0
Nanji  23          0
Nanji  24          0
Nanji  25          0
Nanji  26          0
Nanji  27          0
Nanji  28          0
Nanji  29          0
Nanji  30          0
Nanji  31          40
Suzhou 1           0
Suzhou 2           0
Suzhou 3           0
Suzhou 4           0
Suzhou 5           0
Suzhou 6           0
Suzhou 7           0
Suzhou 8           0
Suzhou 9           0
Suzhou 10          48
Suzhou 11          0
Suzhou 12          0
Suzhou 13          0
Suzhou 14          0
Suzhou 15          0
Suzhou 16          0
Suzhou 17          0
Suzhou 18          12
Suzhou 19          32
Suzhou 20          0
Suzhou 21          0
Suzhou 22          0
Suzhou 23          0
Suzhou 24          0
Suzhou 25          0
Suzhou 26          0
Suzhou 27          0
Suzhou 28          0
Suzhou 29          0
Suzhou 30          0
Suzhou 31          32

(62 row(s) affected)


*/

[GSDN00A]

select * from table where 时间 in (....)

[WoCao_CSD_N]

[Quote=引用 1 楼 的回复:]
SQL code
if not object_id('Tempdb..#test') is null
drop table #test
Go
Create table #test([ID] int,[Ntime] Datetime,[Nanji] int,[Suzhou] int)
Insert #test
select 1,'2012-1-10',20,30 union al……
[/Quote]如果做成这样 怎么弄？
UNAME Ntime UNO UNAME2 NTIME2 UNO2
------ ----------- ----------- ------ ----------- -----------
Nanji 1 0 Nanji 1 0
Nanji 2 0 Nanji 2 0
Nanji 3 0 Nanji 3 0
Nanji 4 0 Nanji 4 0
这样怎么弄呢？合成一张表

[紫竹林畔]

[Quote=引用 3 楼 wocao_csd_n 的回复:]
引用 1 楼 的回复:
SQL code
if not object_id('Tempdb..#test') is null
drop table #test
Go
Create table #test([ID] int,[Ntime] Datetime,[Nanji] int,[Suzhou] int)
Insert #test
select 1,'2012-1-10',20,30……
[/Quote]
这。。。

[WoCao_CSD_N]

[Quote=引用 4 楼 的回复:]
引用 3 楼 wocao_csd_n 的回复:
引用 1 楼 的回复:
SQL code
if not object_id('Tempdb..#test') is null
drop table #test
Go
Create table #test([ID] int,[Ntime] Datetime,[Nanji] int,[Suzhou] int)
Insert #test
s……
[/Quote]？？

[WoCao_CSD_N]

这个是我查出的结果 你的代码执行3遍 条件不一样



我想要的结果是这样的
图片

[紫竹林畔]

呵呵
一会一个要求
跟不上了
累不写了
等楼下吧
：）

[紫竹林畔]

根据一个条件Full join下就是了

[WoCao_CSD_N]

[Quote=引用 7 楼 的回复:]
呵呵
一会一个要求
跟不上了
累不写了
等楼下吧
：）
[/Quote]饿···没事 呵呵 辛苦了 一会结贴给分 谢谢