表1
id 名稱 產品 日期
1 李 112 2020/3/1
1 李 113 2020/3/1
1 李 116 2020/6/1
2 王 112 2020/3/1
2 王 113 2020/2/1
表2
名稱2 名稱 產品
李1 李 112
李1 李 113
李2 李 112
王1 王 112
王1 王 113
表3
名稱3 名稱 產品
李11 李 112
李22 李 113
王11 王 112
王11 王 113
表4
名稱s 產品 金額 日期
李1 112 3 2020/1/2
李1 113 5 2020/4/6
李2 112 2 2020/5/3
王1 112 1 2020/8/1
王1 113 6 2020/8/2
李11 112 2 2020/10/3
李22 113 3 2020/8/4
王11 112 2 2020/7/5
王11 113 1 2020/8/6
得出表a：表4的日期大于表1的日期；
id 名稱 產品 日期 金額
1 李 112 2020/5/1 2
1 李 113 2020/3/1 8
1 李 116 2020/6/1 0
2 王 112 2020/3/1 3
2 王 113 2020/2/1 7
uj5u.com熱心網友回復：
這個邏輯說實話沒看懂，描述的是不是有點問題；說是想要得出表a：表4的日期大于表1的日期
單我從你的結果，好像沒看出 表4的日期 哪里大于表1的日期了
uj5u.com熱心網友回復：
表4里是不是應該四個王都在？uj5u.com熱心網友回復：
表4的日期小于表1的日期，表4里的內容很多，四個王都應該在uj5u.com熱心網友回復：
表a的第一行金額為什么是2？表4的李2 112和李11 112都應該符合要求吧？求和應該是4
CREATE TABLE #A(
ID INT,
NAME VARCHAR(20),
PRODUCT INT,
DDATE DATE
)
CREATE TABLE #B(
NAME2 VARCHAR(20),
NAME VARCHAR(20),
PRODUCT INT
)
CREATE TABLE #C(
NAME3 VARCHAR(20),
NAME VARCHAR(20),
PRODUCT INT
)
CREATE TABLE #D(
NAMES VARCHAR(20),
PRODUCT INT,
AMOUNT INT,
DDATE DATE
)
INSERT INTO #A VALUES (1,'LI',112,'2020-3-1')
INSERT INTO #A VALUES (1,'LI',113,'2020-3-1')
INSERT INTO #A VALUES (1,'LI',116,'2020-6-1')
INSERT INTO #A VALUES (2,'WANG',112,'2020-3-1')
INSERT INTO #A VALUES (2,'WANG',113,'2020-2-1')
INSERT INTO #B VALUES ('LI1','LI',112)
INSERT INTO #B VALUES ('LI1','LI',113)
INSERT INTO #B VALUES ('LI2','LI',112)
INSERT INTO #B VALUES ('WANG1','WANG',112)
INSERT INTO #B VALUES ('WANG1','WANG',113)
INSERT INTO #C VALUES ('LI11','LI',112)
INSERT INTO #C VALUES ('LI22','LI',113)
INSERT INTO #C VALUES ('WANG11','WANG',112)
INSERT INTO #C VALUES ('WANG11','WANG',113)
INSERT INTO #D VALUES ('LI1',112,3,'2020-1-2')
INSERT INTO #D VALUES ('LI1',113,5,'2020-4-6')
INSERT INTO #D VALUES ('LI2',112,2,'2020-5-3')
INSERT INTO #D VALUES ('WANG1',112,1,'2020-8-1')
INSERT INTO #D VALUES ('WANG1',113,6,'2020-8-2')
INSERT INTO #D VALUES ('LI11',112,2,'2020-10-3')
INSERT INTO #D VALUES ('LI22',113,3,'2020-8-4')
INSERT INTO #D VALUES ('WANG11',112,2,'2020-7-5')
INSERT INTO #D VALUES ('WANG11',113,1,'2020-8-6')
SELECT A.ID,A.NAME,A.PRODUCT,LEFT(ISNULL(MIN(XX.DDATE),MIN(A.DDATE)),7)+'-1' DDATE,ISNULL(SUM(XX.AMOUNT),0) AMOUNT FROM #A A LEFT JOIN (
SELECT
ISNULL(
(SELECT MAX(B.NAME) FROM #B B WHERE B.NAME2 = D.NAMES AND B.PRODUCT = D.PRODUCT),
(SELECT MAX(C.NAME) FROM #C C WHERE C.NAME3 = D.NAMES AND C.PRODUCT = D.PRODUCT)) NAME,D.PRODUCT,D.AMOUNT,D.DDATE
FROM #D D) XX ON A.NAME = XX.NAME AND A.PRODUCT = XX.PRODUCT AND A.DDATE<XX.DDATE
GROUP BY A.ID,A.NAME,A.PRODUCT
DROP TABLE #A,#B,#C,#D
uj5u.com熱心網友回復：
表1的“李 112”對應的日期是不是應該是2020/05/01猜一個
IF OBJECT_ID(N'TEMPDB.DBO.#T1') IS NOT NULL
DROP TABLE #T1
GO
CREATE TABLE #T1
(ID VARCHAR(5),
NAME VARCHAR(10),
ITEM VARCHAR(10),
DT DATE)
INSERT INTO #T1
SELECT '1','L','112','2020-03-01' UNION ALL
SELECT '1','L','113','2020-03-01' UNION ALL
SELECT '1','L','116','2020-06-01' UNION ALL
SELECT '2','W','112','2020-03-01' UNION ALL
SELECT '2','W','113','2020-02-01'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#T2') IS NOT NULL
DROP TABLE #T2
GO
CREATE TABLE #T2
(NAME2 VARCHAR(10),
NAME VARCHAR(10),
ITEM VARCHAR(10))
INSERT INTO #T2
SELECT 'L1','L','112' UNION ALL
SELECT 'L1','L','113' UNION ALL
SELECT 'L2','L','112' UNION ALL
SELECT 'W1','W','112' UNION ALL
SELECT 'W1','W','113'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#T3') IS NOT NULL
DROP TABLE #T3
GO
CREATE TABLE #T3
(NAME3 VARCHAR(10),
NAME VARCHAR(10),
ITEM VARCHAR(10))
INSERT INTO #T3
SELECT 'L11','L','112' UNION ALL
SELECT 'L22','L','113' UNION ALL
SELECT 'W11','W','112' UNION ALL
SELECT 'W11','W','113'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#T4') IS NOT NULL
DROP TABLE #T4
GO
CREATE TABLE #T4
(NAMES VARCHAR(10),
ITEM VARCHAR(10),
AMOUNT INT,
DT DATE)
INSERT INTO #T4
SELECT 'L1','112',3,'2020-01-02' UNION ALL
SELECT 'L1','113',5,'2020-04-06' UNION ALL
SELECT 'L2','112',2,'2020-05-03' UNION ALL
SELECT 'W1','112',1,'2020-08-01' UNION ALL
SELECT 'W1','113',6,'2020-08-02' UNION ALL
SELECT 'L11','112',2,'2020-10-03' UNION ALL
SELECT 'L22','113',3,'2020-08-04' UNION ALL
SELECT 'W11','112',2,'2020-07-05' UNION ALL
SELECT 'W11','113',1,'2020-08-06'
GO
SELECT D.ID,D.NAME,D.ITEM,D.DT,ISNULL(SUM(A.AMOUNT),0)
FROM #T4 A
LEFT JOIN #T2 B ON A.NAMES=B.NAME2 AND A.ITEM=B.ITEM
LEFT JOIN #T3 C ON A.NAMES=C.NAME3 AND A.ITEM=C.ITEM
RIGHT JOIN #T1 D ON ISNULL(B.NAME,C.NAME)=D.NAME AND ISNULL(B.ITEM,C.ITEM)=D.ITEM
GROUP BY D.ID,D.NAME,D.DT,D.ITEM
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/257955.html
標籤：應用實例
上一篇：Vfp 10個數快速排序法如何撰寫演算法從小到大排序,請幫忙
下一篇：求助