这是我拥有的一个简化表结构。
我有两张桌子。
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:链接
问题是，它计算了Average中的所有TID点。它只应计算每个TID 的最后两个条目
我希望这是一个明确的问题。我已尽力解释我的问题。如果有任何问题，请提问。我在Stackoverflow上读到了其他类似的问题，但我无法修改它们来为我工作。
使用此查询，您只能从第一个表中选择最近的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
在大多数DB中，按N分组非常容易。只需使用ROW_NUMBER。不幸的是MySQL没有它们，所以我们必须模拟它
SELECT name,
Avg(PTS)
FROM
(
SELECT *,
CASE
WHEN @previousPID IS NULL
OR @previousTID IS NULL
OR ( @previousPID = ORDERED.pid
AND @previousTID = ORDERED.tid )
THEN @rownum := @rownum + 1
ELSE @rownum := 1
end rn,
@previousPID := ORDERED.pid ,
@previousTID := ORDERED.tid
FROM (SELECT t2.name,
t2.pid,
t1.id,
t1.tid ,
t1.Pts
FROM table2 t2
INNER JOIN table1 t1
ON T2.pid = t1.pid
ORDER BY t1.pid,
t1.tid,
t1.id DESC)ORDERED,
(SELECT @rownum := 0,
@previousPID := NULL,
@previousTID := NULL) t) CTE
WHERE CTE.rn <= 2
GROUP BY name
具有以下结果
| NAME | AVG(PTS) |
|-----------|----------|
| Doe | 4 |
| Jhon | 4.25 |
| Joey | 3 |
| Lisa | 4 |
| Mike | 1 |
| No points | 0 |
| Peter | 0.5 |
演示
查看中间结果可能有助于理解CASE语句如何生成行号
| NAME | PID | ID | TID | PTS | @ROWNUM := 0 | @PREVIOUSPID := NULL | @PREVIOUSTID := NULL | RN | @PREVIOUSPID := ORDERED.PID | @PREVIOUSTID := ORDERED.TID |
|-----------|-----|----|-----|-----|--------------|----------------------|----------------------|----|-----------------------------|-----------------------------|
| Jhon | 1 | 3 | 22 | 5 | 0 | (null) | (null) | 1 | 1 | 22 |
| Jhon | 1 | 2 | 22 | 10 | 0 | (null) | (null) | 2 | 1 | 22 |
| Jhon | 1 | 1 | 22 | 3 | 0 | (null) | (null) | 3 | 1 | 22 |
| Jhon | 1 | 12 | 8 | 2 | 0 | (null) | (null) | 1 | 1 | 8 |
| Jhon | 1 | 11 | 8 | 0 | 0 | (null) | (null) | 2 | 1 | 8 |
| Peter | 2 | 5 | 58 | 0 | 0 | (null) | (null) | 1 | 2 | 58 |
| Peter | 2 | 4 | 58 | 1 | 0 | (null) | (null) | 2 | 2 | 58 |
| Lisa | 3 | 7 | 47 | 5 | 0 | (null) | (null) | 1 | 3 | 47 |
| Lisa | 3 | 6 | 47 | 0 | 0 | (null) | (null) | 2 | 3 | 47 |
| Lisa | 3 | 10 | 6 | 7 | 0 | (null) | (null) | 1 | 3 | 6 |
| Doe | 4 | 9 | 51 | 3 | 0 | (null) | (null) | 1 | 4 | 51 |
| Doe | 4 | 8 | 51 | 5 | 0 | (null) | (null) | 2 | 4 | 51 |
| Doe | 4 | 19 | 61 | 2 | 0 | (null) | (null) | 1 | 4 | 61 |
| Doe | 4 | 17 | 61 | 6 | 0 | (null) | (null) | 2 | 4 | 61 |
| Doe | 4 | 16 | 61 | 5 | 0 | (null) | (null) | 3 | 4 | 61 |
| Joey | 5 | 13 | 1 | 4 | 0 | (null) | (null) | 1 | 5 | 1 |
| Joey | 5 | 15 | 12 | 2 | 0 | (null) | (null) | 1 | 5 | 12 |
| Mike | 6 | 14 | 9 | 1 | 0 | (null) | (null) | 1 | 6 | 9 |
| No points | 7 | 18 | 100 | 0 | 0 | (null) | (null) | 1 | 7 | 100 |