查询以选择下一列，如果当前列不返回任何行

  • Z时代
  • 2024-01-10
  • 分类：问答

问题相当广泛，请与我联系。我有一个具有以下结构的单映射表：查询以选择下一列，如果当前列不返回任何行

这种特殊的表中生成一个层次结构的方法中使用。表格中列的顺序和位置表示层次结构的顺序（组织，类别，大陆，国家等）。此层次结构中的每个实体都有一个关联表，其中有关联的Id和Name。例如，有一个Country表，CountryId和CountryName。请注意，由于MappingTable的值都是可以为空的，所以有没有外键约束。

我想生成一个过程，将执行以下操作：根据提供的条件

，层次结构中检索下一个实体的值。例如，如果给出OrganizationId和CategoryId，则需要检索满足所述条件的值ContinentId。

而且，如果ContinentId的值是NULL，则需要检索CountryId的值。在这里，给定条件OrganizationId = 1 and CategoryId = 1该过程应该返回RegionId的列表。

除了检索RegionId之外，还应该从Region表中检索相应的RegionName。

到目前为止，该过程看起来像这样 - 只是在这里解释一些事情。

ALTER PROCEDURE [dbo].[GetHierarchy] 
(
@MappingTableName VARCHAR(30), 
@Position VARCHAR(5), 
-- Given in the form of Key-value pairs 'OrganizationId:1,CategoryId:1' 
@InputData VARCHAR(MAX), 
@Separator CHAR(1), 
@KeyValueSeperator CHAR(1) 
) 
AS 
BEGIN 
DECLARE @Sql NVARCHAR(MAX) 
DECLARE @Result NVARCHAR(MAX) 
DECLARE @Sql1 NVARCHAR(MAX); 
DECLARE @TableName NVARCHAR(30) 
DECLARE @Exists bit 
SELECT @TableName = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @MappingTableName AND ORDINAL_POSITION = @position 
SET @TableName = SUBSTRING(@TableName,0,LEN(@TableName) - 1) 
-- Returns a dynamic query like "SELECT ContinentId from Continent WHERE OrganizationId = 1 and CategoryId = 1". 
SELECT @Sql = [dbo].[KeyValuePairs](@TableName, @InputData, @Separator, @KeyValueSeperator) 
SET @Sql1 = N'SET @Exists = CASE WHEN EXISTS(' + @Sql + N' AND ' + @TableName + N'Id IS NOT NULL) THEN 1 ELSE 0 END' 
PRINT @Sql 
EXEC sp_executesql @Sql1, 
        N'@Exists bit OUTPUT', 
        @Exists = @Exists OUTPUT 
IF(@Exists = 1) 
BEGIN 
    SET @Sql1 = 'SELECT ' + @TableName + 'Id, ' + @TableName + 'Name FROM '+ @TableName+' WHERE ' + @TableName +'Id IN (' + @Sql + ')'; 
    PRINT @Sql1 
    EXECUTE sp_executesql @SQL1 
END 
ELSE 
BEGIN 
    --PRINT 'NOT EXISTS' 
    DECLARE @nextPosition INT 
    SELECT @nextPosition = CAST(@position AS INT) 
    SET @nextPosition = @nextPosition + 1 
    SET @Position = CONVERT(VARCHAR(5), CAST(@position AS INT)) 
    EXEC [dbo].[GetHierarchy] @MappingTableName, @Position, @InputData, @Separator, @KeyValueSeperator 
END 
END

这个程序的逻辑是这样的，我在一个特定的位置（基于此，它是Continent的条件）获得列的名称和基于生成动态查询，以获得下一列的值在输入条件的条件下（我正在使用一个单独的函数为我做这件事）。 一旦检索，我运行查询来检查它是否返回任何行。如果查询返回行，则我从Continent表中检索相应的ContinentName。如果没有行返回，我递归调用该过程，下一个位置作为输入。

在事物的商业方面，它似乎是一个两步过程。但是，作为一个程序，它是相当复杂，广泛的 - 更不用说递归了。有没有更简单的方法来做到这一点？我对CTE不熟悉 - CTE能否实现相同的逻辑？

这很类似于在这里问：Working with a dynamic hierarchy SQL Server

回答：

可能是小漫长的方法。试试这个

DECLARE @T TABLE 
(
    SeqNo INT IDENTITY(1,1), 
    CatId INT, 
    Country INT, 
    StateId INT, 
    DistId INT 
) 
DECLARE @State TABLE 
(
    StateId INT, 
    StateNm VARCHAR(20) 
) 
DECLARE @Country TABLE 
(
    CountryId INT, 
    CountryNm VARCHAR(20) 
) 
INSERT INTO @State 
VALUES(3,'FL') 
INSERT INTO @Country 
VALUES(2,'USA') 
INSERT INTO @T(CatId) 
VALUES(1) 
INSERT INTO @T(CatId,Country) 
VALUES(1,2) 
INSERT INTO @T(CatId,StateId) 
VALUES(1,3) 
;WITH CTE 
AS 
(
    SELECT 
     *, 
     IdVal = COALESCE(Country,StateId,DistId), 
     IdCol = COALESCE('Country '+CAST(Country AS VARCHAR(50)),'StateId '+CAST(StateId AS VARCHAR(50)),'DistId '+CAST(DistId AS VARCHAR(50))) 
     FROM @T 
      WHERE CatId = 1 
),C2 
AS 
(
    SELECT 
     SeqNo, 
     CatId, 
     Country, 
     StateId, 
     DistId, 
     IdVal, 
     IdCol = LTRIM(RTRIM(SUBSTRING(IdCol,1,CHARINDEX(' ',IdCol)))) 
     FROM CTE 
) 
SELECT 
    C2.SeqNo, 
    C2.CatId, 
    S.StateNm, 
    C.CountryNm 
    FROM C2 
     LEFT JOIN @State S 
      ON C2.IdCol ='StateId' 
       AND C2.IdVal = S.StateId 
     LEFT JOIN @Country C 
      ON C2.IdCol ='Country ' 
       AND C2.IdVal = c.CountryId

以上是 查询以选择下一列，如果当前列不返回任何行 的全部内容， 来源链接： utcz.com/qa/260844.html

回到顶部