原始数据查询语句如下,需要查出实名用户和非实名用户的总数和总金额。
select real_flg 是否实名, count(*) 笔数,sum(amt) 总额 from t_user where sts = 'S' group by real_flg;
通过group by后查出来是多行的列表,但我需要的结果是四个字段别名分别存这四个值,
也不想通过两条sql(唯一区别就是where real_flg = 1 和 where real_flg = 0的区别)来实现。这时就可以用列转行了。
sql如下
select
sum(decode(real_flg, '1', count(*), 0)) as 实名笔数,
sum(decode(real_flg, '1', sum(amt), 0)) as 实名总额,
sum(decode(real_flg, '0', count(*), 0)) as 未实名笔数,
sum(decode(real_flg, '0', sum(amt), 0)) as 未实名总额
from t_user where sts= 'S' group by real_flg;
主要是用到了sum ,decode,group by。如上第一个decode字段的意思是 real_flg = 1 时,求count(*), 没有 = 1的情况就显示最后那个参数作为默认值,也就是0。下同。
但decode是oracle才有的,其他的数据库只能用类似的decode的功能来代替。类似的有聚合函数 和case when 来代替。
行转列(PIVOT)与列转行(UNPIVOT)是类似矩阵倒置的效果,不是我本次想要的结果。 有兴趣的可以去了解一下。
下面看下mysql行转列的例子
t_order存了售货员的销售单子,现需统计每个售货员的笔数和金额(包括全部、成功、失败)
第一步根据销售员先分组
# 查询每个销售员 成功笔数,成功金额、失败笔数、失败金额
SELECT tx_dt tx_dt, sale_cnl sale_cnl, ord_sts , COUNT(*) tx_num, IFNULL(SUM(amt),0) tx_amt
FROM t_order
WHERE tx_dt = '20210421'
GROUP BY sale_cnl, ord_sts
销售员10号只有一笔失败记录,销售员13号1笔失败1954笔成功记录
第二步,行转列
SELECT t.sale_cnl , SUM(t.tx_num) all_num, SUM(t.tx_amt) all_amt,
SUM(CASE WHEN t.ord_sts='F1' THEN t.tx_num ELSE 0 END) AS fail_num,
SUM(CASE WHEN t.ord_sts='F1' THEN t.tx_amt ELSE 0 END) AS fail_amt,
SUM(CASE WHEN t.ord_sts='S1' THEN t.tx_num ELSE 0 END) AS succ_num,
SUM(CASE WHEN t.ord_sts='S1' THEN t.tx_amt ELSE 0 END) AS succ_amt
FROM (
SELECT tx_dt tx_dt, sale_cnl sale_cnl, ord_sts , COUNT(*) tx_num, IFNULL(SUM(amt),0) tx_amt
FROM t_order
WHERE tx_dt = '20210421'
GROUP BY sale_cnl, ord_sts
) t GROUP BY t.sale_cnl
1.最外面的sum无需空处理, 因为里面一层的子查询已做ifnull处理就不会出现空。
2.行转列最关键的是在于casewhen,作用是根据ord_sts的值进行分类,将每个销售员的数据行的列做了转换,在新的列(比如fail_num)里面只有一行是原来的值其余的都是0,接下来与第一步简单的group by进行sum()就可以了(用max()代替sum() 结果都一样因为只有一行不是0)。