month_id day_time extend_CHJ extend_HSDY extend_SBQX
1 2011-09-15 00:00:00.000 435.00 435.00 345.00
2 2011-09-16 00:00:00.000 2343.00 45.00 334.33
上面是表结构。
根据月份时间查询，想要结果
1 2011-09-1 00:00:00.000 NUll NUll NUll
2 2011-09-2 00:00:00.000 NUll NUll NUll
3 2011-09-3 00:00:00.000 NUll NUll NUll
4 2011-09-4 00:00:00.000 NUll NUll NUll
5 2011-09-5 00:00:00.000 NUll NUll NUll
6 2011-09-6 00:00:00.000 NUll NUll NUll
.........
15 2011-09-15 00:00:00.000 435.00 435.00 345.00
16 2011-09-16 00:00:00.000 2343.00 45.00 334.33
.........
29 2011-09-29 00:00:00.000 NUll NUll NUll
30 2011-09-30 00:00:00.000 NUll NUll NUll
------解决方案--------------------
- SQL code
生成一个日期表，然后使用左连接select m.* , n.extend_CHJ ,n.extend_HSDY ,n.extend_SBQX from(select 1 monthid , '2011-09-1' day_time union allselect 2 monthid , '2011-09-2' day_time union all...select 30 monthid , '2011-09-30' day_time) m left jointb non m.day_time = n.day_time
------解决方案--------------------
- SQL code
create table tb(month_id int,day_time datetime,extend_CHJ decimal(18,2),extend_HSDY decimal(18,2),extend_SBQX decimal(18,2))insert into tb values(1 ,'2011-09-15 00:00:00.000', 435.00 ,435.00 ,345.00)insert into tb values(2 ,'2011-09-16 00:00:00.000', 2343.00 ,45.00 ,334.33)go--生成一个日期表，然后使用左连接select m.* , n.extend_CHJ ,n.extend_HSDY ,n.extend_SBQX from(select 1 monthid , '2011-09-01' day_time union allselect 2 monthid , '2011-09-02' day_time union allselect 3 monthid , '2011-09-03' day_time union allselect 4 monthid , '2011-09-04' day_time union allselect 5 monthid , '2011-09-05' day_time union allselect 6 monthid , '2011-09-06' day_time union allselect 7 monthid , '2011-09-07' day_time union allselect 8 monthid , '2011-09-08' day_time union allselect 9 monthid , '2011-09-09' day_time union allselect 10 monthid , '2011-09-10' day_time union allselect 11 monthid , '2011-09-11' day_time union allselect 12 monthid , '2011-09-12' day_time union allselect 13 monthid , '2011-09-13' day_time union allselect 14 monthid , '2011-09-14' day_time union allselect 15 monthid , '2011-09-15' day_time union allselect 16 monthid , '2011-09-16' day_time union allselect 17 monthid , '2011-09-17' day_time union allselect 18 monthid , '2011-09-18' day_time union allselect 19 monthid , '2011-09-19' day_time union allselect 20 monthid , '2011-09-20' day_time union allselect 21 monthid , '2011-09-21' day_time union allselect 22 monthid , '2011-09-22' day_time union allselect 23 monthid , '2011-09-23' day_time union allselect 24 monthid , '2011-09-24' day_time union allselect 25 monthid , '2011-09-25' day_time union allselect 26 monthid , '2011-09-26' day_time union allselect 27 monthid , '2011-09-27' day_time union allselect 28 monthid , '2011-09-28' day_time union allselect 29 monthid , '2011-09-29' day_time union allselect 30 monthid , '2011-09-30' day_time) m left jointb non m.day_time = n.day_timedrop table tb/*monthid day_time extend_CHJ extend_HSDY extend_SBQX ----------- ---------- -------------------- -------------------- -------------------- 1 2011-09-01 NULL NULL NULL2 2011-09-02 NULL NULL NULL3 2011-09-03 NULL NULL NULL4 2011-09-04 NULL NULL NULL5 2011-09-05 NULL NULL NULL6 2011-09-06 NULL NULL NULL7 2011-09-07 NULL NULL NULL8 2011-09-08 NULL NULL NULL9 2011-09-09 NULL NULL NULL10 2011-09-10 NULL NULL NULL11 2011-09-11 NULL NULL NULL12 2011-09-12 NULL NULL NULL13 2011-09-13 NULL NULL NULL14 2011-09-14 NULL NULL NULL15 2011-09-15 435.00 435.00 345.0016 2011-09-16 2343.00 45.00 334.3317 2011-09-17 NULL NULL NULL18 2011-09-18 NULL NULL NULL19 2011-09-19 NULL NULL NULL20 2011-09-20 NULL NULL NULL21 2011-09-21 NULL NULL NULL22 2011-09-22 NULL NULL NULL23 2011-09-23 NULL NULL NULL24 2011-09-24 NULL NULL NULL25 2011-09-25 NULL NULL NULL26 2011-09-26 NULL NULL NULL27 2011-09-27 NULL NULL NULL28 2011-09-28 NULL NULL NULL29 2011-09-29 NULL NULL NULL30 2011-09-30 NULL NULL NULL（所影响的行数为 30 行）*/