- SQL code
create table tb (单别 char(2),单号 char(4),料号 char(20) )insert into tb values ('A1','1001','ABC')insert into tb values ('A1','1001','DEF')create table tb1 (存货 char(20),规格 char(20),数量 int)insert into tb1 values ('CPU',[email protected]',100)SELECT * FROM tbSELECT * FROM tb1--单别 单号 料号A1 1001 ABC A1 1001 DEF 存货 规格 数量CPU [email protected] 100 --想要查询的结果(2行料号一起等于规格的时候才显示):单别 单号 料号 规格 数量A1 1001 ABC [email protected] 100 A1 1001 DEF [email protected] 100
------解决方案--------------------
- SQL code
create table tb (单别 char(2),单号 char(4),料号 char(20))insert into tb values ('A1','1001','ABC')insert into tb values ('A1','1001','DEF')create table tb1 (存货 char(20),规格 char(20),数量 int)insert into tb1 values ('CPU',[email protected]',100)select e.单别,e.单号,e.料号,d.规格,d.数量from(select a.单别,a.单号,stuff((select '@'+rtrim(ltrim(料号)) from tb b where b.单别=a.单别 and b.单号=a.单号 for xml path('')),1,1,'') 料号from tb a group by a.单别,a.单号) cinner join tb1 d on rtrim(ltrim(c.料号))=rtrim(ltrim(d.规格))inner join tb e on c.单别=e.单别 and c.单号=e.单号单别 单号 料号 规格 数量---- ---- -------------------- -------------------- -----------A1 1001 ABC [email protected] 100A1 1001 DEF [email protected] 100(2 row(s) affected)