Two SQL table-CodePudding

Table 1: name door code electricity date
Floret, 112114118, 10000 the 2020-1-1
Floret, 112114116, 15000 the 2020-8-1
Zhang, 115114117, 35000 the 2020-1-13
Zhang, 112114118, 23000 the 2020-6-10
Table 2: name door code electricity date
Floret 112 30 2020-1-3
Floret 113 50 2020-1-10
Floret 20 2020-1-13 114
Floret 114 10 2020-1-23
Floret 118 40 2020-2-3
Zhang SAN 115 60 2020-1-3

Table 3 in table 2 date 2020.1.1-2020.4.1
Such as:
(table 1) name (table 1) door code (table 2) electricity
Floret, 112114118116, 100

CodePudding user response:

The first table households have repeat code

CodePudding user response:

Good table is connected, but the string to merge is a bit...

CodePudding user response:

The first table code is repeated, need to go to the heavy, although the two tables is linked together, but it is really complicated

CodePudding user response:

Strives for the great god answer,,,

CodePudding user response:

reference baidu_37188404 reply: 3/f

the first table code is repeated, need to go to the heavy, although the two tables is linked together, but things were complicated

No STRING_SPLIT function had to write a similar function..

 
The create function xxsplit (@ SQL varchar (MAX), @ xx varchar (20)) 
Returns the @ temp table (value varchar (20)) 
As 
The begin 
Declare @ I int 
Xx, set @ I=charindex (@ @ SQL) 
While @ i>=1 
The begin 
Insert the @ temp values (left (SQL, @ @ I - 1)) 
Set @ SQL=the substring (SQL, @ @ I + 1, len (SQL) @ @ I) 
Xx, set @ I=charindex (@ @ SQL) 
End 
Insert into @ temp values (@ SQL) 
Return 
End

 
The CREATE TABLE # A 
(
The NAME VARCHAR (20), 
HUMA VARCHAR (50), 
DIANFEI INT, 
RIQI DATE 
) 
The CREATE TABLE # B 
(
The NAME VARCHAR (20), 
HUMA VARCHAR (50), 
DIANFEI INT, 
RIQI DATE 
) 
# INSERT INTO A VALUES (' flowers', '112114118', 10000, '2020-1-1') 
# INSERT INTO A VALUES (' flowers', '112114116', 15000, '2020-8-1') 
# INSERT INTO A VALUES (' zhang ', '115114117', 35000, '2020-1-13') 
# INSERT INTO A VALUES (' zhang ', '112114118', 23000, '2020-6-10') 

INSERT INTO # B VALUES (30, 'flowers',' 112 ', '2020-1-3') 
INSERT INTO # B VALUES (' flowers', '113', 50, '2020-1-10') 
INSERT INTO # B VALUES (' flowers', '114', 20, '2020-1-13') 
INSERT INTO # B VALUES (' flowers', '114', 10, '2020-1-23') 
INSERT INTO # B VALUES (' flowers', '118', 40, '2020-2-3') 
INSERT INTO # B VALUES (' zhang ', '115', 60, '2020-1-3') 

DECLARE @ the START DATE, @ END DATE 
The SET @ START='2020.1.1' 
The SET @ END='2020.3.1' 
SELECT AA. The NAME NAME, 
STUFF ((SELECT DISTINCT ', '+ value FROM xxsplit (MAX (AA. HUMA),', ') FOR XML PATH (' ')), 1, 2, ' ') registered permanent residence, 
The SUM (BB) DIANFEI) electricity FROM (
The SELECT A.N AME, 
(SELECT STUFF ((SELECT the ', '+ HUMA FROM # B, WHERE B.N AME=A.N AME FOR XML PATH ('')), 1, 0, ')) + ', 'HUMA 
The FROM # AA GROUP BY A.N AME) AA, BB # B 
WHERE AA. NAME=BB. NAME AND CHARINDEX ((', '+ BB. HUMA +', '), AA. HUMA)!=0 
AND BB. RIQI BETWEEN @ START AND @ END 
GROUP BY AA. The NAME 

DROP TABLE # A, # B