是否可以在 t-sql 中对组进行子查询？

Question

我的目标是编写一个查询，该查询将返回带有 3 个标志字段的所有类别，如下所示：

ID |恩 |茹 | Fr

语言标志必须根据是否有与该类别关联的任何课程记录来打开或关闭。

我当前的声明是这样的：

SELECT c.ID,
    (CASE WHEN c.ID IN (SELECT c.ID FROM Lessons AS l WHERE l.Category_Id = c.ID AND l.Language_Id = 1) THEN 1 ELSE 0 END) AS En,
    (CASE WHEN c.ID IN (SELECT c.ID FROM Lessons AS l WHERE l.Category_Id = c.ID AND l.Language_Id = 2) THEN 1 ELSE 0 END) AS Ru,
    (CASE WHEN c.ID IN (SELECT c.ID FROM Lessons AS l WHERE l.Category_Id = c.ID AND l.Language_Id = 3) THEN 1 ELSE 0 END) AS Fr
FROM LessonCategories AS c

问题是这个查询非常慢，因为 Lessons 表有超过 60,000 条记录，而且我运行了 3 次。

我正在寻找一种方法来提高此查询的效率。 我曾想过在类别和课程之间的连接上使用分组，但我不知道具体如何以及是否可能。

更快速查询的伪代码是：

SELECT c.[ID], 
    COUNT(l.Language_Id = 1) > 0 AS En
    COUNT(l.Language_Id = 2) > 0 AS Ru
    COUNT(l.Language_Id = 3) > 0 AS Fr
FROM CategoryTreeView AS c
INNER JOIN Lessons AS l ON l.Category_Id = c.ID
GROUP BY c.[ID]

It it possible toexpress this using valid t-sql? 或者有什么更好的方法来处理这种查询？

PS 如果有帮助的话，我不在乎得到一个按位标志字段而不是 3 个语言字段。

谢谢。

Answer 1

好吧，您只需将两个查询结合起来即可；）

SELECT c.ID, 
       SUM(case when l.Language_Id = 1 then 1 else 0) AS En
       SUM(case when l.Language_Id = 2 then 1 else 0) AS Ru
       SUM(case when l.Language_Id = 3 then 1 else 0) AS Fr
FROM CategoryTreeView AS c
INNER JOIN Lessons AS l ON l.Category_Id = c.ID
GROUP BY c.ID

Answer 2

您可以在第二个示例中使用计数：

COUNT(case when l.Language_Id = 1 then 1 else null end) as EnCount
COUNT(case when l.Language_Id = 2 then 1 else null end) as RuCount

但实际上，您想在第一个示例中使用 exists：

case when exists(SELECT 0 FROM Lessons AS l WHERE l.Category_Id = c.ID AND l.Language_Id = 1) then 1 else 0 end as En

通过正确的索引，三个 exists 会快得多。

Answer 3

顺便说一句，试试这个

SELECT c.ID,
    (CASE WHEN l.Language_Id = 1 THEN 1 ELSE 0 END) AS En,
    (CASE WHEN l.Language_Id = 2 THEN 1 ELSE 0 END) AS Ru,
    (CASE WHEN l.Language_Id = 3 THEN 1 ELSE 0 END) AS Fr
FROM LessonCategories AS c
INNER JOIN Lessons l ON c.ID = l.CategoryId

，
目前，您正在通过应用 CASE 语句在 SELECT 子句中进行过滤。通过将过滤语句移至在 SELECT 之前执行的 WHERE 子句，您可以显着提高任何查询的性能。

Answer 4

一种完全不同的方法尝试枢轴。

DECLARE @ctw TABLE(id int, languageid int) 

insert @ctw values(1,1)
insert @ctw values(1,1)
insert @ctw values(1,2)
insert @ctw values(1,3)
insert @ctw values(2,1)
insert @ctw values(2,3)

SELECT * FROM 
(
SELECT id, CASE languageid WHEN 1 THEN 'en' WHEN 2 THEN 'ru' WHEN 3 THEN 'fr' END language
FROM @ctw) a
PIVOT
(count(language) 
FOR language
in([en],[ru],[fr]) 
)AS p ORDER BY id

测试在这里 https://data.stackexchange.com/stackoverflow/q/107960/