许多在子查询上留下的联接，需要一些方法来提高性能。

Question

下面是我的查询的一个例子。我最多有10个这样的联接/子查询--基本上都是相同的格式，但是有不同的联接和where子句。

SELECT DISTINCT mytable.label, tableA.counter, tableB.counter
FROM mytable
LEFT JOIN 
    (SELECT COUNT(id) as counter, label
        FROM mytable
        ...joins...
        ...where...
        GROUP BY label) tableA 
    ON tableA.label=mytable.label
LEFT JOIN
    (SELECT COUNT(id) as counter, label
        FROM mytable
        ...joins...
        ...where...
        GROUP BY label) tableB 
    ON tableB.label=mytable.label
...

它大约需要2-4秒，这是一个高流量页面，所以这种速度是不够好的。有人能在这里推荐一种提高性能的方法吗？

Answer 1

这里不需要GROUP，因为您只返回1个值。尝试这样的子查询方法：

SELECT DISTINCT T.label,
  (SELECT COUNT(id) as counter FROM tableA A WHERE A.blah = T.blah) as AValue,
  (SELECT COUNT(id) as counter FROM tableB B WHERE B.blah = T.blah) as BValue
FROM mytable T

Answer 2

除了Jon的解决方案之外，我还会使用友联市和枢轴表共享另一个解决方案。

SELECT [A], [B]
FROM (
    SELECT 'A' AS TableName, COUNT(id) as counter
    FROM tableA 
    UNION ALL
    SELECT 'B' AS TableName, COUNT(id) as counter
    FROM tableB
) AS DT
PIVOT(SUM(counter) FOR TableName IN([A], [B])) AS PVT