主表字段fhqdh,clyf,yfyf
10001,100,50
从表字段fhqdh,hwmc,js,zl,tj
10001,鞋子,10,20,0
10001,衣服,1,2,4
现在既要主表的个别字段 也要从表的字段 ，要显示的结果为：
fhqdh,clyf,yfyf,hwmc,js,zl,tj
10001,100,50,鞋子,10,20,0
10001, 衣服,1,2,4
就是clyf和yfyf 只能显示在其中的一行 不能重复！！！求sql语句
------解决方案--------------------
IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 't1')另外如果这种共能可以在前端实现的话，建议在前端实现
DROP TABLE t1
GO
CREATE TABLE t1(fhqdh VARCHAR(5) , clyf INT , yfyf INT )
GO
INSERT INTO t1( fhqdh, clyf, yfyf)
VALUES( '10001', 100, 50)
GO
IF EXISTS (SELECT NAME FROM sys.objects AS o WHERE o.name = 't2')
DROP TABLE t2
GO
CREATE TABLE t2(fhqdh VARCHAR(5),hwmc NVARCHAR(5), js INT ,zl INT , tj INT)
GO
INSERT INTO t2
SELECT '10001' , N'鞋子' , 10,20,0 union all
SELECT '10001' , N'衣服' , 1,2,4
GO
;WITH a AS (
SELECT
t1.fhqdh,
t1.clyf ,
t1.yfyf ,
t2.hwmc ,
t2.js ,
t2.zl ,
t2.tj , lag(t1.clyf , 1) OVER(ORDER BY t1.fhqdh , t1.clyf , t1.yfyf) AS clyf_1 ,
lag(t1.yfyf , 1) OVER(ORDER BY t1.fhqdh , t1.clyf , t1.yfyf) AS yfyf_1
FROM
t2 INNER JOIN t1 ON t2.fhqdh = t1.fhqdh)
SELECT
a.fhqdh,
case when a.clyf_1 IS NULL OR a.clyf <> a.clyf_1 THEN a.clyf ELSE NULL END AS clyf,
case when a.yfyf_1 IS NULL OR a.yfyf <> a.yfyf_1 THEN a.yfyf ELSE NULL END AS yfyf,
a.hwmc,
a.js,
a.zl,
a.tj
FROM
a AS a
/*执行结果
fhqdh clyf yfyf hwmc js zl tj
----- ----------- ----------- ----- ----------- ----------- -----------
10001 100 50 鞋子 10 20 0
10001 NULL NULL 衣服 1 2 4
(2 行受影响)
*/