mysql两表数据合并

遇到的问题是，有两个单独的订单统计表，分别统计的是不同渠道的不同商品按日期的统计数据，在后台数据展示时，又要把两类渠道的数据，按时间顺序放在一起展示，如下是简化后的两个商品统计表ClassA和ClassB，大概思路就是个自group后合并到一起。

class ClassA(db.Model):
    __tablename__ = "classa"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    goods_id = db.Column(db.String(32), doc="商品ID")
    order = db.Column(db.Integer, doc="订单数")
    count = db.Column(db.Integer, doc="商品数")
    sales = db.Column(db.Integer, doc="交易额")
    business_date = db.Column(db.Date, doc="统计日期")

class ClassB(db.Model):
    __tablename__ = "classb"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    goods_id = db.Column(db.String(32), doc="商品ID")
    order = db.Column(db.Integer, doc="订单数")
    count = db.Column(db.Integer, doc="商品数")
    sales = db.Column(db.Integer, doc="交易额")
    business_date = db.Column(db.Date, doc="统计日期")

先复习一下知识点

各种join的区别(图片来自网络)

image
union 合并操作
按理说full join操作也可以，但数据库不认，就用union吧

#SQL UNION 不允许重复的值
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

#SQL UNION ALL 允许重复的值
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

sql语句如下

(select s1.c1, s1.c2,s1.c3,s1.c4,s2.c2, s2.c3,s2.c4 from 
    (select business_date as c1,
            sum(`order`) as c2,
            sum(count) as c3,
            sum(sales) as c4
    from classa 
    GROUP BY business_date) as s1 
    LEFT JOIN 
    (select business_date as c1,
            sum(`order`) as c2,
            sum(count) as c3,
            sum(sales) as c4
    from classb
    GROUP BY business_date) as s2 
    on s1.c1=s2.c1
    where s2.c1 is NULL
)
UNION ALL  --用UNION的时候，where s2.c1 is NULL可以不要
(select s2.c1, s1.c2,s1.c3,s1.c4,s2.c2, s2.c3,s2.c4 from 
    (select business_date as c1,
            sum(`order`) as c2,
            sum(count) as c3,
            sum(sales) as c4
    from classa
    GROUP BY business_date) as s1
    RIGHT JOIN 
    (select business_date as c1,
            sum(`order`) as c2,
            sum(count) as c3,
            sum(sales) as c4 
    from classb
    GROUP BY business_date) as s2 
on s1.c1=s2.c1);

sqlalchemy 代码

classa = ClassA.query.filter()
classb = ClassB.query.filter()

sum1 = classa.with_entities(ClassA.business_date,
                            func.sum(ClassA.count).label('count'),
                            func.sum(ClassA.order).label('order'),
                            func.sum(ClassA.sales).label('sales'))\
                    .group_by(ClassA.business_date)\
                    .subquery()
sum2 = classb.with_entities(ClassB.business_date,
                            func.sum(ClassB.count).label('count'),
                            func.sum(ClassB.order).label('order'),
                            func.sum(ClassB.sales).label('sales'))\
                    .group_by(ClassB.business_date)\
                    .subquery()

s1 = db.session.query(sum1.c.business_date,
                      sum1.c.count,
                      sum1.c.order,
                      sum1.c.sales,
                      sum2.c.count,
                      sum2.c.order,
                      sum2.c.sales)\
               .outerjoin(sum2, 
                      sum1.c.business_date==sum2.c.business_date)\
               .filter(sum2.c.business_date==None)
s2 = db.session.query(sum2.c.business_date,
                      sum1.c.count,
                      sum1.c.order,
                      sum1.c.sales,
                      sum2.c.count,
                      sum2.c.order,
                      sum2.c.sales)\
               .outerjoin(sum1,
                      sum1.c.business_date==sum2.c.business_date)
ret = s1.union(s2).all()