求自动添加不存在日期的方法 求助啊 大神来帮帮忙吧 谢谢了
select t.number as 小时,ISNULL(tt.real_money,0) as 价格 from (SELECT class_id=DATEPART(hh,convert(varchar(20),db_ls.ls_datetime,20)),real_money=Convert(decimal(18,2),sum(round(db_ls_item.h_amount*db_ls_item.h_price*db_ls_item.h_discount,2))) FROM db_product, db_ls, db_ls_item, db_station_total WHERE ( db_ls.ls_id = db_ls_item.ls_id and db_product.h_id = db_ls_item.h_id and db_station_total.station_id = db_ls.station_id ) and ( ( ( ( ( (db_product.h_type like '07%') ) ) ) and ((db_ls.ls_datetime between convert(datetime,'2015-02-15 00:00:00.000') and convert(datetime,'2015-02-15 23:59:59.998'))) and ((db_ls.station_id='G012')) ) ) GROUP BY DATEPART(hh,convert(varchar(20),db_ls.ls_datetime,20)) ) tt inner join master..spt_values t on t.type = 'P' and t.number >= 0 and t.number <=24
left join db_ls
on DATEPART(hh,convert(varchar(20),db_ls.ls_datetime,20)) = tt.class_id
and db_ls.real_money = tt.real_money
and DATEPART(hh,convert(varchar(20),db_ls.ls_datetime,20)) = t.number
我用上面语句不行啊 死循环 不能实现 自动添加 0
执行过后 结果是
小时 价格
0 91.60
1 91.60
2 91.60
3 91.60
4 91.60
5 91.60
6 91.60
7 91.60
8 91.60
9 91.60
10 91.60
11 91.60
12 91.60
13 91.60
14 91.60
15 91.60
16 91.60
17 91.60
18 91.60
19 91.60
20 91.60
21 91.60
22 91.60
23 91.60
24 91.60
0 352.21
1 352.21
2 352.21
3 352.21
4 352.21
5 352.21
6 352.21
7 352.21
8 352.21
9 352.21
10 352.21
11 352.21
12 352.21
13 352.21
14 352.21
15 352.21
16 352.21
17 352.21
18 352.21
19 352.21
20 352.21
21 352.21
22 352.21
23 352.21
24 352.21
0 670.06
1 670.06
2 670.06
3 670.06
4 670.06
5 670.06
6 670.06
7 670.06
8 670.06
9 670.06
10 670.06
11 670.06
12 670.06
13 670.06
14 670.06
15 670.06
16 670.06
17 670.06
18 670.06
19 670.06
20 670.06
21 670.06
22 670.06
23 670.06
24 670.06
0 560.42
1 560.42
2 560.42
3 560.42
4 560.42
5 560.42
6 560.42
7 560.42
8 560.42
9 560.42
10 560.42
11 560.42
12 560.42
13 560.42
14 560.42
15 560.42
16 560.42
17 560.42
18 560.42
19 560.42
20 560.42
21 560.42
22 560.42
23 560.42
24 560.42
0 1389.55
1 1389.55
2 1389.55
3 1389.55
4 1389.55
5 1389.55
6 1389.55
7 1389.55
8 1389.55
9 1389.55
10 1389.55
11 1389.55
12 1389.55
13 1389.55
14 1389.55
15 1389.55
16 1389.55
17 1389.55
18 1389.55
19 1389.55
20 1389.55
21 1389.55
22 1389.55
23 1389.55
24 1389.55
0 1681.88
1 1681.88
2 1681.88
3 1681.88
4 1681.88
5 1681.88
6 1681.88
7 1681.88
8 1681.88
9 1681.88
10 1681.88
11 1681.88
12 1681.88
13 1681.88
14 1681.88
15 1681.88
16 1681.88
17 1681.88
18 1681.88
19 1681.88
20 1681.88
21 1681.88
22 1681.88
23 1681.88
24 1681.88
0 1751.38
1 1751.38
2 1751.38
3 1751.38
4 1751.38
5 1751.38
6 1751.38
7 1751.38
8 1751.38
9 1751.38
10 1751.38
11 1751.38
12 1751.38
13 1751.38
14 1751.38
15 1751.38
16 1751.38
17 1751.38
18 1751.38
19 1751.38
20 1751.38
21 1751.38
22 1751.38
23 1751.38
24 1751.38
0 2492.03
1 2492.03
2 2492.03
3 2492.03
4 2492.03
5 2492.03
6 2492.03
7 2492.03
8 2492.03
9 2492.03
10 2492.03
11 2492.03
12 2492.03
13 2492.03
14 2492.03
15 2492.03
16 2492.03
17 2492.03
18 2492.03
19 2492.03
20 2492.03
21 2492.03
22 2492.03
23 2492.03
24 2492.03
0 951.95
1 951.95
2 951.95
3 951.95
4 951.95
5 951.95
6 951.95
7 951.95
8 951.95
9 951.95
10 951.95
11 951.95
12 951.95
13 951.95
14 951.95
15 951.95
16 951.95
17 951.95
18 951.95
19 951.95
20 951.95
21 951.95
22 951.95
23 951.95
24 951.95
0 1225.73
1 1225.73
2 1225.73
3 1225.73
4 1225.73
5 1225.73
6 1225.73
7 1225.73
8 1225.73
9 1225.73
10 1225.73
11 1225.73
12 1225.73
13 1225.73
14 1225.73
15 1225.73
16 1225.73
17 1225.73
18 1225.73
19 1225.73
20 1225.73
21 1225.73
22 1225.73
23 1225.73
24 1225.73
0 1644.61
1 1644.61
2 1644.61
3 1644.61
4 1644.61
5 1644.61
6 1644.61
7 1644.61
8 1644.61
9 1644.61
10 1644.61
11 1644.61
12 1644.61
13 1644.61
14 1644.61
15 1644.61
16 1644.61
17 1644.61
18 1644.61
19 1644.61
20 1644.61
21 1644.61
22 1644.61
23 1644.61
24 1644.61
0 1032.72
1 1032.72
2 1032.72
3 1032.72
4 1032.72
5 1032.72
6 1032.72
7 1032.72
8 1032.72
9 1032.72
10 1032.72
11 1032.72
12 1032.72
13 1032.72
14 1032.72
15 1032.72
16 1032.72
17 1032.72
18 1032.72
19 1032.72
20 1032.72
21 1032.72
22 1032.72
23 1032.72
24 1032.72
0 361.06
1 361.06
2 361.06
3 361.06
4 361.06
5 361.06
6 361.06
7 361.06
8 361.06
9 361.06
10 361.06
11 361.06
12 361.06
13 361.06
14 361.06
15 361.06
16 361.06
17 361.06
18 361.06
19 361.06
20 361.06
21 361.06
22 361.06
23 361.06
24 361.06
我想实现的效果是
2015-01-01 1 <-这个是代表 年月日 小时 没有分钟和秒
比如
2015-01-01 1
2015-01-01 2 <-2点过来没有3点然后通过SQL语句插入进去然后"价格"的列自己添加为0
2015-01-01 4
2015-01-01 5
2015-01-01 7
2015-01-01 09
2015-01-01 10
通过SQL 语句自己去 补时间
