22,210
社区成员
发帖
与我相关
我的任务
分享
--------------- 测试数据开始 ----------------
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
CREATE TABLE t1(sqty DECIMAL(10,3),sex2 CHAR(8))
CREATE TABLE t2(qty DECIMAL(10,3),ex2 CHAR(8))
INSERT INTO t1 VALUES (33.042,'20150819')
INSERT INTO t1 VALUES (1.844,'20151014')
INSERT INTO t1 VALUES (4.503,'20151023')
INSERT INTO t1 VALUES (0.94,'20151103')
INSERT INTO t1 VALUES (3.358,'20151104')
INSERT INTO t1 VALUES (5.478,'20151106')
INSERT INTO t1 VALUES (5.804,'20151106')
INSERT INTO t1 VALUES (8.941,'20151109')
INSERT INTO t1 VALUES (1.808,'20151109')
INSERT INTO t1 VALUES (3.703,'20151110')
--
INSERT INTO t2 VALUES (40.369,'20150819')
INSERT INTO t2 VALUES (39.165,'20151030')
INSERT INTO t2 VALUES (39.498,'20151111')
INSERT INTO t2 VALUES (39.938,'20151120')
INSERT INTO t2 VALUES (40.081,'20151207')
INSERT INTO t2 VALUES (39.803,'20151214')
INSERT INTO t2 VALUES (39.793,'20151229')
INSERT INTO t2 VALUES (40.234,'20160111')
INSERT INTO t2 VALUES (38.078,'20160126')
INSERT INTO t2 VALUES (39.673,'20160226')
GO
--------------- 测试数据结束 ----------------
;WITH cte1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY ex2) AS rid,* FROM t2
),cte2 AS (
SELECT a.qty,a.ex2 AS beginTime,ISNULL(b.ex2,'2200-01-01') AS endTime FROM cte1 AS a LEFT JOIN cte1 AS b ON a.rid=b.rid-1
)
SELECT a.qty
,b.sqty
,b.sex2
FROM cte2 AS a CROSS APPLY t1 AS b
WHERE b.sex2 BETWEEN a.beginTime AND a.endTime
/*
qty sqty ex2
40.369 33.042 20150819
40.369 1.844 20151014
40.369 4.503 20151023
39.165 0.940 20151103
39.165 3.358 20151104
39.165 5.478 20151106
39.165 5.804 20151106
39.165 8.941 20151109
39.165 1.808 20151109
39.165 3.703 20151110
*/
select qty,sqty,ISNULL(sex2,ex2) as ex2
from t1 as A
full join t2 as B ON A.sex2=B.ex2