欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql开发之行转列、列转行、如何计算累进税问题

程序员文章站 2022-05-03 19:32:48
行转列 姓名 打怪数 猪八戒 10 猪八戒 2 猪八戒 12...

行转列

姓名 打怪数
猪八戒 10
猪八戒 2
猪八戒 12
沙僧 3
沙僧 5
沙僧 1
孙悟空 20
孙悟空 10
孙悟空 17
孙悟空 猪八戒 沙僧
47 24 9
SELECT u1.user_name,SUM(kills) FROM user1 u1
JOIN user_kills uk ON u1.id = uk.user_id
GROUP BY u1.user_name;

结果

user_name SUM(kills)
孙悟空 47
沙僧 9
猪八戒 24

使用 SUM 进行 行转列

SELECT * FROM
(
    SELECT SUM(kills) as '沙僧' FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '沙僧'
)a CROSS JOIN
(
    SELECT SUM(kills) as '猪八戒' FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '猪八戒'
)b CROSS JOIN
(
    SELECT SUM(kills) as '孙悟空' FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '孙悟空'
)c

SELECT SUM(case WHEN user_name = '孙悟空' THEN kills END) AS '孙悟空' ,
    SUM(case WHEN user_name = '猪八戒' THEN kills END) AS '猪八戒' ,
    SUM(case WHEN user_name = '沙僧' THEN kills END) AS '沙僧' 
FROM user1 u JOIN user_kills uk ON u.id = uk.user_id
孙悟空 猪八戒 沙僧
47 24 9

列转行

应用场景

属性拆分
operator_id op_userid permission
1 10001 10070501,13011104,1301105,13010403
operator_id op_userid permission
1 10001 10070501
1 10001 13011104
1 10001 1301105
1 10001 13010403

2. etl数据处理

user_name mobile
唐僧 12112345678,14112345678,16112345678
猪八戒 12144643321,14144643321
孙悟空 12166666666,14166666666,16166666666,18166666666
沙僧 12198343214,14198343214
user_name mobile
唐僧 12112345678
唐僧 14112345678
唐僧 16112345678

1. 利用序列表处理列转行的数据

id
1
2
3
4
5
6
7
SELECT
    user_name,
    REPLACE (
        SUBSTRING(SUBSTRING_INDEX(mobile, ',', a.id),
        CHAR_LENGTH(SUBSTRING_INDEX(mobile, ',', a.id - 1)) + 1)
        ,',','') AS mobile
FROM
    tb_sequence a
CROSS JOIN (
    SELECT
        user_name,
        CONCAT(mobile, ',') AS mobile,
        LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 size
    FROM
        user1 b
) b ON a.id <= b.size
user_name mobile
唐僧 12112345678
唐僧 14112345678
唐僧 16112345678

2. 使用union进行列转行

user_name arms clothing shoe
唐僧 九环锡杖 锦襕袈裟 僧鞋
猪八戒 九齿钉耙 僧衣 僧鞋
孙悟空 金箍棒 锁子黄金甲 藕丝步云履
沙僧 降妖宝杖 僧衣 僧鞋
SELECT user_name,'arms' AS equipment,arms AS eq_name
    FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_id
UNION ALL -- union all 如果没有重复 效率更高 
SELECT user_name,'clothing' AS equipment,clothing AS eq_name
    FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_id
UNION ALL 
SELECT user_name,'shoe' AS equipment,shoe AS eq_name
    FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_id
ORDER BY user_name
user_name equipment eq_name
唐僧 arms 九环锡杖
唐僧 clothing 锦襕袈裟
唐僧 shoe 僧鞋

如何在子查询中匹配两个值

常见的子查询使用场景

查询出每一个取经人打怪最多的日期,并列出取经人的姓名,打怪最多的日期和打怪的数量

SELECT u.user_name,uk.timestr,uk.kills as kills FROM user1 u 
JOIN user_kills uk ON u.id = uk.user_id
JOIN (
        SELECT user_id,MAX(kills) AS kills FROM user_kills GROUP BY user_id
    )c ON uk.user_id = c.user_id AND uk.kills = c.kills
user_name timestr kills
猪八戒 2013-02-05 00:00:00 12
沙僧 2013-02-11 00:00:00 5
孙悟空 2013-01-11 00:00:00 20

同一属性多值过滤

使用关联的方式实现多属性查询(1)

查询 user1_skill中 同时具备 skill为 念经和变化的取经人

SELECT u.user_name,s1.skill,s2.skill
FROM user1 u
JOIN user1_skill s1 ON u.id = s1.user_id
JOIN user1_skill s2 ON u.id = s2.user_id
WHERE s1.skill = '念经' AND s2.skill = '变化'
AND s1.skill_level >0 AND s2.skill_level >0

使用关联的方式实现多属性查询(2)

查询 掌握的技能skill 大于等于2的取经人

SELECT u.user_name,s1.skill,s2.skill,s3.skill,s4.skill
FROM user1 u
LEFT JOIN user1_skill s1 ON u.id = s1.user_id AND s1.skill='念经' AND s1.skill_level > 0
LEFT JOIN user1_skill s2 ON u.id = s2.user_id AND s2.skill='变化' AND s2.skill_level > 0
LEFT JOIN user1_skill s3 ON u.id = s3.user_id AND s3.skill='腾云' AND s3.skill_level > 0
LEFT JOIN user1_skill s4 ON u.id = s4.user_id AND s4.skill='浮水' AND s4.skill_level > 0
WHERE 
    (CASE WHEN s1.skill IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN s2.skill IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN s3.skill IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN s4.skill IS NOT NULL THEN 1 ELSE 0 END) >= 2

使用GROUP BY实现多属性查询

SELECT u.user_name
FROM user1 u
JOIN user1_skill s ON u.id = s.user_id
WHERE s.skill IN('变化','念经','腾云','浮水') AND s.skill_level > 0
GROUP BY u.user_name HAVING COUNT(*) >= 2

如何计算累进税问题

先计算出各个区间 有多少钱需要缴税
SELECT user_name,money,low,high,
LEAST(money - low,high - low) AS curmoney,rate
FROM user1 u JOIN taxrate t ON u.money > t.low
ORDER BY user_name,low
user_name money low high curmoney rate
唐僧 35000.00 0 1500 1500.00 0.03
唐僧 35000.00 1500 4500 3000.00 0.1
唐僧 35000.00 4500 9000 4500.00 0.2
唐僧 35000.00 9000 35000 26000.00 0.25

2. 将需要缴税部分的钱 乘以 税率

SELECT user_name,SUM(curmoney*rate) money
FROM(
    SELECT user_name,money,low,high,
    LEAST(money - low,high - low) AS curmoney,rate
    FROM user1 u JOIN taxrate t ON u.money > t.low
    ORDER BY user_name,low
) c
GROUP BY user_name
user_name money
唐僧 7745.00
孙悟空 5995.00
沙僧 1045.00
猪八戒 2745.00