求sql语句 谢谢
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 行）*/
[解决办法]
- 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.number id , cast('2011-09-01' as datetime) + m.number - 1 day_time, n.extend_CHJ ,n.extend_HSDY ,n.extend_SBQX from master..spt_values m left join tb non cast('2011-09-01' as datetime) + m.number - 1 = n.day_timewhere m.type='p' and m.number between 1 and 30drop 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 行）*/
[解决办法]
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-11-01 16:28:01-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([month_id] int,[day_time] datetime,[extend_CHJ] numeric(6,2),[extend_HSDY] numeric(5,2),[extend_SBQX] numeric(5,2))insert [tb]select 1,'2011-09-15 00:00:00.000',435.00,435.00,345.00 union allselect 2,'2011-09-16 00:00:00.000',2343.00,45.00,334.33--------------开始查询--------------------------declare @startdate datetime,@enddate datetimeset @startdate='2011-09-01'set @enddate='2011-09-30'; with f as(select convert(varchar(10),dateadd(day,number,@startdate),120) as day_timefrom master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p')select month_id=row_number()over(order by getdate()),a.day_time,b.extend_CHJ,b.extend_HSDY,b.extend_SBQXfrom f a left join tb b on datediff(dd,a.day_time,b.day_time)=0----------------结果----------------------------/* (2 行受影响)month_id 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 行受影响)*/
[解决办法]
- SQL code
SELECT B.new_date ,extend_CHJ ,extend_HSDY , xtend_SBQX FROM TB A right join (select cast( '2011-09-01' as datetime) +number as new_date from master..spt_values where type='P' ) Bon A.month_id =B.new_date