Início / dba / Perguntas / 303641
Accepted
Zoinky
Asked: 2021-12-08 09:03:35 +0800 CST

Consulta ao comparar com vários registros filho

  • 772

Eu não consigo descobrir o que o tsql deve ser para fazer o seguinte.

Gostaria de consultar a tabela Tweets com todos os tweets que possuem categoria 1 e 2 (podem ser mais de 2 categorias passadas)

Portanto, a consulta deve retornar o tweet 1 e 3 para as seguintes inserções

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

A eficiência é importante, pois meus tweets terão mais de 2 milhões de registros e as categorias também podem estar em alguns milhões

sql-server t-sql

2 respostas

  1. Como o tamanho da lista não será o mesmo para todas as execuções, você pode tentar Passar parâmetros de matriz para um procedimento armazenado . Aqui está uma maneira de fazer isso:

    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

    Para que essa abordagem funcione corretamente, presumo que (TweetId, CategoryId) seja uma chave primária composta, embora seu código de exemplo não a mostre.

    • 0
  2. Best Answer

    Maneira mais fácil, se forem sempre exatamente dois IDs:

    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)

    Se as categorias a serem consultadas forem flexíveis (número ou CategoryID), você pode querer algo como:

    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

    Dependendo de onde / como as categorias a serem consultadas são "enviadas", você pode passá-lo como variável de tabela para seu procedimento (desvantagem: você precisa criar um tipo para isso), passá-lo como lista de string simples ('1,2' no seu exemplo) e divida a string antes de inserir em #categories_to_query ou torne-a rápida e suja declarando, por exemplo, 10 parâmetros para seu procedimento para permitir que até 10 categorias diferentes sejam filtradas.

    Em relação ao desempenho: você deve ter um índice em CategoryId, TweetId (nesta ordem!) no dbo.TweetCategories

    • 0

relate perguntas