34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Table](
[id] [int] NULL,
[fid] [int] NULL,
[Name] [nchar](10) NULL,
[Number] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (1, 0, N'A ', 5)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (2, 1, N'B ', 7)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (3, 1, N'C ', 3)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (4, 3, N'D ', 5)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (5, 2, N'E ', 2)
;WITH ctea AS (
SELECT *,[Table].Name AS pname FROM [Table]
UNION ALL
SELECT [Table].id ,
[Table].fid ,
[Table].Name ,
[Table].Number,
ctea.pname
FROM [Table]
JOIN ctea ON ctea.id = [Table].fid
)
SELECT pname,SUM(Number) AS Number FROM ctea GROUP BY pname
USE tempdb
GO
IF OBJECT_ID('Table')IS NOT NULL DROP TABLE [Table]
GO
CREATE TABLE [dbo].[Table](
[id] [int] NULL,
[fid] [int] NULL,
[Name] [nchar](10) NULL,
[Number] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (1, 0, N'A ', 5)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (2, 1, N'B ', 7)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (3, 1, N'C ', 3)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (4, 3, N'D ', 5)
INSERT [dbo].[Table] ([id], [fid], [Name], [Number]) VALUES (5, 2, N'E ', 2)
GO
IF OBJECT_ID('dbo.Fun_GetSubList') IS NOT NULL
DROP FUNCTION dbo.Fun_GetSubList
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-03-15
-- Description: 得到id及所有子记录的集合列表
-- =============================================
CREATE FUNCTION dbo.Fun_GetSubList
(
@id INT
)
RETURNS TABLE
AS
RETURN
(
WITH cte AS(
SELECT id,fid,[Name],[Number] FROM [Table] WHERE id=@id
UNION ALL
SELECT t.id,t.fid,t.[Name],t.[Number]
FROM [TABLE] AS t INNER JOIN cte ON t.fid=cte.id
)
SELECT * FROM cte
)
GO
SELECT t.id
,t.fid
,t.[Name]
,(SELECT SUM(f.Number) FROM dbo.Fun_GetSubList(t.id) AS f) AS [total]
FROM [Table] AS t
/*
id fid Name total
1 0 A 22
2 1 B 9
3 1 C 8
4 3 D 5
5 2 E 2
*/