mysql中关于UNION ALL和case when中聚合函数的使用

mysql中关于UNION ALL和case when中聚合函数的用法

UNION ALL
需求：用户有两张收入相关的数据表，现在将两张表的数据一起查出并排序。两张表的字段不是全部相同，t_distributor_log 表中type和t_users_order 中的type含义不一样，需统一意义进行区分，确保两张表最后字段一致，这里使用UNION ALL是因为两张表的数据不需要去重，如果去重，则可以使用UNION 。

		(select 
			user_id as userId,
			(select user from t_user where id = user_id ) as userName, 
			0 as num,
			type as disType,
			to_user_profit as toUserProfit,
			create_time as createTime,
			4 as type
		from  t_distributor_log g
		where g.to_user_id = '1296397120605110274'
		)
		
		UNION ALL
		
		(select 
			user_id  as userId, 
			(select user from t_user where id = user_id ) as userName, 
			num,
			null as disType, 
			to_user_profit as toUserProfit,
			create_time as createTime,
			type as type
		from t_users_order o 
		where to_user_id = '1296397120605110274'
		)
		
		order by createTime desc

case when
需求：从订单表中统计每个公会下主播不同类型收入情况的日报，月报，这里要注意的是使用COUNT函数时，ELSE后面不能用0进行结束，而是用null, 用0会导致累加全部数据行，前面的条件就无效了，SUM函数可以使用else 0

select
        t.id as tradeId,
        t.trade_name as tradeName,
        IFNULL(tab.orderNum,0) as orderNum,
        IFNULL(tab.successNum,0) as successNum,
        IFNULL(tab.userOrderProfit,0) as userOrderProfit,
        IFNULL(tab.tradeProfit,0) as tradeProfit,
        IFNULL(tab.userGiftProfit,0) as userGiftProfit,
        IFNULL(tab.time,#{time}) as time,
        IFNULL((select o.real_money  from t_order o where o.user_id = t.id and status = 1),0) as tradeCash
        from t_trade t left join (
        select trade_id,
            COUNT(
                CASE type
                WHEN  2 THEN 1
                WHEN 3 THEN 1
                ELSE null END  ) as orderNum,
            COUNT(
                CASE order_status
                WHEN 2 THEN 1
                ELSE null END) as successNum,
            SUM(
                CASE type
                WHEN 2 THEN to_user_profit
                WHEN 3 THEN to_user_profit
                ELSE 0 END ) as userOrderProfit,
            SUM(
                CASE type
                WHEN 1 THEN to_user_profit
                ELSE 0 END) as userGiftProfit,
            SUM(trade_profit)as tradeProfit ,
            #{time} as time
        from t_users_order
        where create_time like concat('%',#{time},'%')
        group by trade_id
        ) tab
        on t.id = tab.trade_id
        order by  userOrderProfit  desc