时间戳

首页 > 解决方案 > 如何使用 UNION ALL 语句修复此 SQL？

mysql - 如何使用 UNION ALL 语句修复此 SQL？

问题描述

我有一个每日数据列表，我想根据名称求和，但按日期分组和显示。我想结合 Bookings 表和 POS。我设法获得了每个单独的预订和 POS，但是当试图结合时..它失败了

选择语句都很好，但是当我添加 UNION ALL 语句时失败了

Select Name, CONVERT(char(10), DatePayment,120), SUM(Amount) Amount 
From (
select PT.Name, CONVERT(char(10), DatePayment, 120) Date, SUM(Amount) Amount    
    From Payments P 
        Inner Join PaymentTypes PT On P.PaymentType = PT.ID 
        Inner Join BookingPayments BP On P.ID = BP.PaymentID 
        Inner Join Bookings B On B.ID = BP.BookingID 
        Where   DatePayment >= '2018-12-01 00:00:00' And 
                DatePayment <= '2018-12-31 23:59:59.997' And 
                BookingStatus IN (0,2,3,4,6) 
        Group By PT.Name, CONVERT(char(10), DatePayment, 120)

UNION ALL
Select  PT.Name, CONVERT(char(10), DatePayment, 120) Date, SUM(P.Amount) Amount 
From Payments P 
        Inner Join PaymentTypes PT On P.PaymentType = PT.ID 
        Inner Join POSSales POS On P.ID = POS.PaymentID 
        Where   DatePayment >= '2018-12-01 00:00:00' And 
                DatePayment <= '2018-12-31 23:59:59.997' 
        Group By PT.Name, CONVERT(char(10), DatePayment, 120))

a Group By Name, CONVERT(char(10), DatePayment, 120)
order by CONVERT(char(10), DatePayment, 120);

我不断收到以下错误

Msg 207, Level 16, State 1, Line 22
Invalid column name 'DatePayment'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DatePayment'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'DatePayment'.

标签： mysqlsql

解决方案

您需要使用 Date 而不是 DatePayment，而是使用别名，您需要选择别名

   Select Name, CONVERT(char(10), `Date`,120), SUM(Amount) Amount 
        From (
        select PT.Name, CONVERT(char(10), DatePayment, 120) Date, SUM(Amount) Amount    
            From Payments P 
                Inner Join PaymentTypes PT On P.PaymentType = PT.ID 
                Inner Join BookingPayments BP On P.ID = BP.PaymentID 
                Inner Join Bookings B On B.ID = BP.BookingID 
                Where   DatePayment >= '2018-12-01 00:00:00' And 
                        DatePayment <= '2018-12-31 23:59:59.997' And 
                        BookingStatus IN (0,2,3,4,6) 
                Group By PT.Name, CONVERT(char(10), DatePayment, 120)

        UNION ALL
        Select  PT.Name, CONVERT(char(10), DatePayment, 120) Date, SUM(P.Amount) Amount 
        From Payments P 
                Inner Join PaymentTypes PT On P.PaymentType = PT.ID 
                Inner Join POSSales POS On P.ID = POS.PaymentID 
                Where   DatePayment >= '2018-12-01 00:00:00' And 
                        DatePayment <= '2018-12-31 23:59:59.997' 
                Group By PT.Name, CONVERT(char(10), DatePayment, 120)
)a Group By Name, CONVERT(char(10), `Date`, 120)
   order by CONVERT(char(10), `Date`, 120);

推荐阅读