declare @tabela table (dailyrequest int,startdate datetime,finishdate datetime,gradename nvarchar(20))
insert into @tabela select 30,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_5m'
union all select 50,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_2.5m'
union all select 40,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_3.8m'
union all select 50,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_4.4m'
declare @tableb table (production int,[date] datetime ,gradename nvarchar(20))
insert into @tableb select 40,'12/JAN/2009 11:42:56','p1_5m'
union all select 30,'12/JAN/2009 11:42:56','p1_2.2m'
union all select 50,'12/JAN/2009 11:42:56','p1_3.4m'select isnull(a.dailyrequest,0) dailyrequest,isnull(b.production,0) production,isnull(b.date,0) [date],isnull(b.gradename,a.gradename) gradename from @tabela a full join @tableb b
on a.gradename=b.gradename and (date >= startdate and date <= finishdate)
这样的结果是dailyrequest production Date Gradename
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 NULL p1_2.5m
40 0 NULL p1_3.8m
50 0 NULL p1_4.4m
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m 但是由于'p1_2.5M' 的时间段也在 b.date, 所以怎么写一条sql语句可以让结果是这样的dailyrequest production Date Gradename
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m <---此处修改
40 0 NULL p1_3.8m
50 0 NULL p1_4.4m
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
非常感谢
解决方案 »
- 求SQL嵌套语句(小弟菜鸟,暂时没法给分)
- 分组问题,困挠多时。
- 请问数据库的名字中含有空格 用sql语句查询时该怎么写?
- javax.servlet.ServletException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.的问题
- 哪位老师帮我把这个存储过程减减肥,提高运行速度!
- real型的数据如何保留三位小数点?
- 循环访问数据库的问题
- [求助]sqlserver 活动监视器 中 有个进程老超时,导致程序前台无法登陆
- 如何用SQL语句实现查询功能?
- 请大力和高手们进来看看吧!一个小问题小弟没办法了!!
- 关于SQL嵌入式C语言,如何连接sql server
- 100分求一个数据库优化方法
如果是这样的情况, 就把b.date赋给这条数据
insert into @tabela select 30,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_5m'
union all select 50,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_2.5m'
union all select 40,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_3.8m'
union all select 50,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_4.4m'
declare @tableb table (production int,[date] datetime ,gradename nvarchar(20))
insert into @tableb select 40,'2009-1-12 11:42:56','p1_5m'
union all select 30,'2009-1-12 11:42:56','p1_2.2m'
union all select 50,'2009-1-12 11:42:56','p1_3.4m'select t.dailyrequest,t.production,isnull(x.date,t.date) [date],t.gradename
from (
select isnull(a.dailyrequest,0) dailyrequest,isnull(b.production,0) production,b.date [date],isnull(b.gradename,a.gradename) gradename from @tabela a full join @tableb b
on a.gradename=b.gradename and (date >= startdate and date <= finishdate)
) t left join
(
select distinct b.[date],a.gradename
from @tabela a,@tableb b
where (date >= startdate and date <= finishdate)
) as x
on t.gradename=x.gradename--结果
dailyrequest production date gradename
------------ ----------- ----------------------- --------------------
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m
40 0 NULL p1_3.8m
50 0 NULL p1_4.4m
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m(6 行受影响)
我开始以为都好了, 可我测试的时候发现另一个问题, 比如
declare @tabela table (dailyrequest int,startdate datetime,finishdate datetime,gradename nvarchar(20))
insert into @tabela select 30,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_5m'
union all select 50,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_2.5m'
union all select 40,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_3.8m'
union all select 50,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_4.4m'
declare @tableb table (production int,[date] datetime ,gradename nvarchar(20))
insert into @tableb select 40,'2009-1-12 11:42:56','p1_5m'
union all select 30,'2009-1-13 11:42:56','p1_5m'
union all select 30,'2009-1-12 11:42:56','p1_2.2m'
union all select 50,'2009-1-12 11:42:56','p1_3.4m'select t.dailyrequest,t.production,isnull(x.date,t.date) [date],t.gradename
from (
select isnull(a.dailyrequest,0) dailyrequest,isnull(b.production,0) production,b.date [date],isnull(b.gradename,a.gradename) gradename from @tabela a full join @tableb b
on a.gradename=b.gradename and (date >= startdate and date <= finishdate)
) t left join
(
select distinct b.[date],a.gradename
from @tabela a,@tableb b
where (date >= startdate and date <= finishdate)
) as x
on t.gradename=x.gradename他会出现重复 而且错误的 数据dailyrequest production Date Gradename
30 40 2009-01-12 11:42:56.000 p1_5m
30 40 2009-01-13 11:42:56.000 p1_5m <---此行错误
30 30 2009-01-12 11:42:56.000 p1_5m <---此行错误
30 30 2009-01-13 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m
50 0 2009-01-13 11:42:56.000 p1_2.5m
40 0 NULL p1_3.8m
50 0 NULL p1_4.4m
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m正确的是应该dailyrequest production Date Gradename
30 40 2009-01-12 11:42:56.000 p1_5m
30 30 2009-01-13 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m
50 0 2009-01-13 11:42:56.000 p1_2.5m
40 0 NULL p1_3.8m
50 0 NULL p1_4.4m
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
能再帮我看看吗? 非常感谢, 分数不是问题~!~
create table #1 (dailyrequest int,startdate datetime,finishdate datetime,gradename nvarchar(20))
insert into #1 select 30,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_5m'
union all select 50,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_2.5m'
union all select 40,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_3.8m'
union all select 50,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_4.4m'
select * from #1
create table #2 (production int,[date] datetime ,gradename nvarchar(20))
insert into #2 select 40,'2009-1-12 11:42:56','p1_5m'
union all select 30,'2009-1-13 11:42:56','p1_5m'
union all select 30,'2009-1-12 11:42:56','p1_2.2m'
union all select 50,'2009-1-12 11:42:56','p1_3.4m'
select DailyRequest=isnull(DailyRequest,0),Production=isnull(Production,0),Date=isnull(Date,0),Gradename = isnull(a_Gradename,b_Gradename) from
(
select a.DailyRequest,b.Production,a.gradename as a_gradename,b.date,b.gradename as b_gradename from #1 a left join #2 b on a.gradename = b.gradename where a.gradename in (
select distinct(a.gradename) from #1 a cross join #2 b where datediff(mi,a.startdate,b.date) > 0 and datediff(mi,b.date,a.finishdate)>0
)
union
select a.DailyRequest,b.Production,a.gradename as a_gradename,b.date,b.gradename as b_gradename from #1 a right join #2 b on a.gradename = b.gradename where b.gradename in (
select distinct(b.gradename) from #1 a cross join #2 b where datediff(mi,a.startdate,b.date) > 0 and datediff(mi,b.date,a.finishdate)>0
)
)tDailyRequest Production Date Gradename
------------ ----------- ----------------------- --------------------
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
30 30 2009-01-13 11:42:56.000 p1_5m
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 1900-01-01 00:00:00.000 p1_2.5m(5 row(s) affected)
我想做的结果是这样的,
我会先选择一个日期(以一天为单位),
比如说我选择1月12号,
那么在table b里头所有那天生产的gradename都要出现,
然后再去table a里头找关联, 只要table a里头的时间段在我所选择的日期内, a里头的gradename就要被选择,
然后a里头的gradename当天没有生产,即没有出现在b中, 这个a.gradename就用b.date的时间不知道大家明白了没有...
insert into @tabela select 30,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_5m'
union all select 50,'2009-1-10 1:42:56','2009-1-15 1:42:56','p1_2.5m'
union all select 40,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_3.8m'
union all select 50,'2008-10-2 2:03:08','2008-10-4 2:02:20','p1_4.4m'
declare @tableb table (production int,[date] datetime ,gradename nvarchar(20))
insert into @tableb select 40,'2009-1-12 11:42:56','p1_5m'
union all select 30,'2009-1-12 11:42:56','p1_2.2m'
union all select 50,'2009-1-12 11:42:56','p1_3.4m'select t.dailyrequest,t.production,isnull(x.date,t.date) [date],t.gradename
from (
select isnull(a.dailyrequest,0) dailyrequest,isnull(b.production,0) production,b.date [date],isnull(b.gradename,a.gradename) gradename from @tabela a full join @tableb b
on a.gradename=b.gradename and (date >= startdate and date <= finishdate)
) t left join
(
select distinct b.[date],a.gradename
from @tabela a,@tableb b
where (date >= startdate and date <= finishdate)
) as x
on t.gradename=x.gradename
create table #1 (dailyrequest int,startdate datetime,finishdate datetime,gradename nvarchar(20))
insert into #1 select 30,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_5m'
union all select 50,'10/JAN/2009 1:42:56','15/JAN/2009 1:42:56','p1_2.5m'
union all select 40,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_3.8m'
union all select 50,'2/DEC/2008 2:03:08','4/dec/2008 2:02:20','p1_4.4m'
select * from #1
create table #2 (production int,[date] datetime ,gradename nvarchar(20))
insert into #2 select 40,'2009-1-12 11:42:56','p1_5m'
union all select 30,'2009-1-13 11:42:56','p1_5m'
union all select 30,'2009-1-12 11:42:56','p1_2.2m'
union all select 50,'2009-1-12 11:42:56','p1_3.4m'select dailyrequest,startdate,finishdate,gradename = isnull(a.gradename,b.gradename),production,date into #3 from #1 a full join #2 b on a.gradename=b.gradenameselect isnull(dailyrequest,0) dailyrequest,isnull(production,0) production,isnull(date,0) [date],isnull(gradename,null) gradename
from #3 a where exists(select 1 from #3 where date >= a.startdate and date <= a.finishdate )
union
select isnull(dailyrequest,0) dailyrequest,isnull(production,0) production,isnull(date,0) [date],isnull(gradename,null) gradename
from #3 c where exists(select 1 from #3 where c.date >= startdate and c.date <= finishdate )dailyrequest production date gradename
------------ ----------- ----------------------- --------------------
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
30 30 2009-01-13 11:42:56.000 p1_5m
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 1900-01-01 00:00:00.000 p1_2.5m(5 row(s) affected)
谢谢 yangsnow_rain_wind
你的结果很好了, 可是由于
50 0 1900-01-01 00:00:00.000 p1_2.5m这条数据的时间不能用, 所以你能不能把结果变成
dailyrequest production date gradename
------------ ----------- ----------------------- --------------------
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
30 30 2009-01-13 11:42:56.000 p1_5m
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m
50 0 2009-01-13 11:42:56.000 p1_2.5m其实我只会选择一个时间, 但是因为要做一个VIEW, 所以不知道要怎么写
from #3 a where exists(select 1 from #3 where date >= a.startdate and date <= a.finishdate ) and [date]<>"1900-01-01 00:00:00.000"
union
select isnull(dailyrequest,0) dailyrequest,isnull(production,0) production,isnull(date,0) [date],isnull(gradename,null) gradename
from #3 c where exists(select 1 from #3 where c.date >= startdate and c.date <= finishdate ) and [date]<>"1900-01-01 00:00:00.000"
dailyrequest production date gradename
------------ ----------- ----------------------- --------------------
0 30 2009-01-12 11:42:56.000 p1_2.2m
0 50 2009-01-12 11:42:56.000 p1_3.4m
30 30 2009-01-13 11:42:56.000 p1_5m
30 40 2009-01-12 11:42:56.000 p1_5m
50 0 2009-01-12 11:42:56.000 p1_2.5m
50 0 2009-01-13 11:42:56.000 p1_2.5m