27,579
社区成员
发帖
与我相关
我的任务
分享
--表A
SELECT 1 AS ITEM, '07'AS PRONO, '2015-8-1'AS DAT, 5.4 AS PRI UNION ALL
SELECT 2 AS ITEM, '07'AS PRONO, '2015-8-18'AS DAT, 6.2 AS PRI UNION ALL
SELECT 3 AS ITEM, '07'AS PRONO, '2015-8-28'AS DAT, 6.8 AS PRI
--表B
SELECT 1 AS ITEM, '07'AS PRONO, '2015-8-13'AS DAT UNION ALL
SELECT 2 AS ITEM, '07'AS PRONO, '2015-8-17'AS DAT UNION ALL
SELECT 3 AS ITEM, '07'AS PRONO, '2015-8-19'AS DAT UNION ALL
SELECT 4 AS ITEM, '07'AS PRONO, '2015-8-29'AS DAT
/*
规则:
表B的第1,2条日期是在>表A的第1条,<表A的第2条,取第1条的金额
表B的第3条的日期是在>表A的第2条,<表A的第3条,取第2条的金额
表B的第4条的日期是在>表A的第3条,取第3条的金额
想得到的结果是表B 能抓到表A的金额:
1 07 2015-8-13 5.4
2 07 2015-8-17 5.4
3 07 2015-8-19 6.2
4 07 2015-8-29 6.8
*/
DECLARE @TA TABLE (ITEM INT, PRONO CHAR(2), DAT DATE, PRI FLOAT)
DECLARE @TB TABLE (ITEM INT, PRONO CHAR(2), DAT DATE)
--表A
INSERT INTO @TA
SELECT 1 AS ITEM, '07'AS PRONO, '2015-8-1'AS DAT, 5.4 AS PRI UNION ALL
SELECT 2 AS ITEM, '07'AS PRONO, '2015-8-18'AS DAT, 6.2 AS PRI UNION ALL
SELECT 3 AS ITEM, '07'AS PRONO, '2015-8-28'AS DAT, 6.8 AS PRI
--表B
INSERT INTO @TB
SELECT 1 AS ITEM, '07'AS PRONO, '2015-8-13'AS DAT UNION ALL
SELECT 2 AS ITEM, '07'AS PRONO, '2015-8-17'AS DAT UNION ALL
SELECT 3 AS ITEM, '07'AS PRONO, '2015-8-19'AS DAT UNION ALL
SELECT 4 AS ITEM, '07'AS PRONO, '2015-8-29'AS DAT
SELECT *, (SELECT TOP 1 PRI FROM @TA A WHERE A.DAT < B.DAT ORDER BY DAT DESC)
FROM @TB B