php - SQL 内部连接和限制记录

Question

这是我的简化表结构。

我有两个表。

TABLE 1

       ID        PTS        PID        TID
    ----------------------------------------
        1         3          1          22
        2         10         1          22
        3         5          1          22
        4         1          2          58
        5         0          2          58
        7         0          3          47
        8         5          3          47
        15        5          4          51
        15        3          4          51
        9         7          3          6
        10        0          1          8
        11        2          1          8
        12        4          5          1
        13        1          6          9
        14        2          5          12
        15        5          4          61
        15        6          4          61
        15        2          4          61
        16        0          7          100
        ect.      ect.       ect.       ect.


TABLE 2

      NAME       PID
    -------------------
      Jhon       1
      Peter      2
      Lisa       3
      Doe        4
      Joey       5
      Mike       6
      Debby      7
      ect.       ect.

现在我需要从每个 TID 中选择最新的两个 PTS，并将它们与表 2 中的 PID 行进行匹配，并计算平均值。

THE DESIRED OUTCOME

      NAME       AVG
    -------------------
      Jhon       4,25
      Peter      1,00
      Lisa       6,00
      Doe        4,00
      Joey       3,00
      Mike       1,00
      Debby      0,00
      ect.       ect.

澄清:PID 行是相关的。多个 TID 可以有相同的 PID，TID 可以有多个 PTS。我正在使用 PDO 套接字。

当时我的查询是:

$query = $db->prepare("SELECT IFNULL(AVG(pts), 0) AS P, TA1.PID AS TA1PID, name AS N FROM ".
                    "table1 TA1 LEFT JOIN table2 TA2 ON TA1.PID = TA2.PID ".
                    "GROUP BY name, TA2.PID ".
                    "ORDER BY TA1.id DESC");

但这会从 TID 计算所有的点 (PTS)。但我只想计算每个 TID 的两个最新点 (PTS)。我整天都在尝试不同的查询，但我无法弄清楚。我是 SQL 的新手，我设法让一个示例正常工作，但使用纯 PHP，它并不漂亮 :D

sqlFiddle: LINK

问题是它计算了所有的 TID 点的平均值。它只应计算每个 TID 的最后两个条目

我希望这是一个明确的问题。我已尽力解释我的问题。如有任何问题请提问。我在 Stackoverflow 上阅读过其他与我的问题类似的问题，但我无法修改它们以使其适合我。

Answer 1

您只能使用此查询从第一个表中选择最近的 2 行

select t1.id, t1.pts, t1.pid, t1.tid
from table1 t1
join table1 t2 on t2.id >= t1.id and t1.tid = t2.tid
group by t1.id
having count(*) <= 2

并将其插入原始查询而不是表 1

SELECT IFNULL(AVG(pts), 0) AS AVG, TA1.PID AS 
Table1_ID, name AS Name FROM
(
    select t1.id, t1.pts, t1.pid, t1.tid
    from table1 t1
    join table1 t2 on t2.id >= t1.id and t1.tid = t2.tid
    group by t1.id
    having count(*) <= 2
)
TA1 LEFT JOIN table2 TA2 ON TA1.PID = TA2.PID
      GROUP BY name, TA2.PID
      ORDER BY TA1.id DESC