时间戳

首页 > 解决方案 > 传入多个 Id 以返回 case 语句中的列

sql - 传入多个 Id 以返回 case 语句中的列

问题描述

我正在使用一些动态 SQL 根据订阅类型返回一些列。我遇到的问题是多种订阅类型。虽然我可以胜任处理 1 种订阅类型。我不知道如何处理 2 种订阅类型。我总共有 8 种不同的类型。

例如：

DECLARE @subType tinyint = 2

DECLARE @Id varchar(4) = 'U01'
DECLARE @SQLProjectDetails nvarchar(MAX)

SET @SQLProjectDetails = N'SELECT ' +
       STUFF(
            -- General Information 

            CASE WHEN @subType IN (1,2,3,4) THEN N',' + NCHAR(13) + 
                 NCHAR(10) + N'        wf1.Id' ELSE N'' END + 
            CASE WHEN @subType IN (1,2) THEN N',' + NCHAR(13) + 
                 NCHAR(10) + N'        wf1.Name' ELSE N'' END + 
            CASE WHEN @subType IN (5,6,8) THEN N',' + NCHAR(13) + 
                 NCHAR(10) + N'       wf1.OtherNames' ELSE N'' END +
            CASE WHEN @subType IN (1,2) THEN N',' + NCHAR(13) + 
                 NCHAR(10) + N'        wf1.CountryName' ELSE N'' END +
             , 1, 10,N'') + NCHAR(13) + NCHAR(10) +
            N'FROM Table wf1where (wf1.Id= @p)';

EXEC sp_executesql @SQLProjectDetails,  N'@p varchar(4)', @p = @Id;

但是，如果我他们的订阅类型为 2 和 5，该怎么办？

那么 othernames 列也会被返回吗？我遇到的主要问题是 8 种不同的订阅类型。虽然我可以尝试涵盖所有可能的可能性，但我更愿意动态地执行此操作（主要是因为有 40000 多种不同的变化）。

标签： sqlsql-servercasedynamic-sql

解决方案

如果你有一个这样的 ColLookup 表：

SubType, ColumnName
1,wf1.Id
2,wf1.Id
3,wf1.Id
4,wf1.Id
1,wf1.Name
2,wf1.Name
5,wf1.OtherNames
6,wf1.OtherNames
8,wf1.OtherNames
1,wf1.CountryName
2,wf1.CountryName

然后像这样的 SQL 将为您提供您应该提取的列名：

SELECT 
  STRING_AGG(l.ColumnName, ',') as ColList
FROM
  otherTable o
  INNER JOIN
  ColLookup l on o.subtype = l.subtype
WHERE
  o.id = 1234

如果 o.id 1234 的子类型为 1，则此 SQL 将返回

'wf1.Id,1,wf1.Name,wf1.CountryName'

如果 o.id X 的子类型为 3，则此 SQL 将返回

'wf1.Id'

使其成为完整的 SQL 可能类似于：

SELECT 
  CONCAT('SELECT ', STRING_AGG(l.ColumnName, ','), ' FROM blah')  as sq
FROM
  otherTable o
  INNER JOIN
  ColLookup l on o.subtype = l.subtype
WHERE
  o.id = 1234

您可以将其分配给变量并执行它

如果您的 SQLS 是 2017 年之前的版本并且没有 STRING_AGG，请使用类似的方法将多行连接成一个字符串，例如 STUFF/FOR XML PATH。如果需要定义的顺序，请在查找表中添加一个顺序列（STRING_AGG 有 WITHIN GROUP ORDER BY 选项，我相信其他方法也有更改连接字符串顺序的选项）

确实，我会在 C# 中执行此操作。我将运行选择所有内容的主查询，然后运行：

SELECT ColName FROM ColLookup WHERE SubType in (1,5)

我将它放入字典/哈希集（这使用 linq 伪代码，但是你想要）：

//this is a union - anything 1 can see or 5 can see
var dict = dbcontext.Wf1Table.Where(r => r == 1 || r == 5).Distinct().ToDictionary(r => r.ColumnName, r => r.ColumnName)

//this is an intersect - anything common to 1 and 5 only
var dict = dbcontext.Wf1Table.Where(r => r == 1).Intersect(dbcontext.Wf1Table.Where(r => r == 5)).ToDictionary(r => r.ColumnName, r => r.ColumnName)

//this is if you downloaded the columns into a datatable
//SELECT DISTINCT colname FROM t WHERE subtype IN (1,5) --union
//SELECT colname FROM t WHERE subtype IN (1,5) GROUP BY colname HAVING COUNT(*) = 2 --intersection
var dict = new Dictionary(string, string);
foreach(var ro in colnamedatatable)
  dict[ro.ColName] = null;

我会将我的主要查询拉入数据表并删除不需要的列：

for(int c = datatable.Columns.Length -1; c >= 0; c--) //gobackwards
  if(!dict.ContainsKey(datatable.Columns[c].ColumnName))
    datatable.Columns.RemoveAt(c);

如果你想在 SQL 中坚持这样做，这将给出一个相交：

SELECT 
  CONCAT('SELECT ', STRING_AGG(l.ColumnName, ','), ' FROM blah')  as sq
FROM
  otherTable o
  INNER JOIN
  ColLookup l on o.subtype = l.subtype
WHERE
  o.id = @id
GROUP BY
  l.ColumnName
HAVING COUNT(*) = @count_of_subtypes_for_that_id

有些东西需要知道给定 ID 有多少子类型；数据库知道，但很难在这方面为您提供指导，因为您还没有说明数据是如何存储的。如果正确（多个 ID 记录/中间人表），则此查询有效。如果它是“逗号分隔列表”，那么（呃）它需要解析成两行

推荐阅读