发表说说数量最多的user

需求发表说说数量最多的user  三张表 t_user t_userinfo t_talk.见名知意

 

先建一张表test，做个测试 

 

 

mysql> select * from test;
+------+------+
| id   | num  |
+------+------+
|   14 |    1   |
|   14 |    3   |
|   14 |    4   |
|   10 |    5   |
+------+------+

 

明知会得到多个结果 

mysql> select num from test where id=(select max(id) from test)

    -> ;
+------+
| num  |
+------+
|    1 |
|    3 |
|    4 |
+------+

 加个limit，就得到一条结果

mysql> select num from test where id=(select max(id) from test) limit 1
    -> ;
+------+
| num  |
+------+
|    1 |
+------+

 得到t_talk里面发表说最多的人的uid

 

select  t2.publisher from 
	(select count(cont) contSize,publisher from t_talk group by publisher) t2 
 	where t2.contSize= 
 	(select max(t1.contSize) from (select count(cont) contSize from t_talk group by publisher) t1)

+-----------+
| publisher |
+-----------+
|         2 |
+-----------+

 到此为此，拿到publisher就可以用hibernate去拿Load了。但是t_talk里有t_user外键，如何用hql来描述上面这一段sql呢。我晕。

 

我描述错了，是取说说最多前几位用户。以下hql语句居然可以。但我把hibernate的东东用成这个样子，感觉到shamfull.

 

select u.uid,u.nikeName,i.imgUrl,count(*) from User u , Talk t,UserInfo i where t.publisher=u.uid	

 and i.uiid=u.uid  group by t.publisher order by count(*) desc

 

i.uiid中uiid不是entity类中的，居然是sql中的。why ?