如何计算mysql中两个依赖列的出现次数

Question

我需要帮助解决我在mysql中遇到的问题。

因此，我有乒乓球的结果，结构如下：

W1 | L1 | W2 | L2 | W3 | L3 | W4 | L4 | W5 | L5
------------------------------------------------
 6   7    6    1    7    6
 6   1    6    1
 5   7    6    7
 6   1    7    5    6    7    6    7    7     5

可以玩最多5盘，每盘都保存在自己的列中(W1-胜利者第一盘，L1 -输家第一盘，.)。

所以我想知道有多少场比赛是赢了还是输了。领带断裂是Wx和Lx在7-6或6-7关系中的每一次发生.

我知道如何获得至少有一个领带中断的所有行，但是由于一行(match)可能有多个领带断点，我在计算它时有问题。有什么想法吗？

谢谢

Answer 1

首先，您的表架构不是最好的。更好的方案是：

Match | Set  | W | L
---------------------
  1      1     6   7
  1      2     6   3
  1      3     5   7
  1      4     3   6
  2      1     6   7
  2      2     6   7
  2      3     6   2
  2      4     3   6

那么您的查询将是：

SELECT count(*) FROM <table> WHERE (W=6 AND L=7) OR (W=7 AND L=6)

因此，我们可以通过SQL让您的表看起来类似于上面的表，然后在上面的SQL中使用该SQL作为子查询。让你的看起来像我的：

SELECT 1 as set, W1 as W, L1 as L FROM <table>
UNION ALL
SELECT 2,  W2 as W, L2 as L FROM <table>
UNION ALL
SELECT 3, W3 as W, L3 as L FROM <table>
UNION ALL
SELECT 4, W4 as W, L4 as L FROM <table>
UNION ALL
SELECT 5, W5 as W, L5 as L FROM <table>

将它们结合起来：

SELECT count(*) 
FROM (
  SELECT 1 as set, W1 as W, L1 as L FROM <table>
  UNION ALL
  SELECT 2,  W2 as W, L2 as L FROM <table>
  UNION ALL
  SELECT 3, W3 as W, L3 as L FROM <table>
  UNION ALL
  SELECT 4, W4 as W, L4 as L FROM <table>
  UNION ALL
  SELECT 5, W5 as W, L5 as L FROM <table>
) as unionSub
WHERE (W=6 AND L=7) OR (W=7 AND L=6)

也许您可以从这个Union查询中获得一个视图，并在将来使用它来引用这个表，因为我可以想象您最终会经常使用它。

Answer 2

当你有莱蒙斯做柠檬汽水。这并不重要，但也许能帮你到达那里。

SQL Fiddle演示

SELECT 
      CASE WHEN `W1` = 7 and `L1` = 6 THEN 1 ELSE 0 END +
      CASE WHEN `W2` = 7 and `L2` = 6 THEN 1 ELSE 0 END +
      CASE WHEN `W3` = 7 and `L3` = 6 THEN 1 ELSE 0 END +
      CASE WHEN `W4` = 7 and `L4` = 6 THEN 1 ELSE 0 END +
      CASE WHEN `W5` = 7 and `L5` = 6 THEN 1 ELSE 0 END  as Winner_break_won,
      CASE WHEN `W1` = 6 and `L1` = 7 THEN 1 ELSE 0 END +
      CASE WHEN `W2` = 6 and `L2` = 7 THEN 1 ELSE 0 END +
      CASE WHEN `W3` = 6 and `L3` = 7 THEN 1 ELSE 0 END +
      CASE WHEN `W4` = 6 and `L4` = 7 THEN 1 ELSE 0 END +
      CASE WHEN `W5` = 6 and `L5` = 7 THEN 1 ELSE 0 END  as Winner_break_lost
FROM tennis    

输出

| Winner_break_won | Winner_break_lost |
|------------------|-------------------|
|                1 |                 1 |
|                0 |                 0 |
|                0 |                 1 |
|                0 |                 2 |