mysql:查找具有重复值和条件的行 [英] mysql: find rows with repeated values plus condition

问题描述

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

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

• date = 他们玩的日期(仅 1 个月，也可以从 1 到 30)
• 姓名 = 球员姓名
• 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     |
+----+------------+-------+-------------+

我想找到所有在一天内参加 2 项以上运动且其中一项必须是足球"的人(姓名和运动).

I want to find all the people (name and sport) who play more than 2 sports in one day and one of the sport has to be "football".

所以结果应该是这样的，

So the result should be like this,

+----+------+------+----------+
| ID | Date | Name |  Sport   |
+----+------+------+----------+
|  1 |    1 | A    | football |
|  2 |    1 | A    | soccer   |
|  4 |    2 | B    | tenis    |
|  5 |    2 | B    | football |
+----+------+------+----------+

我们不计算姓名C"，因为他不踢足球(即使他一天参加 2 项以上的运动).

We don't count Name "C" since he does not play football (even he play more than 2 sport in one day).

我试试

SELECT * FROM player GROUP BY Date, Name HAVING count(Date) > 1;

但不会给我我想要的.

PS:这篇文章不是重复的帖子.在 MySQL 中查找重复值中的那些答案不会直接针对这个问题.这是找到具有重复值和条件的行.请删除重复"标签，以便其他人可以从这个问题中受益.

PS: this post is not a duplicate post. Those answers in Finding duplicate values in MySQL wont directly target this question. this is finding the row with repeated values and condition. please remove the "duplicate" tag so others can benefit from this question.

推荐答案

你应该寻找这个:

表pl1有匹配的player name和date谁玩过football，pl2 包括计数，pl3 为您提供在特定日期玩过 football 和更多游戏的所有球员，然后您从 pl4<获取匹配数据/代码>

Table pl1 has matching player name and date who has played football, pl2 includes the count, pl3 gets you all those players who has played football and more games on a particular date and then you fetch the matching data from pl4

SELECT 
    pl4.*
FROM
    player pl4
        JOIN
    (SELECT 
        pl2.name, pl2.date, COUNT(pl2.name) numberofgames
    FROM
        player pl2
    JOIN (SELECT 
        date, name
    FROM
        player
    WHERE
        sport = 'football') pl1 ON (pl2.name = pl1.name
        AND pl2.date = pl1.date)
    GROUP BY pl2.name , pl2.date
    HAVING numberofgames > 1) pl3 ON (pl3.name = pl4.name
        AND pl3.date = pl4.date)

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