Table 1
Id the name of the product date
1 112 li 2020/3/1
1 113 li 2020/3/1
1 116 li 2020/6/1
King of 2 112 2020/3/1
King of 2 113 2020/2/1
Table 2
Name 2 name products
Li li 112
1Li li 113
1Li li 112
King, the king of 112
King, the king of 113
Table 3
The name 3 products
Li li 11 112
Li li 22 113
King of 11 112
King of 11 113
Table 4
Name s products amount date
Li 1 112 3 2020/1/2
1 113 5 li 2020/4/6
Li 112 2 2020/5/3
King 1 112 1 2020/8/1
King 1 113 6 2020/8/2
Li 11 112 2 2020/10/3
Li 22 113 3 2020/8/4
Wang 11 112 2 2020/7/5
Wang 11 113 1 2020/8/6
Draw a table a: table 4 date greater than table 1 date;
Id the name of the product date amount
1 112 li 2020/5/1 2
1 113 2020/3/1 8 lee
1 116 li 2020/6/1 0
King of 2 112 2020/3/1 3
King of 2 113 2020/2/1 7

CodePudding user response:

The truth didn't understand the logic, description is a bit of a problem;

Say want to come to table a: table 4 date than the date of the table 1
Single me from your results, if not see where the date is greater than in table 4 table 1 date

CodePudding user response:

In table 4 should be in four king?

CodePudding user response:

Date less than the date of table 1, table 4 are many contents in the table 4, four king should be in

CodePudding user response:

Why table a first line of the amount of 2? Table 4 2 112 li and li 11 112 should meet the requirements? Sum should be 4

 
The CREATE TABLE # A (
ID INT, 
The NAME VARCHAR (20), 
The PRODUCT INT, 
DDATE DATE 
) 
The CREATE TABLE # B (
NAME2 VARCHAR (20), 
The NAME VARCHAR (20), 
The PRODUCT INT 
) 
The CREATE TABLE # C (
NAME3 VARCHAR (20), 
The NAME VARCHAR (20), 
The PRODUCT INT 
) 
The CREATE TABLE # D (
NAMES VARCHAR (20), 
The PRODUCT INT, 
The 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, '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') 

The SELECT Anderson D, A.N AME, Amy polumbo RODUCT, LEFT (ISNULL (MIN (XX) DDATE), MIN (a. d. DATE)), 7) + '1' DDATE, ISNULL (SUM (XX) AMOUNT), 0) AMOUNT FROM # aa LEFT JOIN (
SELECT 
ISNULL (
(SELECT MAX (B.N AME) FROM # B B WHERE B.N AME2=D.N AMES AND p. RODUCT=D.P RODUCT), 
(SELECT MAX (AME) FROM C # C WHERE C.N AME3=D.N AMES AND C.P RODUCT=D.P RODUCT)) NAME, D.P RODUCT, da MOUNT, D.D DATE 
The FROM # D D) XX ON A.N AME=XX. The NAME AND Amy polumbo RODUCT=XX. The PRODUCT AND a. d. DATEGROUP BY Anderson, D, A.N AME, Amy polumbo RODUCT 

# # # DROP TABLE A, B, C, # D 

CodePudding user response:

Table 1 "112 li" is the date of the corresponding should be 2020/05/01

Guess a

 
IF OBJECT_ID (N 'TEMPDB for. DBO. # T1) IS NOT NULL 
# DROP TABLE T1 
GO 

# the CREATE TABLE T1 
(ID VARCHAR (5), 
The 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 for. DBO. # T2') IS NOT NULL 
DROP TABLE # T2 
GO 

The CREATE TABLE # T2 
(NAME2 VARCHAR (10), 
The NAME VARCHAR (10), 
The 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 for. DBO. # T3) IS NOT NULL 
The DROP TABLE # T3 
GO 

The CREATE TABLE # T3 
(NAME3 VARCHAR (10), 
The NAME VARCHAR (10), 
The 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 for. DBO. # T4) IS NOT NULL 
DROP TABLE # T4 
GO 

The CREATE TABLE # T4 
(NAMES VARCHAR (10), 
ITEM VARCHAR (10), 
The AMOUNT INT, 
DT DATE) 

INSERT INTO # T4 
SELECT 'L1', '112', 3, '2020-01-02' UNION ALL 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull