SQL Complex case

Work often encounter very difficult data queries, operators do not know how your database tables are designed, and do not know what data your database records, he only know what data they need to see, even some of the data does not exist at all.

Difficulty of single-table query: A table of database tables, such as a room two people games, homeowner Houser challenger Challenger, only recorded the winner winner, as well as the score point

Demand, list players, the total number of lost today, the total number of wins today, the final score of the final query results are

The designer of the slot table, and the operator who raised the demand. To isolate this data, you have to use a lot of SQL techniques, not limited to case-when, the left join, right join,union

Take a look at the simplest query statement:

Select/*something*/from Table #从表中查询某字段

Based on the requirements, we can draw a preliminary sql:

Select Player,totallose,totalwin, (totallose+totalwin) from table #查询玩家输的总数, win total, final score,

Obviously from the table can not directly find the requirements of the field, but to go through a certain logical processing and operation

Select Player,totallose,totalwin, (Totallose+totalwin) from (/*something*/)

Inside the brackets (/***/) is that we need to do some logical processing of the data

The first step: Query the output, the loser to find out, as well as the total number of losses

(Select Case when Winner=houser and challenger when Winner=challenger then Houser else 0 end as player,

SUM (-point) as lose from table group by player) as a

The second step, leftist, to the original loss of the total number of people to find out

Left JOIN (SELECT winner as player, sum (point) as win Group by player) as B

On A.player=b.player

The third step, the winning people, and the total number of win to find out, that is, the leftist has just become right-connected

(Select Case when Winner=houser and challenger when Winner=challenger then Houser else 0 end as player, sum (-point) as Lo Se 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

The fourth step is to union the results of leftist and right.

Back to the simplest query we started with.

Select Player,totallose,totalwin, (Totallose+totalwin) from (/***/)

Finally, we have to complete the (/***/), the 12th step to find out the results as data from the players, and then union right to connect

SELECT

A.player as player, a.lose as Totallose, B.win as Totalwin, (Totallose+totalwin) as Total

From

( SELECT

Case if Winner=houser then challenger when Winner=challenger then Houser else 0 end as player, sum (-point) as Los E

　　 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 T Otal

From

( SELECT

　　　　 Case If 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

The end of this article ...

Address: https://mp.weixin.qq.com/s?__biz=MzI4NTEzMjc5Mw==&mid=2650554680&idx=1&sn= 4ba6ac9e6e41c17329d5944e5aaaa60d&chksm= F3f833aec48fbab8895402ba2f859064a8c71bda062769a47167360b60bdd21dea6edc8579a1#rd

SQL Complex case