SELECT u1.user_name,SUM(kills) FROM user1 u1
JOIN user_kills uk ON u1.id = uk.user_id
GROUPBY u1.user_name;
结果
user_name
SUM(kills)
孙悟空
47
沙僧
9
猪八戒
24
使用 SUM 进行 行转列
SELECT * FROM
(
SELECTSUM(kills) as'沙僧'FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '沙僧'
)a CROSSJOIN
(
SELECTSUM(kills) as'猪八戒'FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '猪八戒'
)b CROSSJOIN
(
SELECTSUM(kills) as'孙悟空'FROM user1 u JOIN user_kills uk on u.id = uk.user_id AND u.user_name = '孙悟空'
)c
SELECTSUM(caseWHEN user_name = '孙悟空'THEN kills END) AS'孙悟空' ,
SUM(caseWHEN user_name = '猪八戒'THEN kills END) AS'猪八戒' ,
SUM(caseWHEN 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
CROSSJOIN (
SELECT
user_name,
CONCAT(mobile, ',') AS mobile,
LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1sizeFROM
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
UNIONALL -- unionall 如果没有重复 效率更高
SELECT user_name,'clothing'AS equipment,clothing AS eq_name
FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_id
UNIONALLSELECT user_name,'shoe'AS equipment,shoe AS eq_name
FROM user1 u JOIN user1_equipment ue ON u.id = ue.user_id
ORDERBY 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 GROUPBY 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 >0AND s2.skill_level >0
使用关联的方式实现多属性查询(2)
查询 掌握的技能skill 大于等于2的取经人
SELECT u.user_name,s1.skill,s2.skill,s3.skill,s4.skill
FROM user1 u
LEFTJOIN user1_skill s1 ON u.id = s1.user_id AND s1.skill='念经'AND s1.skill_level > 0LEFTJOIN user1_skill s2 ON u.id = s2.user_id AND s2.skill='变化'AND s2.skill_level > 0LEFTJOIN user1_skill s3 ON u.id = s3.user_id AND s3.skill='腾云'AND s3.skill_level > 0LEFTJOIN user1_skill s4 ON u.id = s4.user_id AND s4.skill='浮水'AND s4.skill_level > 0WHERE
(CASEWHEN s1.skill ISNOTNULLTHEN1ELSE0END) +
(CASEWHEN s2.skill ISNOTNULLTHEN1ELSE0END) +
(CASEWHEN s3.skill ISNOTNULLTHEN1ELSE0END) +
(CASEWHEN s4.skill ISNOTNULLTHEN1ELSE0END) >= 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 > 0GROUPBY u.user_name HAVINGCOUNT(*) >= 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
ORDERBY 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
ORDERBY user_name,low
) c
GROUPBY user_name