|
1.创建表单CREATE TABLE RIS_AcceptItems ( applyno?? ?INT, itemcode VARCHAR(100) )
2.插入数据?
insert into RIS_AcceptItems values(959,'TJ56')
insert into RIS_AcceptItems values(959,'TJ58')
insert into RIS_AcceptItems values(1041,'TJ66')
insert into RIS_AcceptItems values(1041,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1378')
insert into RIS_AcceptItems values(1097,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1378')
insert into RIS_AcceptItems values(1102,'CH1352')
insert into RIS_AcceptItems values(1102,'CH1378')
insert into RIS_AcceptItems values(1105,'CH1352')
insert into RIS_AcceptItems values(1105,'CH1378')
insert into RIS_AcceptItems values(1105,'CH1352')?
3.查询数据? select applyno,stuff((select distinct ','+itemcode from RIS_AcceptItems where a.applyno=applyno for xml path('')),1,1,'') itemcode from RIS_AcceptItems a group by applyno
--查询结果
applyno?? ?itemcode
959?? ?TJ56,TJ58
1041?? ?CH1352,TJ66
1097?? ?CH1352,CH1378
1102?? ?CH1352,CH1378
1105?? ?CH1352,CH1378
4.查询的时候，用,作为分隔符，进行查询
DECLARE @FHCS VARCHAR(50)
set @FHCS = '1，2,3'
set @FHCS= REPLACE(@FHCS,'，',',') ?-- 将入参的中文分号转换成英文分号。统一类型
?SELECT * ?FROM table A?
?inner JOIN [dbo].[fn_SplitTSQL](@FHCS,',') ?b on ?a.itemcode?= b.ELEMENT
WHERE 1=1? ??
|