mysql - 无法根据两个条件两次 JOIN 同一张表

Question

我正在使用 MySQL 和 PHP。这是我收到错误的查询。

$query = 
"SELECT days.day, count(myDataTable.appId) as countf, count(myDataTable.appId) as counts
            FROM
              (
               select curdate() as day
               union select curdate() - interval 1 day
               union select curdate() - interval 2 day
               union select curdate() - interval 3 day
               union select curdate() - interval 4 day
               union select curdate() - interval 5 day
               union select curdate() - interval 6 day
               union select curdate() - interval 7 day
               union select curdate() - interval 8 day
               union select curdate() - interval 9 day
               ) days
              left join myDataTable as n1
              on days.day = n1.date AND n1.appId = '$id' AND n1.status = 'ERROR'
              group by days.day
              left join myDataTable as n2
              on days.day = n2.date AND n2.appId = '$id' AND n2.status = 'SUCCESS'
              group by days.day";

错误日志是:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near  'left join myDataTable on days.day = myDataTable.date AND myDataTable.appId ' at line 17

Answer 1

正确缩进 SQL 有助于您发现错误。通过主要关键字(SELECT、FROM、WHERE、HAVING、GROUP BY 和 ORDER BY)缩进将帮助您快速发现它们:

SELECT 
    days.day, 
    count (myDataTable.appId) as countf, 
    count(myDataTable.appId) as counts
FROM
  (
     select curdate() as day
       union select curdate() - interval 1 day
       union select curdate() - interval 2 day
       union select curdate() - interval 3 day
       union select curdate() - interval 4 day
       union select curdate() - interval 5 day
       union select curdate() - interval 6 day
       union select curdate() - interval 7 day
       union select curdate() - interval 8 day
       union select curdate() - interval 9 day
   ) days
  left join myDataTable as n1
       on days.day = n1.date AND n1.appId = '$id' AND n1.status = 'ERROR'
group by
  days.day
  left join myDataTable as n2
       on days.day = n2.date AND n2.appId = '$id' AND n2.status = 'SUCCESS'
group by
  days.day

您可以看到有两个 GROUP BY 不起作用。此外，您在第一个 GROUP BY 子句中有一个 LEFT JOIN ，它也不起作用。删除第一个 GROUP BY 将使您更接近:

SELECT 
    days.day, 
    count (myDataTable.appId) as countf, 
    count(myDataTable.appId) as counts
FROM
  (
     select curdate() as day
       union select curdate() - interval 1 day
       union select curdate() - interval 2 day
       union select curdate() - interval 3 day
       union select curdate() - interval 4 day
       union select curdate() - interval 5 day
       union select curdate() - interval 6 day
       union select curdate() - interval 7 day
       union select curdate() - interval 8 day
       union select curdate() - interval 9 day
   ) days
  left join myDataTable as n1
       on days.day = n1.date AND n1.appId = '$id' AND n1.status = 'ERROR'
  left join myDataTable as n2
       on days.day = n2.date AND n2.appId = '$id' AND n2.status = 'SUCCESS'
group by
  days.day

不，你有一个正确的 FROM 子句。这是数据库查看的第一部分，因此它知道从哪里获取数据以及如何将数据连接在一起。您的表别名在此处设置，然后在查询中的其他任何地方使用。这导致了第二个问题。

您在 SELECT 子句中引用了 myDataTable，但当数据库查看您的 SELECT 时，myDataTable 已不在上下文中。不过，别名 n1 和 n2 是，因此请更改这些别名以引用您的表别名:

SELECT 
    days.day, 
    count (n1.appId) as countf, 
    count(n2.appId) as counts
FROM
  (
     select curdate() as day
       union select curdate() - interval 1 day
       union select curdate() - interval 2 day
       union select curdate() - interval 3 day
       union select curdate() - interval 4 day
       union select curdate() - interval 5 day
       union select curdate() - interval 6 day
       union select curdate() - interval 7 day
       union select curdate() - interval 8 day
       union select curdate() - interval 9 day
   ) days
  left join myDataTable as n1
       on days.day = n1.date AND n1.appId = '$id' AND n1.status = 'ERROR'
  left join myDataTable as n2
       on days.day = n2.date AND n2.appId = '$id' AND n2.status = 'SUCCESS'
group by
  days.day

最后，您可以在 SELECT 中使用 CASE 语句，而不是为每种状态加入两次 myDataTable:

SELECT 
    days.day,
    SUM(CASE WHEN n1.status = 'ERROR' THEN 1 ELSE 0 END) as countf,
    SUM(CASE WHEN n1.status = 'SUCCESS' THEN 1 ELSE 0 END) as counts    
FROM
  (
     select curdate() as day
       union select curdate() - interval 1 day
       union select curdate() - interval 2 day
       union select curdate() - interval 3 day
       union select curdate() - interval 4 day
       union select curdate() - interval 5 day
       union select curdate() - interval 6 day
       union select curdate() - interval 7 day
       union select curdate() - interval 8 day
       union select curdate() - interval 9 day
   ) days
  left join myDataTable as n1
       on days.day = n1.date AND n1.appId = '$id'  
group by
  days.day