工作中往往会遇到非常棘手的数据查询,运营人员不知道你的数据库表是如何设计的,也不知道你的数据库记录了啥数据,他只知道自己需要看什么数据,甚至有些数据根本就不存在.
单表查询的难度: 一张数据库的表table,如下图,一个房间两个人对局,房主houser 挑战者challenger,只记录了赢家winner,以及分数point
需求,列出 玩家,今天输的总分数,今天赢的总分数,最终输赢的分数最终的查询的结果是
暂时不吐槽表的设计者,以及提出需求的运营人员. 要把这个数据查出来,得用很多sql技巧,不限于 case when ,left join ,right join,union
来看一个最简单的查询语句:
select /*something*/ from table #从表中查询某字段
根据需求我们可以得出初步sql:
select player,totallose,totalwin,(totallose+totalwin) from table #查询玩家输的总数,赢得总数,最终分数,
显然从table不能直接找到需求的字段,而是要经过一定的逻辑处理和运算
select player,totallose,totalwin,(totallose+totalwin) from (/*something*/)
括号里面的(/***/)就是我们需要对数据进行一些逻辑处理
第一步:查询输的,把输的人查出来,以及输的总数
(select case when winner=houser then challenger when winner=challenger then houser else 0 end AS player,
sum(-point) AS lose from table group by player ) AS a
第二步,左联一下,把原本输的人的赢得总数查出来
LEFT JOIN (SELECT winner AS player ,sum(point) AS win group by player) AS b
ON a.player=b.player
第三步,把赢的人,和赢的总数查出来,即把刚才的左联变成右连
(select case when winner=houser then challenger when winner=challenger then houser else 0 end AS player, sum(-point) AS lose from table group by player ) AS a
RIGHT JOIN (SELECT winner AS player ,sum(point) AS win group by player) AS b
ON a.player=b.player
第四步 把左联和右连的结果再union一下
回到 我们一开始的最简单的那条查询
select player,totallose,totalwin,(totallose+totalwin) from (/***/)
最终,我们要把 (/***/) 完成, 把第一二步查出来的结果作为数据从中查出玩家,再union一下右连接
SELECT
a.player AS player , a.lose AS totallose, b.win AS totalwin, (totallose+totalwin) AS total
FROM
( SELECT
case when winner=houser then challenger when winner=challenger then houser else 0 end AS player, sum(-point) AS lose
FROM
table
GROUP BY player
) AS a
LEFT JION
(SELECT
winner AS player , sum(point) AS win
FROM
table
GROUP BY
player) AS b
ON
a.player=b.player
UNION
SELECT
a.player AS player , a.lose AS totallose, b.win AS totalwin, (totallose+totalwin) AS total
FROM
( SELECT
case when winner=houser then challenger when winner=challenger then houser else 0 end AS player, sum(-point) AS lose
FROM
table
GROUP BY player
) AS a
RIGHT JION
(SELECT
winner AS player ,sum(point) AS win
FROM
table
GROUP BY
player) AS b
ON
a.player=b.player
ORDER BY total DESC
本文完......
地址:https://mp.weixin.qq.com/s?__biz=MzI4NTEzMjc5Mw==&mid=2650554680&idx=1&sn=4ba6ac9e6e41c17329d5944e5aaaa60d&chksm=f3f833aec48fbab8895402ba2f859064a8c71bda062769a47167360b60bdd21dea6edc8579a1#rd