在sql server 2008 中完成下面这道题，需要用存储过程完成，哪位大神来帮帮忙啊

发布网友发布时间：2022-05-24 19:28

共2个回答

热心网友时间：2023-10-27 18:08

IF OBJECT_ID('Department') IS NOT NULL
DROP TABLE Department
GO

CREATE TABLE Department
(
PID INT,
ID INT,
DepaName NVARCHAR(50)
)
;

INSERT INTO dbo.Department
        ( PID, ID, DepaName )
VALUES  ( 0,999,N'生产部'),( 999,998,N'冲压车间'),( 999,997,N'组装车间'),( 997,996,N'组装一部'),( 997,995,N'组装二部')

IF OBJECT_ID('rDepartment') IS NOT NULL
DROP PROC rDepartment
GO
CREATE PROC rDepartment
AS
;WITH CTE AS
(
SELECT PID AS 父编号,
ID AS 编号,
DepaName AS 部门名称,
CONVERT(VARCHAR(50),ROW_NUMBER() OVER (ORDER BY ID DESC)) AS 排序号
FROM dbo.Department
WHERE PID = 0
UNION ALL
SELECT
PID AS 父编号,
ID AS 编号,
DepaName AS 部门名称,
CONVERT(VARCHAR(50),b.排序号 + '.' + RTRIM(ROW_NUMBER() OVER (PARTITION BY b.编号 ORDER BY ID DESC)))   AS 排序号
FROM dbo.Department a
INNER JOIN CTE b ON a.PID = b.编号
)
SELECT * FROM CTE
go

热心网友时间：2023-10-27 18:08

男方有小孩但没结婚可追问。。。。。。。