mysql - 我如何在单个查询中组合多个联合

Question

查询返回工作日站点和时间的平均账单。 以下查询使用多个联合我想在单个查询中组合所有 我该怎么做。

select 
        a.Month,
        'Weekday' as Type,
        'Lunch' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.dayStatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) <= '15:00'
                and date_format(DTTM, '%a') not in ('Sat' , 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId 
    union (select 
        a.Month,
        'Weekday' as Type,
        'Evening' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.daystatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) > '15:00'
                and time(DTTM) < '19:00'
                and date_format(DTTM, '%a') not in ('Sat' , 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId) union (select 
        a.Month,
        'Weekday' as Type,
        'Dinner' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.dayStatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) >= '19:00'
                and date_format(DTTM, '%a') not in ('Fri' , 'Sat', 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId) union (select 
        a.Month,
        'Weekend' as Type,
        'Lunch' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.daystatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) <= '15:00'
                and date_format(DTTM, '%a') in ('Sat' , 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId) union (select 
        a.Month,
        'Weekend' as Type,
        'Evening' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.dayStatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) > '15:00'
                and time(DTTM) < '19:00'
                and date_format(DTTM, '%a') in ('Sat' , 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId) union (select 
        a.Month,
        'Weekend' as Type,
        'Dinner' as 'Sale',
        sum(case
            when a.siteId = '102' then a.AvgBill
            else '--'
        end) as '102',
        sum(case
            when a.siteId = '103' then a.AvgBill
            else '--'
        end) as '103',
        sum(case
            when a.siteId = '104' then a.AvgBill
            else '--'
        end) as '104',
        sum(case
            when a.siteId = '105' then a.AvgBill
            else '--'
        end) as '105',
        sum(case
            when a.siteId = '106' then a.AvgBill
            else '--'
        end) as '106'
    from
        (select 
            date_format(o.dayStatus, '%M') as Month,
                c.companyId as CompanyId,
                o.companyId as siteId,
                o.bill,
                round((sum(o.bill) / count(orderId)), 2) as AvgBill,
                date_format(DTTM, '%a') as Day
        from
            orders o, mdm_sites s, mdm_company c
        where
            s.siteId = o.companyId
                and o.isBilled = 1
                and billMode = 0
                and s.companyid = c.CompanyId
                and time(DTTM) >= '19:00'
                and date_format(DTTM, '%a') in ('Fri' , 'Sat', 'Sun')
                and year(o.dayStatus) = (2014)
                and case
                when 0 in (1) then c.companyId in (c.companyid)
                else c.companyId in (1)
            end
                and case
                when 0 in (0) then o.companyId in (o.companyId)
                else o.companyId in (0)
            end
                and case
                when 0 in (12) then month(o.dayStatus) in (month(o.dayStatus))
                else month(o.dayStatus) in (12)
            end
        group by c.companyId , o.companyId) as a
    group by a.CompanyId);


the output of above query

month       Type    Sale    102     103    104     105      106   
------------------------------------------------------------------
December    Weekday Lunch   566.63  530.19  644.9   556.9   467.65
December    Weekday Evening 433.89  404.69  457.18  547.22  396.6
December    Weekday Dinner  427.68  354.54  371.42  386.21  366.03
December    Weekend Lunch   410.57  381.36  383.86  365.94  394.14
December    Weekend Evening 418.45  305.31  429.12  464.81  301.9
December    Weekend Dinner  415.91  374.95  375.28  376.93  360.45

我如何在单个查询中执行此操作，谢谢

Answer 1

枢轴将是理想的解决方案，并节省执行计划。您的数据透视组将是月份、类型、销售额。您的计算组将为 102,103,104,105,106。

数据透视也将在单个查询中完成。

枢轴的一般指南: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

一个与您要完成的非常相似的示例: http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx