MySQL 使用 CAST 和 SUM 与 JOIN

Question

我有以下 3 个 MySQL 查询:

-1-

 SELECT CAST(SUM(revenue) AS CHAR) revenue FROM PA
 WHERE site_id = 2 AND data_date BETWEEN '2016-01-01' AND '2016-01-01';

-2-

SELECT CAST(SUM(revenue) AS CHAR) revenue FROM PB
 WHERE site_id = 2 AND data_date BETWEEN '2016-01-01' AND '2016-01-01';

-3-

SELECT CAST(SUM(revenue) AS CHAR) revenue FROM PC
 WHERE site_id = 2 AND data_date BETWEEN '2016-01-01' AND '2016-01-01';

每个人只会给我查询表中的收入。问题是我想获得所有 3 张 table 的总收入，知道如何加入它们吗？我最后一次尝试是:

SELECT
    PA.CAST (SUM(PA.revenue) AS CHAR) revenue,
    PB.CAST (SUM(PB.revenue) AS CHAR) revenue,
    PC.CAST (SUM(PC.revenue) AS CHAR) revenue
FROM
    PA
INNER JOIN PB ON PA.site_id = PB.site_id
INNER JOIN PC ON PA.site_id = PC.site_id

但是我的语法有错误...知道该怎么做吗？

错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS CHAR) revenue, PB.CAST(SUM(PC' at line 1

Answer 1

不要使用表别名限定函数。

这是错误的...

  PA.CAST(SUM(revenue))
  ^^^

限定对列的引用...

  CAST(SUM(PA.revenue))
           ^^^

这是语法错误。

但我认为您不想加入这些表...有可能生成这些表的叉积(半笛卡尔积)。

如果其中一个表没有返回任何行，则查询将返回零行。或者，如果从同一个 site_id 的一个表中返回多行，则会产生“重复”行，并且 SUM 将被夸大。

<小时/>

不知道你想要实现什么......

给定工作查询的示例，我们可以提出类似的建议，尽管可能有更有效的方法来实现等效结果。

  SELECT SUM(v.revenue) AS revenue
    FROM ( 
           ( 
             SELECT SUM(PA.revenue) AS revenue
               FROM PA
              WHERE PA.site_id = 2 
                AND PA.data_date BETWEEN '2016-01-01' AND '2016-01-01'
           )
           UNION ALL
           (
             SELECT SUM(PB.revenue) AS revenue
               FROM PB
              WHERE PB.site_id = 2 
                AND PB.data_date BETWEEN '2016-01-01' AND '2016-01-01'
           )
           UNION ALL
           (
             SELECT SUM(PC.revenue) AS revenue
               FROM PC
              WHERE PC.site_id = 2 
                AND PC.data_date BETWEEN '2016-01-01' AND '2016-01-01'
           )
         ) v