使用子选择进行选择 [英] select using sub select

问题描述

我有一个包含统计信息的表格，其中每一行包括 player_id、game_id 和 team_id.我正在尝试创建一个查询以返回结果，其中返回不同的游戏，其中两个或多个玩家在同一团队的游戏中一起玩.我试图创建一个这样的选择语句，但显然这是错误的.

I have a table with stats where each row includes player_id, game_id, and team_id. I am trying to create a query to return result where distinct games are returned where two or more players played together in a game on same team. I trying to create a select statement like this but obviously this is wrong.

SELECT * FROM (
 SELECT * FROM stats AS t1 WHERE player_id IN (array)
 ) AS t2 
WHERE t1.game_id=t2.game_id AND t1.team_id=t2.team_id

推荐答案

如果你的表有一个 id 你可以使用这个 sql 和 INNER JOIN

If your table has a id you can use this sql with INNER JOIN

SELECT t1.*,t2.*
FROM stats t1
INNER JOIN stats t2 
  ON  t1.game_id=t2.game_id 
  AND t1.team_id=t2.team_id 
  AND t2.id <> t1.id
WHERE t1. player_id IN (array);

您可以将 SELECT 修改为 SELECT DISTINCT t1.game_id

The you can modify the SELECT to SELECT DISTINCT t1.game_id

这是我的表格结果:

MariaDB [Bernd]> SELECT * FROM softball_stats;
+-------------+-----------+---------+---------------+
| sb_stats_id | player_id | game_id | sb_stats_team |
+-------------+-----------+---------+---------------+
|           1 |       100 |       1 |          1000 |
|           2 |       100 |       2 |          1000 |
|           3 |       100 |       3 |          1010 |
|           4 |       101 |       2 |          1000 |
|           5 |       102 |       3 |          1010 |
|           6 |       103 |       1 |          1000 |
+-------------+-----------+---------+---------------+
6 rows in set (0.01 sec)

MariaDB [Bernd]> 
MariaDB [Bernd]> SELECT t1.*,t2.*
    -> FROM softball_stats t1
    -> INNER JOIN softball_stats t2 
    ->   ON  t1.game_id=t2.game_id 
    ->   AND t1.sb_stats_team=t2.sb_stats_team 
    ->   AND t2.sb_stats_id <> t1.sb_stats_id
    -> WHERE t1. player_id IN (100,101);
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
| sb_stats_id | player_id | game_id | sb_stats_team | sb_stats_id | player_id | game_id | sb_stats_team |
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
|           4 |       101 |       2 |          1000 |           2 |       100 |       2 |          1000 |
|           2 |       100 |       2 |          1000 |           4 |       101 |       2 |          1000 |
|           3 |       100 |       3 |          1010 |           5 |       102 |       3 |          1010 |
|           1 |       100 |       1 |          1000 |           6 |       103 |       1 |          1000 |
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
4 rows in set (0.01 sec)

MariaDB [Bernd]> 

这篇关于使用子选择进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！