行转列

SELECT u1.user_name,SUM(kills) FROM user1 u1
JOIN user_kills uk ON u1.id = uk.user_id
GROUP BY u1.user_name;

结果

使用 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

列转行

应用场景

1. 属性拆分

2. etl数据处理

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

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

2. 使用union进行列转行

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

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

常见的子查询使用场景

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

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

同一属性多值过滤

使用关联的方式实现多属性查询(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

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

mysql生成行号

SELECT
    COUNT(*) AS rowNum,
    E1.first_name
FROM
    employees E1,
    employees E2
WHERE
    E1.first_name >= E2.first_name
GROUP BY
    E1.emp_no

SELECT
    (@rowNum :=@rowNum + 1) AS rowNum,
    first_name
FROM
    employees,
    (SELECT(@rowNum := 0)) num
ORDER BY
    first_name DESC

FROM_UNIXTIME(pubTime,'%Y') = year(CURDATE())

收支纪录计算余额

db.png

status 1 收入
status 2 支出

select 
m.id,
m.money,
m.status,
m.time,
(
    select SUM(
            case when mm.`status` = 1 then mm.money 
            when mm.status = 2 then -mm.money end
            )
        from money mm WHERE m.time <= mm.time) sum

from money m order by m.time desc