我在 sql 的数据库中有这个2 table
表1：
USER id | state| TYPE | time |
1 | 1 | 1 | time |
2 | 1 | 2 | time |
3 | 1 | 2 | time |
4 | 1 | 2 | time |
5 | 0 | 1 | time |
6 | 0 | 1 | time |
表2：
id |USER id| run
1 | 3 | 7
2 | 1 | 5
3 | 1 | 5
4 | 4 | 8
5 | 2 | 6
6 | 2 | 6
7 | 3 | 7
8 | 3 | 7
9 | 3 | 7
10 | 3 | 7
11 | 2 | 6
12 | 4 | 8
13 | 4 | 8
14 | 1 | 5
15 | 2 | 6
16 | 2 | 6
17 | 5 | 9
18 | 4 | 8
我正在打印这个
SELECT * FROM TABLE1 WHERE state != 0
它将以这种方式打印行
USER ID 1
USER ID 2
USER ID 3
USER ID 4
但我想ascending这个by count
WHERE, Count = num of row of TABLE2
其中用户 ID = 1 或 2 或..N
这里：
count of USER id 1 = 3
count of USER id 2 = 5
count of USER id 3 = 5
count of USER id 4 = 4
现在我想上升
表 1 其中表 2 中的高计数到低计数以及高运行到低运行
USER ID 3
USER ID 2
USER ID 4
USER ID 1
使用 PHP 和 MYSQLI
请帮助我
select t1.id, count(t2.id) as t2_count
from table1 t1
left join table2 t2 on t1.`user id` = t2.`user id`
group by t1.id
order by t2_count desc