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