首页 > 数据库技术 > 详细

mysql 查询 两个表中不同字段的 和,并通过两个表的时间来分组

时间:2019-03-07 10:11:58      阅读:233      评论:0      收藏:0      [点我收藏+]

 

 

 

 

 

( SELECT
sum( a.cost_sum ) AS sum_cost,
sum( a.phone_sum ) AS sum_phone,
sum( a.arrive_sum ) AS sum_arrive,
FORMAT( sum( a.cost_sum ) / sum( a.arrive_sum ), 0 ) AS arrive_cb,
a.time AS time 
FROM
    (
        SELECT
            FROM_UNIXTIME( time, "%Y-%m-%d" ) AS time,
            sum( cost ) AS cost_sum,
            sum( arrive_true ) AS arrive_sum,
            sum( phone ) AS phone_sum 
        FROM
            `cmf_test_jj_data` 
        GROUP BY
            `time` UNION
            (
        SELECT
            FROM_UNIXTIME( time, "%Y-%m-%d" ) AS time,
            0 AS cost_sum,
            sum( arrive_num ) AS arrive_sum,
            0 AS phone_sum 
        FROM
            cmf_test_jj_arrive_new 
        GROUP BY
            time 
            ) 
    ) a 
WHERE
    ( `a`.`time` >= 0 AND `a`.`time` <= 1551801600 ) 
GROUP BY
    a.time 
ORDER BY
    a.time DESC 
    )

 

大的方面   为   select  ... from  (查询表) a where  ... group by .... order by .....

 

查询表  为  将两个表union ,   union时, 两张表查询出来的字段名 必须是一致的,  没有的字段, 可以用   “ 0 as column ”   来补充。

 

 

 

 

 

 

 

 

 

 

1

 

mysql 查询 两个表中不同字段的 和,并通过两个表的时间来分组

原文:https://www.cnblogs.com/Skate0rDie/p/10487336.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!