问题描述
我有一个表玩家"如下在哪里:ID 是主键.
I have a table "player" as followwhere: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:删除具有条件重复值的行(重复列)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!