使用逗号分隔参数帮助进行 sql 搜索查询 [英] Help with a sql search query using a comma delimitted parameter

问题描述

我正在寻找这样的东西，但无法找出编写查询的最佳方式:

I am looking for something like this but can't figure out the best way to write the query:

SELECT DISTINCT CategoryID FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN ALL (234,245,645)

我目前正在构建一个动态查询，因为 ID 作为逗号分隔的字符串 '234,245,645' 传入，但众所周知，没有 ALL 这样的东西.基本上我想返回列表中包含所有子类别的所有类别.希望这是有道理的.

I am currently building a dynamic query since the IDs are passed in as a comma delimitted string '234,245,645' But as we all know there is no such thing as ALL. Basically I want to return all Categories that are have all of the sub categories in the list. Hope this makes sense.

推荐答案

你可以这样做:

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID)
       = LEN(
          REPLACE(
           REPLACE(
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(
                   REPLACE(
                    REPLACE('234,245,645','0','')
                   , '1', '')
                  , '2', '')
                 , '3', '')
                , '4', '')
               , '5', '')
              , '6', '')
             , '7', '')
            , '8', '')
           , '9', '')
          , ' ', '')) + 1

解决方案 2:另一个可行的方法:

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID) 
       = (SELECT COUNT(DISTINCT SubcategoryID)
            FROM SubCategories 
           WHERE SubcategoryID IN (234,245,645))

这篇关于使用逗号分隔参数帮助进行 sql 搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！