高分求一条HQL多对多查询语句

[newflypig]

一个学科表（Field），一个用户表（User），多对多关系。
学科表有两个字段，id和descripiton，用户表有众多字段，不罗列了

Field中有SET集合users
User中有SET集合fields

现在要查掌握id like '520%'的女性用户


我这样写的：
from User u,Field f where u.gender='m' in (select f.users from Field where f.id like '520%')

持续报错！

高分献上，谢谢帮助

[newflypig]

改成
from User u where u.gender='m' and u in elements(select f.users from Field f where f.id like '520%')
还不行，快要跪求了。。。

[newflypig]

还是没人，“噗通一声”，我跪求了。。。
这个问题如此普遍啊！

[默然说话]

select u from User u,Field f where u.gender='m' and f in (select f2 from Field f2 where f2.id like '520%')

不知道行不行，谢谢。

[默然说话]

给点错误信息呀。

[newflypig]

不好意思啊，楼上的，你的HQL还是不成！

[newflypig]

我这么写select u from User u where u.gender='m' and u in (select f.users from Field f where f.id like '520%')

tomcat报这个：

Hibernate: select user0_.id as id0_, user0_.edu_level as edu2_0_, user0_.spec_id as spec3_0_, user0_.workspaceID as workspac4_0_, user0_.account as account0_, user0_.examin as exam
in0_, user0_.isCityer as isCityer0_, user0_.password as password0_, user0_.name as name0_, user0_.gender as gender0_, user0_.birthday as birthday0_, user0_.city as city0_, user0_.m
obilephone as mobilep13_0_, user0_.identify as identify0_, user0_.email as email0_, user0_.address as address0_, user0_.college as college0_, user0_.remarks as remarks0_, user0_.up
datetime as updatetime0_, user0_.tips as tips0_, user0_.trains as trains0_ from test.t_user user0_ where user0_.gender='m' and (user0_.id in (select . from test.t_field field1_, r_
user_field users2_, test.t_user user3_ where field1_.id=users2_.field_id and users2_.user_id=user3_.id and (field1_.id like '520%')))
16:56:29,171 WARN JDBCExceptionReporter:100 - SQL Error: 1064, SQLState: 42000
16:56:29,171 ERROR JDBCExceptionReporter:101 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'from test.t_field field1_, r_user_field users2_, test.t_user user3_ where field1' at line 1

[newflypig]

怎么就不行呢,我好郁闷啊
select f.users from Field f where f.id like '520%'可以查
from User u where u.gender = 'm'也可以查

怎么这两个组合起来就不能查呢，我快崩溃了。。。。。。。。。。。。

[默然说话]

语法有问题。。。。

[默然说话]

Google了一下，不小心找到一条HQL，不知道合不合你用。

select f from User u inner join u.fields f where u.gender='m' and f.id like '520%'

注意，这里的f是u.fields的别名。不知道能不能解决你的问题。

[默然说话]

经测试通过。可以查出来的。

select u from User u inner join u.fields f where u.gender='m' and f.id like '520%'

谢谢

[newflypig]

11楼，你的“1”让我很无奈，难道你想让我用JAVA语法去用.getFields()遍历结果集，那也太搓了吧，你老板会让你这么干？

[默然说话]

耶！！！赚到啦！！！！150分呀~~~~~~

[daisycool]

先提两个问题：

1. 根据LZ的问题，要“现在要查掌握id like '520%'的女性用户“， 根本用不着Field表啊，直接一个SELECT u FROM user WHERE id LIKE '520%' AND gender='f' 就行了。猜测一下LZ是否是想查找id为520%的用户的学科？如果是这样的话，用'SELECT u FROM user u WHERE id LIKE '520%' AND gender='f' 得到所有相关用户，然后每个用户有User.getFields()来得到所有学科啊。

2. LZ是否为这一多对多的关系建立中间表格？比如： Field --- field_user --- User，如果有的话，查询时

3. 查找女性用户，应该是gender='f'啊，LZ的是'm'，但这只会导致查询结果错误。

[fdg174456]

耶找到了

[Kinki_Yang]

就是找这个，谢楼主和mouyong啦！

[yexiaqiu]

哦也，找到了，同楼上！谢谢楼主和mouyong！O(∩_∩)O哈哈~开心呐

[jinianmutou]

同楼上！谢谢楼主和mouyong！O(∩_∩)O哈哈