mysql:删除具有条件重复值的行(重复列) [英] mysql: remove rows with repeated values with condition (repeated columns)

问题描述

我有一个表玩家"如下在哪里:ID 是主键.

I have a table "player" as follow where: ID is primary key.

• date = 他们玩的日期(仅 1 个月，也可以从 1 到 31)
• 姓名 = 球员姓名
• Sport = 他们玩的运动，列表中可以有很多运动；但我只关注那些打足球"并且一天打两场以上的人

这是玩家"表.

+----+------------+-------+-------------+
| ID | Date       | Name  | Sport       |
+----+------------+-------+-------------+
|  1 |          1 | A     | football    |
|  2 |          1 | A     | soccer      |
|  3 |          3 | A     | tennis      |
|  4 |          2 | B     | tennis      |
|  5 |          2 | B     | football    |
|  6 |          1 | C     | basketball  |
|  7 |          1 | C     | tennis      |
|  8 |          1 | C     | fishing     |
|  9 |          4 | D     | football    |
+----+------------+-------+-------------+

我想找到不这样做的人(姓名和运动)列表:

I want to find list of the people (name and sport) who DO NOT:

• 在一天内练习足球"+ 其他运动.

注意:如果有人在某一天玩足球"+其他游戏，我们会将他从当天的列表中删除.只在特定日期删除他.

note: if someone who play "football" + other game(s) in one day, we remove him from the list for that day. only remove him for that particular day.

所以结果应该是这样的，

So the result should be like this,

+----+------+------+-----------+
| ID | Date | Name |  Sport    |
+----+------+------+-----------+
|  3 |    3 | A    | tennis    |
|  6 |    1 | C    | basketball|
|  7 |    1 | C    | tennis    |
|  8 |    1 | C    | fishing   |
|  9 |    4 | D    | football  |
+----+------+------+-----------+

这是列出的后续问题mysql:查找具有重复值和条件的行

谢谢你的帮助！

推荐答案

你应该寻找这个:

在这里，我们省略了关键记录值(日期+姓名)，这些匹配在关键记录值(日期+姓名)谁踢过足球和<代码>没有踢过足球的关键记录值(日期+姓名)

Here, we are omitting the key record values (date + name) those match in an intersection of key record values (date + name) who played football and key record values (date + name) who did not play football

SELECT 
    p1.*
FROM
    player p1
        LEFT JOIN
    (SELECT 
        pnfb.*
    FROM
        (SELECT 
        date, name
    FROM
        player
    WHERE
        sport <> 'football') pnfb
    JOIN (SELECT 
        date, name
    FROM
        player
    WHERE
        sport = 'football') pfb ON (pnfb.date = pfb.date
        AND pnfb.name = pfb.name)) p2 ON (p1.date = p2.date AND p1.name = p2.name)
WHERE
    p2.date IS NULL;

这篇关于mysql:删除具有条件重复值的行(重复列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！