首頁猿問 SQL語句新問題

SQL語句新問題

MySQL SQL Server

繁華開滿天機 2018-07-23 06:23:22

A表ID Name Time1 123 2008-1-11 123 2008-1-21 123 2008-1-31 123 2008-1-41 123 2008-1-51 123 2008-1-6 B表ID V1 V2 V3 Time1 0 0 0 2008-1-11 0 0 0 2008-1-21 0 0 0 2008-1-51 0 0 0 2008-1-10查詢結(jié)果：（）為說明a.ID Name a.Time V1 V2 V3 b.Time1 123 2008-1-1 2008-1-1（?。卤碇袑臄?shù)據(jù)）1 123 2008-1-2 2008-1-2（取Ｂ表中對應的數(shù)據(jù)）1 123 2008-1-3 2008-1-2（向下?。卤碇凶罱臄?shù)據(jù)）1 123 2008-1-4 2008-1-2（向下取Ｂ表中最近的數(shù)據(jù)）1 123 2008-1-5 2008-1-5（?。卤碇袑臄?shù)據(jù)）1 123 2008-1-6 2008-1-5（向下取Ｂ表中最近的數(shù)據(jù)）Ａ表中數(shù)據(jù)查詢出來，再找與之對應Ｂ表中匹配時間相同的數(shù)據(jù)，時間不存在，則向下取最近的數(shù)據(jù)？想了一上午沒法解決？現(xiàn)在懷疑能不能用ＳＱＬ寫出來ＰＳ：不能寫：select A.*,(select top 1 v1 from B where B.ID=A.ID and B.time<=A.Time order by B.Time desc) as V1from A這樣的語句

查看完整描述

2 回答

互換的青春

TA貢獻1797條經(jīng)驗獲得超6個贊

DECLARE @a table(id int ,[name] varchar(10), [time] datetime) DECLARE @b table(id int ,v1 int ,v2 int, v3 int ,[time] datetime) INSERT INTO @a SELECT 1, '123', '2008-1-1' UNION ALL SELECT 1, '123', '2008-1-2' UNION ALL SELECT 1, '123', '2008-1-3' UNION ALL SELECT 1, '123', '2008-1-4' UNION ALL SELECT 1, '123', '2008-1-5' UNION ALL SELECT 1, '123', '2008-1-6' INSERT INTO @B SELECT 1, 0, 0, 0, '2008-1-1' UNION ALL SELECT 1, 0, 0, 0, '2008-1-2' UNION ALL SELECT 1, 0, 0, 0, '2008-1-5' UNION ALL SELECT 1, 0, 0, 0, '2008-1-10' --METHOD 1 SELECT a.*, (SELECT MAX(b.[time]) FROM @b b where b.id=a.id and DATEDIFF(day,a.[time],b.[time])<=0 ) as [time] FROM @a a --METHOD2 SELECT a.*, (SELECT TOP 1 b.[time] FROM @b b where b.id=a.id and DATEDIFF(day,a.[time],b.[time])<=0 ORDER BY b.[time] DESC ) as [time] FROM @a a --METHOD3 ......

反對回復 2018-07-25