如何在mysql中使用join和union进行子查询

debugcn 发表于 Dev

伊琳娜

我试图在两个子查询中使用连接和联合。这是我的查询：

SELECT Service,SP.Second_Period, SP.TRX_SP,FP.First_Period,FP.TRX_FP
FROM (((SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
RIGHT JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service))
UNION ALL
((SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
LEFT OUTER JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service) ))  as tbl2
WHERE SP.Service=FP.Service
GROUP BY Service
Order BY Service

通过这样做，它会返回给我这个错误：

1064 - 你的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册以了解要使用的正确语法

靠近'LEFT OUTER JOIN (SELECT Service, SUM(Processed) as First_Period, COUNT(Processe' at line 16)

我究竟做错了什么？由于我无法在 mysql 中使用完全外部联接，因此我正在使用左右联接与联合。

更新

这是将 Service 替换为 DAYNAME(Dataime) 的纽约代码

SELECT *
FROM (
(
SELECT * FROM
(SELECT DAYNAME(Dataime), SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
RIGHT JOIN
(SELECT DAYNAME(Dataime), SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING (DAYNAME(Dataime)))
UNION ALL
(
SELECT * FROM
(SELECT DAYNAME(Dataime), SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
LEFT OUTER JOIN
(SELECT DAYNAME(Dataime), SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING (DAYNAME(Dataime)) ))  as tbl2
GROUP BY DAYNAME(Dataime)
Order BY DAYNAME(Dataime)

这是错误：

1064 - 你的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册以了解要使用的正确语法

附近 '(Dataime))) UNION ALL (

达山·梅塔

您需要进行一些更改才能使此查询正常工作：

将内部SELECT查询包装到另一个SELECT *查询中以使其JOIN工作，例如() SP LEFT JOIN () SP不是有效的语法。相反，使用SELECT * FROM (..) SP JOIN (..) FP
删除SP.*从外部SELECT的SP和FP只对内部查询可见，使用SELECT *替代
再次WHERE SP.Service=FP.Service从外部WHERE子句中删除，SP并且FP将不可见。

以下查询应该有效：

SELECT *
FROM (
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
RIGHT JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service))
UNION ALL
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
LEFT OUTER JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service) ))  as tbl2
GROUP BY Service
Order BY Service

更新

您不能在USING子句中使用函数，因此您需要为该列设置别名并在中使用别名USING，例如：

SELECT *
FROM (
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
RIGHT JOIN
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING(`day`))
UNION ALL
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
LEFT OUTER JOIN
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING (`day`) ))  as tbl2
GROUP BY `day`
Order BY `day`

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。