mysql开发技巧笔记

行转列

姓名	打怪数
猪八戒	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

列转行

应用场景

1. 属性拆分

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

如何计算累进税问题

1. 先计算出各个区间 有多少钱需要缴税

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