MySQL复杂查询问题

Question

我的 MySQL 查询有问题： 我有两张桌子： - 集群类别 - 域

现在我有一个 SQL 查询，其中列出了具有类别名称的特定类别的所有域 - 这是我的查询：

SELECT domains.*, clustercategories.clustercategoryname
FROM (domains, clustercategories)
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3)) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

现在的问题是，我还有第三个表“子页面”，我想在其中计算特定类别的所有条目状态 = '1' 的域，我不知道如何修改我的查询以使其工作 - 我已经尝试过此查询，但我没有得到任何结果：

SELECT domains.*, clustercategories.clustercategoryname
FROM (domains, clustercategories)
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3) AND (SELECT COUNT(*) AS total FROM subpages WHERE subpages.domainid = domains.id AND subpages.status = '1')) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10) AND (SELECT COUNT(*) AS total FROM subpages WHERE subpages.domainid = domains.id AND subpages.status = '1'))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

有人有任何想法吗？

Answer 1

我认为您需要将子查询放入投影中，如下所示：

SELECT domains.*, clustercategories.clustercategoryname, 
(SELECT COUNT(*) FROM subpages WHERE subpages.domainid = domains.id AND subpages.status = '1') AS total
FROM domains, clustercategories
WHERE ((clustercategories.id = 3 AND (domains.cluster1id = 3 OR domains.cluster2id = 3)) 
OR (clustercategories.id = 10 AND (domains.cluster1id = 10 OR domains.cluster2id = 10))) 
AND domains.status = '1' 
GROUP BY domains.name 
ORDER BY domains.name

Answer 2

在我看来，您的第一个查询可以像这样重写

SELECT  d.*
        , cc.clustercategoryname
FROM    domains d
        INNER JOIN clustercategories cc
          ON cc.id = d.cluster1id 
          OR cc.id = d.cluster2id
WHERE   cc.id IN (3, 10)
        AND d.status = '1'                                           
GROUP BY 
        d.name
ORDER BY 
        d.name

，因此添加子页面的数量可以这样完成

  SELECT  d.*
          , cc.clustercategoryname
          , sp.total
  FROM    domains d
          INNER JOIN clustercategories cc 
            ON cc.id = d.cluster1id 
               OR cc.id = d.cluster2id
          LEFT OUTER JOIN (
            SELECT  COUNT(*) AS total
                    , domainid
            FROM    subpages
            WHERE   subpages.status = '1'
            GROUP BY
                    domainid
          ) sp ON sp.domainid = d.domainid          
  WHERE   cc.id IN (3, 10)
          AND d.status = '1'                                           
  GROUP BY 
          d.name
  ORDER BY 
          d.name