sql复杂案例

时间:2022-07-08 20:50:20

  工作中往往会遇到非常棘手的数据查询,运营人员不知道你的数据库表是如何设计的,也不知道你的数据库记录了啥数据,他只知道自己需要看什么数据,甚至有些数据根本就不存在.

sql复杂案例

  单表查询的难度: 一张数据库的表table,如下图,一个房间两个人对局,房主houser 挑战者challenger,只记录了赢家winner,以及分数point

sql复杂案例

  需求,列出  玩家,今天输的总分数,今天赢的总分数,最终输赢的分数最终的查询的结果是

sql复杂案例

  暂时不吐槽表的设计者,以及提出需求的运营人员. 要把这个数据查出来,得用很多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