SQL查询中遇到空行补全的问题

Question

测试代码如下declare @table1 table (classid int,pricetime datetime,price money)
insert into @table1 select 1,'2010-03-01',22
insert into @table1 select 1,'2010-03-03',523
insert into @table1 select 1,'2010-03-04',233
insert into @table1 select 1,'2010-03-06',123
insert into @table1 select 1,'2010-03-07',455
insert into @table1 select 1,'2010-03-12',1020
insert into @table1 select 1,'2010-03-15',657
insert into @table1 select 1,'2010-03-23',235
insert into @table1 select 1,'2010-03-25',545
insert into @table1 select 1,'2010-04-01',477
insert into @table1 select 1,'2010-04-03',455
insert into @table1 select 1,'2010-04-04',86
insert into @table1 select 1,'2010-04-07',8
insert into @table1 select 1,'2010-04-12',900
insert into @table1 select 1,'2010-04-15',78
insert into @table1 select 1,'2010-04-23',88
insert into @table1 select 1,'2010-04-25',800insert into @table1 select 2,'2010-03-01',234
insert into @table1 select 2,'2010-03-02',11
insert into @table1 select 2,'2010-03-04',111
insert into @table1 select 2,'2010-03-06',463
insert into @table1 select 2,'2010-03-08',634
insert into @table1 select 2,'2010-03-12',345
insert into @table1 select 2,'2010-03-13',899
insert into @table1 select 2,'2010-03-21',899
insert into @table1 select 2,'2010-03-25',434
insert into @table1 select 2,'2010-04-02',234
insert into @table1 select 2,'2010-04-03',235
insert into @table1 select 2,'2010-04-04',765
insert into @table1 select 2,'2010-04-08',235
insert into @table1 select 2,'2010-04-12',1244
insert into @table1 select 2,'2010-04-16',124
insert into @table1 select 2,'2010-04-24',244
insert into @table1 select 2,'2010-04-25',535
insert into @table1 select 2,'2010-04-27',124insert into @table1 select 3,'2010-03-02',525
insert into @table1 select 3,'2010-03-03',262
insert into @table1 select 3,'2010-03-04',235
insert into @table1 select 3,'2010-03-05',235
insert into @table1 select 3,'2010-03-06',23
insert into @table1 select 3,'2010-03-17',325
insert into @table1 select 3,'2010-03-18',235
insert into @table1 select 3,'2010-03-21',235
insert into @table1 select 3,'2010-03-22',623
insert into @table1 select 3,'2010-04-01',623
insert into @table1 select 3,'2010-04-02',235
insert into @table1 select 3,'2010-04-03',235
insert into @table1 select 3,'2010-04-04',1435
insert into @table1 select 3,'2010-04-12',466
insert into @table1 select 3,'2010-04-13',346
insert into @table1 select 3,'2010-04-23',343
insert into @table1 select 3,'2010-04-29',1225declare @stime datetime,@etime datetimeset @stime='2010-03-01'
set @etime='2010-03-30'select b.classid,
b.price,
convert(varchar(10),dateadd(day,number,@stime),120) dt
from master..spt_values a 
left join @table1 b 
on datediff(dd,dateadd(day,number,@stime),b.pricetime)=0
where type='P' and number between 0 and datediff(day,@stime,@etime)
and classid=1结果
classsid   price   dt
1 22.00 2010-03-01
1 523.00 2010-03-03
1 233.00 2010-03-04
1 123.00 2010-03-06
1 455.00 2010-03-07
1 1020.00 2010-03-12
1 657.00 2010-03-15
1 235.00 2010-03-23
1 545.00 2010-03-25
我想查询出某个ID在某个时间段的价格
如果价格表中没有的话，我希望也能列出那个时间下没有价格的ID并且把价格列为0
比如上面的结果2号没有
我希望也能在结果中有这样子的一列
classid  price  dt
1       0.00    2010-03-02而且我还希望能一下子查询出很多classid的结果

Answer 1

sqlserver 不是特别熟，我说下用Oracle的想到的方法
第一步
select rownum, 月份第一天+rownum from dual connect by rownum < 月份天数
建立全天数结果集
第二步
取table中pricetime 日期 关联第一步结果集 
第三步 
外联中 null 补为0 类似
select a.n ,nvl(b.price,0),a.pricetime from 
(select rownum n ,sysdate-28+rownum pricetime from dual connect by rownum < 31)a,
(select 25.20 price,sysdate pricetime, to_number(to_char(sysdate-20,'dd'))n from dual )b
where a.n = b.n(+)

Answer 2

你这个方法我看的不是很明白。。但是我大概觉得你这个只是在日期上补齐了而已没有利用到classid这个条件。。这个也是需要补齐的

Answer 3

set nocount on
declare @table1 table (classid int,pricetime datetime,price money)
insert into @table1 select 1,'2010-03-01',22
insert into @table1 select 1,'2010-03-03',523
insert into @table1 select 1,'2010-03-04',233
insert into @table1 select 1,'2010-03-06',123
insert into @table1 select 1,'2010-03-07',455
insert into @table1 select 1,'2010-03-12',1020
insert into @table1 select 1,'2010-03-15',657
insert into @table1 select 1,'2010-03-23',235
insert into @table1 select 1,'2010-03-25',545
insert into @table1 select 1,'2010-04-01',477
insert into @table1 select 1,'2010-04-03',455
insert into @table1 select 1,'2010-04-04',86
insert into @table1 select 1,'2010-04-07',8
insert into @table1 select 1,'2010-04-12',900
insert into @table1 select 1,'2010-04-15',78
insert into @table1 select 1,'2010-04-23',88
insert into @table1 select 1,'2010-04-25',800insert into @table1 select 2,'2010-03-01',234
insert into @table1 select 2,'2010-03-02',11
insert into @table1 select 2,'2010-03-04',111
insert into @table1 select 2,'2010-03-06',463
insert into @table1 select 2,'2010-03-08',634
insert into @table1 select 2,'2010-03-12',345
insert into @table1 select 2,'2010-03-13',899
insert into @table1 select 2,'2010-03-21',899
insert into @table1 select 2,'2010-03-25',434
insert into @table1 select 2,'2010-04-02',234
insert into @table1 select 2,'2010-04-03',235
insert into @table1 select 2,'2010-04-04',765
insert into @table1 select 2,'2010-04-08',235
insert into @table1 select 2,'2010-04-12',1244
insert into @table1 select 2,'2010-04-16',124
insert into @table1 select 2,'2010-04-24',244
insert into @table1 select 2,'2010-04-25',535
insert into @table1 select 2,'2010-04-27',124insert into @table1 select 3,'2010-03-02',525
insert into @table1 select 3,'2010-03-03',262
insert into @table1 select 3,'2010-03-04',235
insert into @table1 select 3,'2010-03-05',235
insert into @table1 select 3,'2010-03-06',23
insert into @table1 select 3,'2010-03-17',325
insert into @table1 select 3,'2010-03-18',235
insert into @table1 select 3,'2010-03-21',235
insert into @table1 select 3,'2010-03-22',623
insert into @table1 select 3,'2010-04-01',623
insert into @table1 select 3,'2010-04-02',235
insert into @table1 select 3,'2010-04-03',235
insert into @table1 select 3,'2010-04-04',1435
insert into @table1 select 3,'2010-04-12',466
insert into @table1 select 3,'2010-04-13',346
insert into @table1 select 3,'2010-04-23',343
insert into @table1 select 3,'2010-04-29',1225declare @stime datetime,@etime datetimeset @stime='2010-03-01'
set @etime='2010-03-30'select t.classid,isnull(b.price,0) price,
convert(varchar(10),dateadd(day,number,@stime),120) dt
from (select number from master..spt_values where type='P' and number between 0 and datediff(day,@stime,@etime) )a 
cross join (select distinct classid from @table1) t 
left join @table1 b on t.classid=b.classid and datediff(dd,dateadd(day,a.number,@stime),b.pricetime)=0 

Answer 4

嗯，这个方法只能查询单个，
存储过程中需要查询多个的话，能否变通下：查单个的作为一个function
过程中循环传入参数classid得的结果合并------------------具体只能看要求了

Answer 5

顶4楼大神nb用了cross join 可以关联所有..

Answer 6

帅哥我的classid是作为条件传进来的  可能还会有其他相关的条件不一定每次都以ID这种单一的条件
可能是复杂的组合条件
这样子我只要用条件生成一个新表 放在Cross join这个位置就可以了吗？