这个问题已困扰偶多时
现在好像马上就要解决了,但还差一点请看以下两表:Tab1.Day Tab1.Data
20120201 23
20120202 45
20120203 12
20120204 34
20120205 54
20120206 75
20120207 35
20120208 23
20120209 22
20120210 89Tab2.Day Tab2.Data
20120201 3
20120204 5
20120206 4要求用SQL语句输出以下结果:
20120201 23 3
20120202 45 3
20120203 12 3
20120204 34 5
20120205 54 5
20120206 75 4
20120207 35 4
20120208 23 4
20120209 22 4
20120210 89 4偶想这样:SELECT Tab1.Day, Tab1.Data, MAX(Tab2.Day) FROM Tab1, Tab2 WHERE Tab1.Day>=Tab2.Day GROUP BY Tab1.Day;这是偶能想出来的最接近目标的语句,希望高手能帮偶再进一步,谢谢!
现在好像马上就要解决了,但还差一点请看以下两表:Tab1.Day Tab1.Data
20120201 23
20120202 45
20120203 12
20120204 34
20120205 54
20120206 75
20120207 35
20120208 23
20120209 22
20120210 89Tab2.Day Tab2.Data
20120201 3
20120204 5
20120206 4要求用SQL语句输出以下结果:
20120201 23 3
20120202 45 3
20120203 12 3
20120204 34 5
20120205 54 5
20120206 75 4
20120207 35 4
20120208 23 4
20120209 22 4
20120210 89 4偶想这样:SELECT Tab1.Day, Tab1.Data, MAX(Tab2.Day) FROM Tab1, Tab2 WHERE Tab1.Day>=Tab2.Day GROUP BY Tab1.Day;这是偶能想出来的最接近目标的语句,希望高手能帮偶再进一步,谢谢!
data2=(select top 1 data from tab2 where day<=a.day order by day desc)
from tab1 a
从2月4日起是4;
从2月6日起是5
--xiugai
select [day],data,
data2=(select top 1 data from tab2 where a.[day]<=[day] order by [day] desc)
from tab1 a
“top”是关键词吗?
可以写成“[day]”?
SELECT Tab1.Day, Tab1.Data, Tab2.Data FROM Tab1, Tab2, Tab3 WHERE Tab1.Day=Tab3.Day1 AND Tab2.Day=Tab3.Day2;
偶把问题再简化一下,希望高手赐教:请看以下两表:Tab1.Day
20120201
20120202
20120203
20120204
20120205
20120206
20120207
20120208
20120209
20120210 Tab2.Day Tab2.Data
20120201 3
20120204 5
20120206 4要求用SQL语句输出以下结果:
20120201 3
20120202 3
20120203 3
20120204 5
20120205 5
20120206 4
20120207 4
20120208 4
20120209 4
20120210 4
20120202 20120201
20120203 20120201
20120204 20120204
20120205 20120204
20120206 20120206
20120207 20120206
20120208 20120206
20120209 20120206
20120210 20120206好像很接近答案,但偶就是没法变出那一列
--> 测试数据:[tbl1]
if object_id('[tbl1]') is not null drop table [tbl1]
create table [tbl1]([Day] datetime,[Data] int)
insert [tbl1]
select '20120201',23 union all
select '20120202',45 union all
select '20120203',12 union all
select '20120204',34 union all
select '20120205',54 union all
select '20120206',75 union all
select '20120207',35 union all
select '20120208',23 union all
select '20120209',22 union all
select '20120210',89
--> 测试数据:[tbl2]
if object_id('[tbl2]') is not null drop table [tbl2]
create table [tbl2]([Day] datetime,[Data] int)
insert [tbl2]
select '20120201',3 union all
select '20120204',5 union all
select '20120206',4
select c.daya as [Day],c.[Data],d.[Data] from(
select a.[Day] as daya,a.[Data],case
when a.[Day]<'20120204' then 1
when a.[Day] between '20120204' and '20120205' then 2
else 3 end as [state] from tbl1 a)c
inner join(select *,
case when a.[Day]='20120201' then 1
when a.[Day]='20120204' then 2
when a.[Day]='20120206' then 3
else 0 end as [state] from tbl2 a)d
on c.[state]=d.[state]/*
Day Data Data
2012-02-01 00:00:00.000 23 3
2012-02-02 00:00:00.000 45 3
2012-02-03 00:00:00.000 12 3
2012-02-04 00:00:00.000 34 5
2012-02-05 00:00:00.000 54 5
2012-02-06 00:00:00.000 75 4
2012-02-07 00:00:00.000 35 4
2012-02-08 00:00:00.000 23 4
2012-02-09 00:00:00.000 22 4
2012-02-10 00:00:00.000 89 4
*/
那这个去inner join 一下你的第二张表
insert into tab1 values('20120201', 23)
insert into tab1 values('20120202', 45)
insert into tab1 values('20120203', 12)
insert into tab1 values('20120204', 34)
insert into tab1 values('20120205', 54)
insert into tab1 values('20120206', 75)
insert into tab1 values('20120207', 35)
insert into tab1 values('20120208', 23)
insert into tab1 values('20120209', 22)
insert into tab1 values('20120210', 89)
create table Tab2([Day] varchar(10),Data int)
insert into tab2 values('20120201', 3)
insert into tab2 values('20120204', 5)
insert into tab2 values('20120206', 4)
goselect m.* , data = (select top 1 data from tab2 n where n.[Day] <= m.[Day] order by n.[day] desc) from tab1 mdrop table tab1 , tab2/*
Day Data data
---------- ----------- -----------
20120201 23 3
20120202 45 3
20120203 12 3
20120204 34 5
20120205 54 5
20120206 75 4
20120207 35 4
20120208 23 4
20120209 22 4
20120210 89 4(所影响的行数为 10 行)
*/
select Tab1.[Day], Tab1.Data,
data2=(select top 1 Tab2.Data from Tab2 where Tab2.[Day]>=Tab1.[Day])
from Tab1
where Tab2.Day=(select MAX(Tab2.Day) from Tab2 where Tab1.Day>=Tab2.Day);以上语句应该可以了,明天再好好理解理解(偶要睡了)
感谢各位参与!
顺便问一下各位用的是什么数据库?
偶用的是Linux下的mysql