字符拼接问题

[小野马1209]

问题描述：
有@#A和@#B两个表，现在需要拼接B表的PersonID ，拼接后的结果为什么把表2所有的名字都拼接了，条件不是限定了BillNo吗

declare @#A table (BillNo varchar(20),Remark varchar(100))
--插入数据
insert into @#A
select '001','' UNION ALL
select '002',''

declare @#B table (BillNo varchar(20),PersonID varchar(100))
--插入数据
INSERT INTO @#B 
select '001','张三' UNION ALL
select '001','李四' UNION ALL
select '002','王五'
------------------测试数据结束

SELECT A.BillNo,REMARK=STUFF((SELECT ',' + PERSONID from @#A AA Inner JOIN @#B BB
ON BB.BillNo=AA.BillNo FOR XML PATH('')),1,1,'') from @#A A INNER JOIN @#B B
ON B.BillNo=A.BillNo GROUP BY A.BillNo

想要的结果
BillNo  Remark
0001    张三,李四
0002    王五

[二月十六]

declare @#A table (BillNo varchar(20),Remark varchar(100))
--插入数据
insert into @#A
select '001','' UNION ALL
select '002',''
 
declare @#B table (BillNo varchar(20),PersonID varchar(100))
--插入数据
INSERT INTO @#B 
select '001','张三' UNION ALL
select '001','李四' UNION ALL
select '002','王五'
------------------测试数据结束
 
SELECT  A.BillNo ,
        REMARK = STUFF(( SELECT ',' + PERSONID
                         FROM   @#B BB
                         WHERE  BB.BillNo = A.BillNo
                       FOR
                         XML PATH('')
                       ), 1, 1, '')  --外边A表数据作为条件读取B表数据
FROM    @#A A  --不用关联B表

 

[小野马1209]

declare @#A table (BillNo varchar(20),Remark varchar(100))
--插入数据
insert into @#A
select '001','' UNION ALL
select '002',''

declare @#B table (BillNo varchar(20),PersonID varchar(100))
--插入数据
INSERT INTO @#B 
select '001','张三' UNION ALL
select '001','李四' UNION ALL
select '002','王五'
------------------测试数据结束

SELECT A.BillNo,REMARK=STUFF((SELECT ',' + PERSONID from @#A AA Inner JOIN @#B BB
ON BB.BillNo=AA.BillNo FOR XML PATH('')),1,1,'') from @#A A INNER JOIN @#B B
ON B.BillNo=A.BillNo 

想要的结果
BillNo  Remark
0001    张三,李四
0002    王五

[小野马1209]

declare @#A table (BillNo varchar(20),Remark varchar(100))
--插入数据
insert into @#A
select '001','' UNION ALL
select '002',''

declare @#B table (BillNo varchar(20),PersonID varchar(100))
--插入数据
INSERT INTO @#B 
select '001','张三' UNION ALL
select '001','李四' UNION ALL
select '002','王五'
------------------测试数据结束

SELECT A.BillNo,REMARK=STUFF((SELECT ',' + PERSONID from @#A AA Inner JOIN @#B BB
ON BB.BillNo=AA.BillNo FOR XML PATH('')),1,1,'') from @#A A INNER JOIN @#B B
ON B.BillNo=A.BillNo GROUP BY A.BillNo

想要的结果
BillNo  Remark
0001    张三,李四
0002    王五

[吉普赛的歌]

DECLARE @#A TABLE (BillNo VARCHAR(20), Remark VARCHAR(100))
--插入数据
INSERT INTO @#A
SELECT '001','' 
UNION ALL
SELECT '002',''

DECLARE @#B TABLE (BillNo VARCHAR(20), PersonID VARCHAR(100))
--插入数据
INSERT INTO @#B
SELECT '001','张三' UNION ALL
SELECT '001','李四' UNION ALL
SELECT '002','王五'
------------------测试数据结束

SELECT 
	a.BillNo
	,STUFF((SELECT ','+b.PersonID 
	        FROM @#B AS b 
	        WHERE a.BillNo=b.BillNo FOR XML PATH('')),1,1,'') AS [Remark]
FROM @#A AS a
/*
BillNo	Remark
001	    张三,李四
002	    王五
*/