mysql - 最少计数的MYSQL DB SELECT

This question already has answers here:

SQL Select only rows with Minimum Value on a Column with Where Condition
（2个答案）
SQL select only rows with max value on a column [duplicate]
（27个答案）
去年关门了。
我得到一个数据集作为一个表它看起来像这样：

###################################
UID    |TID     |MID     |RESULT  |
1      |1       |1       |1       |
2      |1       |1       |2       |
3      |1       |1       |3       |
4      |2       |1       |4       |
5      |2       |1       |5       |
6      |2       |1       |6       |
1      |1       |2       |6       |
2      |1       |2       |5       |
3      |1       |2       |4       |
4      |2       |2       |3       |
5      |2       |2       |2       |
6      |2       |2       |1       |
1      |1       |3       |2       |
2      |1       |3       |4       |
3      |1       |3       |6       |
4      |2       |3       |5       |
5      |2       |3       |3       |
6      |2       |3       |1       |

我现在想要每行的最小数量，按TID和MID分组
如果我这样做：

SELECT UID, TID, MID, RESULT
FROM matches
WHERE matches.id = '$ID'
ORDER BY TID, MID, RESULT

我把东西按正确的方式订好了。这样地：

###################################
UID    |TID     |MID     |RESULT  |
1      |1       |1       |1       |
2      |1       |1       |2       |
3      |1       |1       |3       |

3      |1       |2       |4       |
2      |1       |2       |5       |
1      |1       |2       |6       |

1      |1       |3       |2       |
2      |1       |3       |4       |
3      |1       |3       |6       |

4      |2       |1       |4       |
5      |2       |1       |5       |
6      |2       |1       |6       |

6      |2       |2       |1       |
5      |2       |2       |2       |
4      |2       |2       |3       |

6      |2       |3       |1       |
5      |2       |3       |3       |
4      |2       |3       |5       |

现在我只想有每个街区的第一排。计算每个块中每个UID的最佳频率。
我不知道如果不在php中做很多循环就如何得到它。如果我按TID、MID和MIN（RESULT）分组，那么输出哪个UID会有点随机。
解决方案：

SELECT COUNT(*) AS AMOUNT, t1.UID AS UID, t1.TID AS TID, t1.MID AS MID, t1.RESULT AS result
  FROM
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, um.q_result AS RESULT
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    ORDER BY TID, MID
  ) t1
  INNER JOIN
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, min(um.q_result) AS min_result
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    GROUP BY TID, MID
  ) t2 ON t2.TID = t1.TID AND t2.MID = t1.MID AND t2.min_result = t1.result
 GROUP BY t1.UID ORDER BY AMOUNT DESC

最佳答案

您可以将子查询与min一起用于按TID和MID列出的列结果组

  SELECT UID, TID, MID, RESULT
  FROM matches  m
  inner join (
    select TID, MID, min(result) min_result
    from matches
    group by TID, MID
  ) t on t.TID = m.TID and t.MID = m.MID and t.min_result = m.result

看看你的代码和示例，你在subselect中有一个错误的用户id，你没有group by，而是order by，所以我认为你应该这样更改代码

SELECT COUNT(*) AS AMOUNT, t1.UID AS UID, t1.TID AS TID, t1.MID AS MID, t1.RESULT AS result
  FROM
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, um.q_result AS RESULT
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    ORDER BY TID, MID
  ) t1
  INNER JOIN
  (
    SELECT um.user_id AS UID, um.team_id AS TID, um.match_id AS MID, min(um.q_result) AS min_result
    FROM user_matches um JOIN league_matches lm
    WHERE um.match_id = lm.id
    AND lm.league_subseason_id = '135'
    GROUP BY TID, MID
  ) t2 ON t2.TID = t1.TID AND t2.MID = t1.MID AND t2.min_result = t1.result
 GROUP BY t1.UID ORDER BY AMOUNT DESC

关于mysql - 最少计数的MYSQL DB SELECT ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48948182/