mysql开发技巧笔记

行转列

姓名 打怪数 猪八戒 10 猪八戒 2 猪八戒 12 沙僧 3 沙僧 5 沙僧 1 孙悟空 20 孙悟空 10 孙悟空 17 孙悟空 猪八戒 沙僧 47 24 9

SELECT u1.user_name,SUM(kills) FROM user1 u1JOIN user_kills uk ON u1.id = uk.user_idGROUP 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 = '孙悟空')cSELECT 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 mobileFROM    tb_sequence aCROSS 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_idUNION 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_idUNION ALL SELECT user_name,'shoe' AS equipment,shoe AS eq_name    FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_idORDER 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_idJOIN (        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.skillFROM user1 uJOIN user1_skill s1 ON u.id = s1.user_idJOIN user1_skill s2 ON u.id = s2.user_idWHERE 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.skillFROM user1 uLEFT JOIN user1_skill s1 ON u.id = s1.user_id AND s1.skill='念经' AND s1.skill_level > 0LEFT JOIN user1_skill s2 ON u.id = s2.user_id AND s2.skill='变化' AND s2.skill_level > 0LEFT JOIN user1_skill s3 ON u.id = s3.user_id AND s3.skill='腾云' AND s3.skill_level > 0LEFT JOIN user1_skill s4 ON u.id = s4.user_id AND s4.skill='浮水' AND s4.skill_level > 0WHERE     (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_nameFROM user1 uJOIN user1_skill s ON u.id = s.user_idWHERE s.skill IN('变化','念经','腾云','浮水') AND s.skill_level > 0GROUP BY u.user_name HAVING COUNT(*) >= 2

如何计算累进税问题

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

SELECT user_name,money,low,high,LEAST(money - low,high - low) AS curmoney,rateFROM user1 u JOIN taxrate t ON u.money > t.lowORDER 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) moneyFROM(    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) cGROUP BY user_name

user_name money 唐僧 7745.00 孙悟空 5995.00 沙僧 1045.00 猪八戒 2745.00