sql - SQL中的组合表

Question

我有两张表想合并。

我希望表格会自动添加一行，以便image2在generalname.image1

 select B.Trsdate, count(B.Billno) As Billno, sum(B.pax) As Pax, sum(B.Grossamount) As GrossAmount, sum(B.discountamount) As Discount, sum(B.taxamount) As Tax, sum(B.servicechargeamount) As SCharge, sum(B.nettamount) As NettAmt, sum(B.rounddiff) As RDiff, sum(B.roundamt) As RoundAmt, sum(B.reversalYN) As RevBillNo, SUM(GD.CASH) AS 'P_CASH', SUM(GD.VISA) AS 'P_VISA', SUM(GD.MASTER) AS 'P_MASTER', SUM(GD.AMEX) AS 'P_Amex', SUM(GD.CityLedger) AS 'P_CityLedger', SUM(GD.OtherPayment) As 'P_Other'
from vpos_eod_bills As B
INNER JOIN
    (
        SELECT TrsNo,SUM(CASH) as CASH,SUM(Visa) as Visa, SUM(Master) as Master, SUM(Amex) AS Amex, SUM(CityLedger) as CityLedger, SUM(OtherPayment) as OtherPayment, SUM(Total) as Total FROM 
            (
                select TrsNo, GENERALNAME,
                (case WHEN(Generalname IN ('CASH'))
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'CASH',

                (case WHEN(Generalname IN ('VISA'))
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'Visa',

                (case WHEN(Generalname IN ('MASTER'))
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'Master',

                (case WHEN(Generalname IN ('AMEX'))
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'Amex',

                (case WHEN(Generalname = 'City Ledger' OR Generalname = 'CREDIT A/C' OR Generalname = 'BOSS' OR Generalname = 'ENTERTAINMENT')
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'CityLedger',

                (case WHEN(Generalname not IN ('CASH','Voucher','VISA','MASTER','AMEX','JCB','City Ledger','CREDIT A/C','BOSS','ENTERTAINMENT') and (Generalname not like '%card%') and (Generalname not like '%Coupon%') and (Generalname not like '%GROUPON%') and (Generalname not like '%COURSE%'))
                THEN
                    SUM(AMOUNT)
                ELSE
                    0
                END) as 'OtherPayment',

                SUM(AMOUNT) as Total

                from Vpos_eod_GeneralDetails
                where BillType = 'P'
                group by TrsNo, GeneralName
            ) As A
    Group By A.trsno
    )As GD ON GD.TrsNo = B.TrsNo
where B.PaidStatus = '1' and B.VoidStatus = '0' and (B.trsdate between '20200101' and '20200131')
group by B.trsdate

标签： sqlsql-serversql-server-2008