Question

sql left join 连用问题，计算总算 并排序

SELECT t1.*, count(t2.*) AS num, t3* FROM `test1` AS t1 LEFT JOIN `test2` AS t2 t1.tid = t2.tid LEFT JOIN `test3` AS t3 ON t3.tid = t1.tid WHERE 其他条件 ORDER BY num DESC
表的内容
test1
tid name ......
1 rrrr
2 zzzz
3 cccc
4 xxxx
5 oooo

test2
id tid name......
1 3 lassd
2 4 aaaaa
3 3 mmmmm
4 3 ddddd
5 1 kkkkk
6 3 ppppp

test3
不太重要就省略

我的目的，想查询出 test2表里面t2.tid = t1.tid,即列出 tid=3时有三条记录；4为一条；1为一条；2为0条。
并按照num排序 以上的写法还是有问题的。
我的是mysql数据库
SELECT t1.*, count(t2.*) AS num, t3* FROM `test1` AS t1 LEFT JOIN `test2` AS t2 ON t1.tid = t2.tid LEFT JOIN `test3` AS t3 ON t3.tid = t1.tid WHERE 其他条件 ORDER BY num DESC

Answer 1

select ti.tid,count(t2.tid) as num,t3.name from test1 t1,test2 t2,test3 t3
where t1.tid=t2.tid,and t2.tid=t3.tid group by t1.tid order by count(t2.tid) as num desc

差不多了 你只要 加上个 group by t1.tid 把他们分类归组就可以了

Answer 2

select A.* ,B.* from test2 as A left join test1 as B on A.tid=B.tid
你后面的Num我不知道是在那里的，估计是你test3表的吧。你再left join test3 as C on A.X=C.X order by C.num
差不多是这样。

追问

num很明显啊 是count(t2.*) AS num 是这的

Answer 3

Try this:

SELECT t1.tid, t1.name, count(t2.id) AS num
FROM test1 t1 LEFT JOIN test2 t2
ON t2.tid = t1.id
GROUP BY t1.tid, t1.name
ORDER BY count(t2.id) DESC

追问

你好 test3是不能舍弃掉得 而且你的这个方法和我没有什么区别吧

追答

你的没有group by肯定出错
test不知道你要干嘛

追问

呵呵 没有看到 忘了写了。。。O(∩_∩)O谢谢