需求发表说说数量最多的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 ?