mysql - 如何检索mysql中 "count"列之前的最后一个id值？

Question

当我选择两个表的值时

SELECT 
   tab.id
   ,tab.name
   ,tab2.id
   ,tab2.name
   ,count(distinct tab3.id) as totalusers
FROM master tab
  LEFT JOIN user as tab2 ON tab.id = tab2.cod
  LEFT JOIN user as tab3 ON tab.id = tab3.cod
WHERE tab.id = 5 limit 1
ORDER BY tab2.id DESC

或者

SELECT 
   tab.id
   ,tab.name
   ,tab2.id
   ,tab2.name
   ,count(tab2.id)
FROM master tab
  LEFT JOIN user as tab2 ON tab.id = tab2.cod
WHERE tab.id = 5 limit 1
ORDER BY tab2.id DESC

我得到了

+ ------ + -------- + ------- + --------- + ------------------ + 
| tab.id | tab.name | tab2.id | tab2.name | count(tab3.id)     |
+ ------ + -------- + ------- + --------- + ------------------ + 
| 5      | home1    | 132     | joao      | 3                  |
+ ------ + -------- + ------- + --------- + ------------------ + 

但是tab2.id检索表的第一个值，当我计算值时顺序不起作用，我如何获取最后一个键134和他的名字？

用户

+ ---- + ----- + --- +
| id   | name  | cod |
+ ---- + ----- + --- +
| 132  | joao  |  5  |
+ ---- + ----- + --- +
| 133  | well  |  5  |
+ ---- + ----- + --- +
| 134  | cindy |  5  |
+ ---- + ----- + --- +

观察:MAX() 可以帮助( Return last id and count of id )，但我也需要用户的名称，我可以在没有子查询且只有一行(限制 1)的情况下进行连接吗？

Answer 1

似乎您需要内部联接和分组依据

select tab.id, tab,name, t2.id, t2.name, count(*)
from tab
inner join 
(select id, name, code from 
user where id = (select max(id) from user )) t on t.cod = tab.code
inner join user as uset.code = tab.code
group by tab.id, tab,name, t2.id, t2.name