mysql - 并集和左连接查询

Question

希望有人可以帮助我，我有两张表，其中我需要一张表，可以在其中使用分组依据的小时总和，也可以在其中加入它们，以便我可以对这些表的总和进行比较.

我的想法是这样的查询，但有一个错误:

SELECT hagent.row_date, hagent.starttime_unix, SUM( hagent.i_stafftime ) 
FROM hagent
WHERE hagent.row_date = '2017-08-04'
AND hagent.starttime_unix IN('1700','1730','1800','1830','1900','1930','2000','2030','2100')
GROUP BY hagent.starttime_unix, hagent.row_date
UNION
SELECT hagent.row_date, hagent.starttime_unix, SUM( hagent.i_stafftime ) 
FROM hagent
WHERE hagent.row_date = '2017-08-05'
AND hagent.starttime_unix IN('900','930','1000','1030','1100','1130','1200','1230','1300','1330','1400','1430','1500','1530','1600','1630')
GROUP BY hagent.starttime_unix, hagent.row_date
LEFT JOIN (
    SELECT
    hsplit.row_date,
    hsplit.starttime AS hora ,
    Sum(hsplit.i_stafftime)
    FROM
    hsplit
    WHERE
    hsplit.row_date = '2017-08-05'
    GROUP BY
    hsplit.row_date,
    hsplit.starttime
) s ON (hagent.starttime_unix = s.hora)

注意:我不确定加入是否正确

已编辑

这是错误代码:

[Err] 1064 - 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 'LEFT JOIN (
    SELECT
    hsplit.row_date,
    hsplit.starttime,
    Sum(hsplit.i_staff' at line 12

Answer 1

您有几件事出了问题。另外，我认为您可以在没有 UNION 的情况下实现此目的，因为两个查询中的唯一区别是 WHERE。

SELECT hagent.row_date, hagent.starttime_unix, SUM( hagent.i_stafftime ) 
FROM hagent
LEFT JOIN (
    SELECT
    hsplit.row_date,
    hsplit.starttime AS hora ,
    Sum(hsplit.i_stafftime)
    FROM
    hsplit
    WHERE
    hsplit.row_date = '2017-08-05'
    GROUP BY
    hsplit.row_date,
    hsplit.starttime
) s ON (hagent.starttime_unix = s.hora)
WHERE (
    hagent.row_date = '2017-08-04'
    AND hagent.starttime_unix IN('1700','1730','1800','1830','1900','1930','2000','2030','2100')
    ) OR (
    hagent.row_date = '2017-08-05'
    AND hagent.starttime_unix IN('900','930','1000','1030','1100','1130','1200','1230','1300','1330','1400','1430','1500','1530','1600','1630')
    )
GROUP BY hagent.starttime_unix, hagent.row_date

请告诉我这是否有效。如果您出于某种特定原因需要工会:

SELECT * FROM (
    SELECT hagent.row_date, hagent.starttime_unix, SUM( hagent.i_stafftime ) 
    FROM hagent
    WHERE hagent.row_date = '2017-08-04'
    AND hagent.starttime_unix IN('1700','1730','1800','1830','1900','1930','2000','2030','2100')
    GROUP BY hagent.starttime_unix, hagent.row_date
    UNION
    SELECT hagent.row_date, hagent.starttime_unix, SUM( hagent.i_stafftime ) 
    FROM hagent
    WHERE hagent.row_date = '2017-08-05'
    AND hagent.starttime_unix IN('900','930','1000','1030','1100','1130','1200','1230','1300','1330','1400','1430','1500','1530','1600','1630')
) AS u
LEFT JOIN (
    SELECT
    hsplit.row_date,
    hsplit.starttime AS hora ,
    Sum(hsplit.i_stafftime)
    FROM
    hsplit
    WHERE
    hsplit.row_date = '2017-08-05'
    GROUP BY
    hsplit.row_date,
    hsplit.starttime
) s ON (u.starttime_unix = s.hora)
GROUP u.starttime_unix, u.row_date

抱歉，它的格式不太好...我没有使用带有 SQLMS 的计算机，无法很好地对其进行格式化。