sql - 如何在 SQL for Categories - Subcategories 中将两个查询组合为单个结果集？

Question

我有以下功能:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE 
AS
RETURN 
(
SELECT ISBN FROM (
SELECT ISBN, 
ROW_NUMBER() OVER(AddedDate DESC) AS RowNum
FROM (
        SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblSubCategories
        WHERE SubCategoryID = @SubCategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern 
) AS Info
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)

感谢 StackOverflow 上其他人的帮助，将列出所有具有 Given SubCategory 的项目 - 但是我希望能够包括以下内容:

    SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
    FROM tblCategories
    WHERE CategoryID = @CategoryID) Cats
    JOIN tblStock Stock
    ON Stock.CategoryCode LIKE Cats.Pattern

以便将类别作为集合获取，然后子类别是此的子集，例如我有一个类别的 EG，其中包含两个子类别 EG-EG 和 EG-IE，它们本身就是类别代码的列表，例如:

EG-EG
- 等等
- 电汇
- 三氯乙烯
EG-IE
- EIEG
- 爱奇艺

如何获取它以便它执行类别，然后从此列表中执行子类别，作为其中的一部分，我需要“NOT”行为，因为将有一个通用类别，它将拾取所有剩余的子类别，而不是具体说明，但会被类别查询选取。

我只是找不到合适的组合 - SubCategories 和 Categories 单独工作，但我希望它们成为彼此的 SuperSets 和 SubSets。

这是 ListStockByCategoryFunction:

CREATE FUNCTION [dbo].[ListStockByCategory]
(   
    @CategoryID varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE AS
RETURN
(
SELECT ISBN FROM (SELECT ISBN, 
ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
FROM (
        SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblCategory
        WHERE CategoryID = @CategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern
) AS Info
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)

我有一个有效的解决方案，但是性能是 Not Acceptable ，任何人都可以帮助解决这个问题，因为我已经研究了一段时间并且似乎无法找到优化它的方法 - 如果这样，子类别是类别的子集有帮助，请看下面的例子:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID     varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE 
AS
RETURN (
SELECT ISBN FROM (
SELECT ISBN,
ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
FROM BooksInStock WHERE ISBN IN
(SELECT ISBN FROM(SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblCategories
        WHERE CategoryID = @CategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern
WHERE 
ISBN IN
(SELECT ISBN FROM(SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblSubCategories
        WHERE SubCategoryID = @SubCategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern))
) AS Info WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1)

Answer 1

好吧，如果没有表定义、键、索引查询计划或数据示例，就不可能说出来，但我认为这会有所帮助:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID     varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE AS
RETURN 
(
    SELECT ISBN FROM 
    (
        SELECT  ISBN,  ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
        FROM BooksInStock 
        WHERE EXISTS
        (        
            SELECT *

            FROM tblStock AS stk
            JOIN tblStock AS stk2         ON stk.ISBN = stk2.ISBN
            JOIN tblCategories AS cat     ON cat.CategoryID = @CategoryId
            JOIN tblSubCategories AS sub  ON cat.CategoryID = sub.CategoryID

            WHERE cat.CategoryID = @CategoryId
              AND sub.CategoryID = @CategoryId
              AND bis.ISBN = stk.ISBN
              AND  stk.CategoryCode LIKE RTRIM(LTRIM(cat.CategoryCode))+'%'
              AND stk2.CategoryCode LIKE RTRIM(LTRIM(sub.CategoryCode))+'%'
        )
    ) AS Info 
    WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)