sql 两表相连

CREATE TABLE #A
(
NAME VARCHAR(20),
HUMA VARCHAR(50),
DIANFEI INT,
RIQI DATE
)
CREATE TABLE #B
(
NAME VARCHAR(20),
HUMA VARCHAR(50),
DIANFEI INT,
RIQI DATE
)
INSERT INTO #A VALUES (‘小花’,‘112,114,118’,10000,‘2020-1-1’)
INSERT INTO #A VALUES (‘小花’,‘112,114,116’,15000,‘2020-8-1’)
INSERT INTO #A VALUES (‘张三’,‘115,114,117’,35000,‘2020-1-13’)
INSERT INTO #A VALUES (‘张三’,‘112,114,118’,23000,‘2020-6-10’)

INSERT INTO #B VALUES (‘小花’,‘112’,30,‘2020-1-3’)
INSERT INTO #B VALUES (‘小花’,‘113’,50,‘2020-1-10’)
INSERT INTO #B VALUES (‘小花’,‘114’,20,‘2020-1-13’)
INSERT INTO #B VALUES (‘小花’,‘114’,10,‘2020-1-23’)
INSERT INTO #B VALUES (‘小花’,‘118’,40,‘2020-2-3’)
INSERT INTO #B VALUES (‘张三’,‘115’,60,‘2020-1-3’)

DECLARE @START DATE,@END DATE
SET @START = ‘2020.1.1’
SET @END = ‘2020.3.1’
SELECT AA.NAME 姓名,
STUFF((SELECT DISTINCT ‘,’+value FROM xxsplit(MAX(AA.HUMA),’,’) FOR XML PATH(’’)),1,2,’’) 户口,
SUM(BB.DIANFEI) 电费 FROM (
SELECT A.NAME,
(SELECT STUFF((SELECT ‘,’+HUMA FROM #A B WHERE B.NAME=A.NAME FOR XML PATH (’’)),1,0,’’))+’,’ HUMA
FROM #A A GROUP BY A.NAME) AA,#B BB
WHERE AA.NAME = BB.NAME AND CHARINDEX((’,’+BB.HUMA+’,’),AA.HUMA) != 0
AND BB.RIQI BETWEEN @START AND @END
GROUP BY AA.NAME

DROP TABLE #A,#B