主页 / dba / 问题 / 303641
Accepted
Zoinky
Asked: 2021-12-08 09:03:35 +0800 CST

比较多个子记录时的查询

  • 772

我似乎无法弄清楚 tsql 应该做什么来执行以下操作。

我想查询具有类别 1 和 2 的所有推文的推文表（可能超过 2 个类别）

所以查询应该为以下插入返回推文 1 和 3

USE tempdb
CREATE TABLE [dbo].[Tweets]([Id] [bigint] NOT NULL)
CREATE TABLE [dbo].[TweetCategories]([TweetId] [bigint] NOT NULL, [CategoryId] [int] NOT NULL)

INSERT INTO Tweets(Id) VALUES(1)
INSERT INTO Tweets(Id) VALUES(2)
INSERT INTO Tweets(Id) VALUES(3)
INSERT INTO Tweets(Id) VALUES(4)

INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(1, 1)
INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(1, 2)

INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(2, 2)
INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(2, 3)

INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(3, 1)
INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(3, 2)
INSERT INTO TweetCategories(TweetId, CategoryId) VALUES(3, 3)


DROP TABLE tweets
DROP TABLE TweetCategories

效率很重要，因为我的推文将有超过 200 万条记录，类别也可能有几百万

sql-server t-sql

2 个回答

  1. 由于每次执行的列表大小都不相同，您可以尝试将数组参数传递给存储过程。这是一种方法：

    CREATE TYPE id_list AS TABLE (
    id int NOT NULL PRIMARY KEY
);
GO

CREATE PROCEDURE [dbo].[selected_categories] (
    @id_list    id_list READONLY
)
AS
BEGIN

    SELECT TC.TweetId
    FROM TweetCategories TC
        INNER JOIN @id_list CL ON TC.CategoryId = CL.id
    GROUP BY TC.TweetId
    HAVING COUNT(1) = (SELECT COUNT(1) FROM @id_list);

END;
GO

DECLARE @category_list id_list;
    
INSERT INTO @category_list (id)
VALUES (1), (2);

EXECUTE [dbo].[selected_categories]
    @id_list = @category_list;
GO

DROP PROCEDURE selected_categories;
DROP TYPE id_list;
GO

    为了使这种方法正常工作，我假设 (TweetId, CategoryId) 是一个复合主键，尽管您的示例代码没有显示它。

    • 0
  2. Best Answer

    最简单的方法，如果它总是恰好是两个 ID：

    SELECT *
  FROM dbo.Tweets AS t
 WHERE EXISTS (SELECT * FROM dbo.TweetCategories AS tc WHERE tc.CategoryId = 1 AND tc.TweetId = t.Id)
   AND EXISTS (SELECT * FROM dbo.TweetCategories AS tc WHERE tc.CategoryId = 2 AND tc.TweetId = t.Id)

    如果要查询的类别是灵活的（数字或 CategoryID），您可能需要以下内容：

    DROP TABLE IF EXISTS #categories_to_query
CREATE TABLE #categories_to_query (CategoryId INT NOT NULL PRIMARY KEY);
INSERT INTO #categories_to_query (CategoryId)
VALUES (1), (2)
DECLARE @ctq INT = (SELECT COUNT(*) FROM #categories_to_query AS ctq)

SELECT t.*
  FROM dbo.Tweets AS t
 INNER JOIN (SELECT tc.TweetId
               FROM dbo.TweetCategories AS tc
              INNER JOIN #categories_to_query AS ctq
                 ON ctq.CategoryId = tc.CategoryId
              GROUP BY tc.TweetId
              HAVING COUNT(*) = @ctq
            ) AS cat
   ON cat.TweetId = t.Id

    根据要查询的类别“提交”的位置/方式，您可以将其作为表变量传递给您的过程（缺点：您必须为此创建一个类型），将其作为简单的字符串列表传递（'1,2'在您的示例中）并在插入 #categories_to_query 之前拆分字符串，或者通过在您的过程中声明例如 10 个参数以允许过滤多达 10 个不同的类别来使其快速而肮脏。

    关于性能：您应该在 dbo.TweetCategories 上有一个 CategoryId、TweetId 的索引（按此顺序！）

    • 0

相关问题