34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE t1
(
id INT,
style VARCHAR(10),
code INT,
name VARCHAR(50),
pid INT
)
INSERT INTO t1
SELECT 1,'大类',1,'水泥',NULL UNION ALL
SELECT 2,'小类',1,'速凝水泥',1 UNION ALL
SELECT 3,'小类',2,'慢凝水泥',1 UNION ALL
SELECT 4,'品牌',1,'feifei',2 UNION ALL
SELECT 5,'品牌',2,'nini',2
SELECT * FROM t1
SELECT a.code AS [大类code],a.NAME AS [大类name],b.code AS [小类code],b.NAME AS [小类name],c.code AS [品牌code],
c.NAME AS [品牌name]
FROM t1 AS a WITH(NOLOCK) INNER JOIN t1 AS b ON b.pid=a.code AND a.pid IS NULL AND b.code=2 INNER JOIN
t1 AS c WITH(NOLOCK) ON c.pid=b.code AND c.style='品牌'
-------------------------------
大类code 大类name 小类code 小类name 品牌code 品牌name
1 水泥 2 慢凝水泥 1 feifei
1 水泥 2 慢凝水泥 2 nini