在Mysql中如何将图一转变成图二,让我们慢慢来实现
图一图二
1、首先我们得准备两张
one_usertable
killtable
2、执行下面的sql语句,进行对每一个人的数量进行汇总
SELECT one_usertable.username, SUM(killtable.kills_number) as "数量"
from one_usertable JOIN killtable on
one_usertable.id =killtable.user_id GROUP BY one_usertable.username
得出如下的结果,那么现在是行的形式了,接下来,我们进行转列的形式
3、执行下面的sql语句
SELECT * FROM (
SELECT SUM(killtable.kills_number) as "陈小燕"
from one_usertable JOIN killtable on
one_usertable.id =killtable.user_id and one_usertable.username="陈小燕"
) a CROSS JOIN(
SELECT SUM(killtable.kills_number) as "陈小蝶"
from one_usertable JOIN killtable on
one_usertable.id =killtable.user_id and one_usertable.username="陈小蝶"
) b CROSS JOIN(
SELECT SUM(killtable.kills_number) as "陈小水"
from one_usertable JOIN killtable on
one_usertable.id =killtable.user_id and one_usertable.username="陈小水") c
那么我们要显示的行转列就会实现出来了
还有第二种实现方法,我们接下来实现CASE方法来实现
SELECT sum(case WHEN username="陈小燕" then kills_number end) as '陈小燕',
sum(case WHEN username="陈小蝶" then kills_number end) as '陈小蝶',
sum(case WHEN username="陈小水" then kills_number end) as '陈小水'
FROM one_usertable JOIN killtable on one_usertable.id=killtable.user_id
得出来的结果仍然是
但是我们用这两种行转列是存在一个缺点的,比如说,我们连接join次数会随着用户的次数增加而增加,影响效率不高,而且还会带来给我们写sql的复杂性,虽然在开发中我们用这种的技巧并不是很多,但是我们每天学多一点就是进步一点。