我必须编写脚本,我想组合成一个:
SELECT [CategoryId] FROM [dbo].[Unit] where Id in (716,724)
和
DECLARE @Id INT = 15
;WITH cteGetRootID
As
(
SELECT
Id, [Name], ParentId, 1 AS CodePosition
FROM
Category WHERE Id = @Id
UNION All
SELECT
ic.Id, ic.[Name], ic.ParentId, CodePosition + 1
FROM Category ic
INNER JOIN cteGetRootID cte ON ic.Id = cte.ParentId
)
SELECT top 1 Id, [Name] FROM cteGetRootID
ORDER BY CodePosition desc
我想在第一部分为所有结果做低代码,如果所有结果都有相同的根,则返回根分类 喜欢声明@Id INT = 从 [dbo] 中选择 [类别 ID]。[单位] 其中 Id 在 (716,724(
希望它能有所作为
您可以在第一次选择并集时使用dbo.Unit
和dbo.Category
之间的join
:
;WITH cteGetRootID
As
(
SELECT
c.Id, c.[Name], c.ParentId, 1 AS CodePosition
FROM
Category c
INNER JOIN [dbo].[Unit] u on u.CategoryID = c.Id
WHERE u.Id in (716,724)
UNION All
SELECT
ic.Id, ic.[Name], ic.ParentId, CodePosition + 1
FROM Category ic
INNER JOIN cteGetRootID cte ON ic.Id = cte.ParentId
)
SELECT top 1 Id, [Name] FROM cteGetRootID
ORDER BY CodePosition desc