22,300
社区成员




USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([id] smallint,[name] nvarchar(4),[type] smallint)
Insert into tb
Select 1,N'水果',1
Union all Select 2,N'苹果',1
Union all Select 3,N'香蕉',1
Union all Select 4,N'玩具',4
Union all Select 5,N'泰迪熊',4
Union all Select 6,N'变形金刚',4
Union all Select 7,N'流氓兔',4
--------------- 1.
SELECT
b.id AS [唯一编号]
,b.name AS [名称]
,CASE WHEN b.id<>b.type THEN a.name ELSE LTRIM((SELECT COUNT(1) FROM tb AS x WHERE x.type=a.id AND x.id<>a.id)) END AS [数量或类型]
FROM tb AS a
LEFT JOIN tb AS b ON a.id=b.type
WHERE a.id=a.type
ORDER BY [唯一编号]
/*
唯一编号 名称 数量或类型
------ ---- ------------
1 水果 2
2 苹果 水果
3 香蕉 水果
4 玩具 3
5 泰迪熊 玩具
6 变形金刚 玩具
7 流氓兔 玩具
*/
--------------- 2.
SELECT
id AS [唯一编号]
,name AS [名称]
,LTRIM((SELECT COUNT(1) FROM tb AS x WHERE x.type=a.id AND x.id<>a.id)) AS [数量或类型]
FROM tb AS a
WHERE a.id=a.type
UNION ALL
SELECT
b.id
,b.name
,a.name
FROM tb AS a,tb AS b
WHERE a.id=b.type
AND a.id<>b.id
AND a.id=a.type
ORDER BY [唯一编号]
/*
唯一编号 名称 数量或类型
------ ---- ------------
1 水果 2
2 苹果 水果
3 香蕉 水果
4 玩具 3
5 泰迪熊 玩具
6 变形金刚 玩具
7 流氓兔 玩具
*/