您可以帮助修改查询或其他查询以获得预期结果吗 [英] Can you help to modify the query or other query for getting the expected result

问题描述

我尝试了很多方法，但没有得到预期的结果.如何获得预期的结果.

I tried many way but didn't get expected result. How can I get expected result.

您可以从此处下载 dbtable结构:-

SELECT 
    IF(o.source_id = 1, 'online',0)  TYPE,
        MONTH(date_created) AS monthvalue
        ,SUM(total_price) AS totalSale
FROM orders o
WHERE   o.source_id = 1  AND  date_created BETWEEN(CURDATE() - INTERVAL 1 MONTH)
    AND CURDATE()
GROUP BY monthvalue

UNION ALL

SELECT 
    IF(o.source_id = 2, 'visit',0)  TYPE,
        MONTH(date_created) AS monthvalue
        ,SUM(total_price) AS totalSale
FROM orders o
WHERE   o.source_id =2 AND  date_created BETWEEN(CURDATE() - INTERVAL 1 MONTH)
    AND CURDATE()
GROUP BY monthvalue

UNION ALL

SELECT 
    IF(o.source_id = 3, 'phone',0)  TYPE,
        MONTH(date_created) AS monthvalue
        ,SUM(total_price) AS totalSale
FROM orders o
WHERE   o.source_id = 3 AND  date_created BETWEEN(CURDATE() - INTERVAL 1 MONTH)
    AND CURDATE()
GROUP BY monthvalue

得到的结果:-

TYPE    monthvalue  totalSale   
online  6            38300
visit   6            16800
phone   5              500
phone   6             7150

预期结果:

Type    May     June
online  0       38300
Visit   0       16800
Phone   500      7150

推荐答案

根据您的数据库架构，如果只希望获得预期的结果，请尝试使用sql;)

According to your database schema, if you just want your expected result, please try following sql;)

select 
    case source_id when 1 then 'online' when 2 then 'visit' when 3 then 'phone' end as `Type`,
    sum(if(month(date_created) = 5, total_price, 0)) as `May`,
    sum(if(month(date_created) = 6, total_price, 0)) as `June`
from `orders`
where date_created between (curdate() - interval 1 month) and curdate()
group by source_id 

SQLFiddle DEMO HERE .

这篇关于您可以帮助修改查询或其他查询以获得预期结果吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！