100分求解hql查询问题

[Sunny_kaka]

有两个对象,IP与IP来源,他们是多对一的关系,并且IP有个状态属性status,status=0时为未分配,1为已分配.
我想得到的结果如下:

IP来源名称 ip总数 未分配ip 已分配ip


我现在的hql如下写:

select ips.name,count(ip) from IP ip right join ip.source ips group by ips.id 

这样可以得到所有的IP来源以及ip总数.
有没有办法能在一个hql里面把未分配ip和已分配ip也一起查出来.

很仓促可能写的有点乱..
但是我觉得这种需求应该很普遍啊.应该有比较好的解决办法.
求解答.

[Sunny_kaka]

[Quote=引用 2 楼 qx8668 的回复:]
Java code
select ips.name,count(ip) from IP ip right join ip.source ips where ip.status=0 group by ips.id union select ips.name,count(ip) from IP ip right join ip.source ips where ip.status=1 group by ips.id
不知道行不行，行的话告诉我一下，没试过
[/Quote]
union是把两个查询语句的结果连接到一起组成多条记录..
我的需求是返回的每条记录中包括[IP来源名称 ip总数 未分配ip 已分配ip]这四个属性

[awusoft]

三个集合相连
IP来源 IP总数
IP来源 未分配
IP来源 已分配

这样应该可以了吧

[visualC120]

HQL是啥？

[liuqianqian]

hql语句支持left join吗？我以前写的怎么不支持啊？

[FAXIU1982]

顶

[Sunny_kaka]

楼上高手的方法我都试了下,结果和实际需要的有些偏差..我自己又琢磨了一下午,现在总结一下.

8楼和10楼的方法都试了.8楼的连接3个表没有限制条件,得到是三个表的笛卡尔积,结果很乱.
10楼的限制条件没有左外连接,导致没有IP来源的那些公司显示不出来.

我重新说下表的对应关系:
公司表(ip_record_company) 1-------* IP来源表(ip_source) 1------* IP表(ip)

我想要的结果是所有公司的一个列表,就是IP总数为0的公司也要列出来.
字段包括公司名称,IP总数,未分配IP(status=0),已分配IP(status=1,2,3).

测试通过的结果:

select a.cid1, a.cname, a.totalIPs,b.unassignedIPs,c.assignedIPs from
	(select c1.id as cid1, c1.name as cname, count(ip1.id) as totalIPs 
	from ip as ip1 right join ip_source as ips1 on ip1.source_id=ips1.id right join ip_record_company as c1 on ips1.company_id=c1.id 
	group by c1.id) as a 
left join 
	(select c2.id as cid2, count(ip2.id) as unassignedIPs 
	from ip as ip2 right join ip_source ips2 on ip2.source_id=ips2.id right join ip_record_company c2 on ips2.company_id = c2.id 
	where ip2.status=0 group by c2.id) as b 
on a.cid1=b.cid2 left join
	(select c3.id as cid3, count(ip3.id) as assignedIPs 
	from ip as ip3 right join ip_source ips3 on ip3.source_id=ips3.id right join ip_record_company c3 on ips3.company_id = c3.id
	 where ip3.status in(1,2,3) group by c3.id) as c 
on b.cid2=c.cid3 

多谢楼上各位帮忙,特别是10楼.思路最重要啊~

[islandrabbit]

这个要求还是用SQL吧，因为HQL只支持subselect跟在where后面，不支持subselect跟在from后面。
反正Hibernate也可以直接用SQL：

  String sql = "select distinct a.totalIPs,b.assignedIPs,c.unassignedIPs, s.SOURCE_NAME from" + 
" (select i1.SOURCE_ID, count(*) as totalIPs from IP as i1 group by i1.SOURCE_ID) as a,"+
" (select i2.SOURCE_ID, count(*) as assignedIPs from IP as i2 where i2.IP_STATUS=1 group by i2.SOURCE_ID) as b," +
" (select i3.SOURCE_ID, count(*) as unassignedIPs from IP as i3 where i3.IP_STATUS=0 group by i3.SOURCE_ID) as c, Source as s";

            
            List<Object[]> list = session.createSQLQuery(sql).list();
            
            for(Object[] objArray : list){
            	System.out.println(objArray[0]);
            	System.out.println(objArray[1]);
            	System.out.println(objArray[2]);
            	System.out.println(objArray[3]);
            }

[镜花水月73]

[Quote=引用 10 楼 islandrabbit 的回复:]
这个要求还是用SQL吧，因为HQL只支持subselect跟在where后面，不支持subselect跟在from后面。
反正Hibernate也可以直接用SQL：
Java code
String sql="select distinct a.totalIPs,b.assignedIPs,c.unassignedIPs, s.SOURCE_NAME from"+" (select i1.SOURCE_ID, count(*) as totalIPs from IP as i1 group by i1.SOURCE_ID) as a,"+" (select i2.SOURCE_ID, count(*) as assignedIPs from IP as i2 where i2.IP_STATUS=1 group by i2.SOURCE_ID) as b,"+" (select i3.SOURCE_ID, count(*) as unassignedIPs from IP as i3 where i3.IP_STATUS=0 group by i3.SOURCE_ID) as c, Source as s";


List<Object[]> list= session.createSQLQuery(sql).list();for(Object[] objArray : list){
System.out.println(objArray[0]);
System.out.println(objArray[1]);
System.out.println(objArray[2]);
System.out.println(objArray[3]);
}
[/Quote]

好像可以哈

[xuexijava]

不会帮顶

[重返春季]

帮着顶一下！

[Z_FEI]

[Quote=引用 8 楼 awusoft 的回复:]
select a.sum1,b.sum2,c.sum3 from
( select count(*) as sum1 from IP group by 来源ID) as a,
(select count(*) as sum2 from IP where status=1 group by 来源ID) as b,
(select count(*) as sum3 from IP where status=0 group by 来源ID) as c
where a.来源id= b.来源ID and b.来源ID=c.来源ID
[/Quote]
感觉这个应该可可以的！

[码农小小鸟]

我写一个你拿去试试,A 表,B表,不知道可不可以用
select a.status as 已分配 from A as a where a.status='1' left join ((select a.status as 未分配 from A as a where a.status='0') left join (select b.name as 来源名,count(a.id) as 总数 from A as a left join B as b))

[bunrise]

其实你这个已经输入时一个简单的报表了，如果你用HQL，你得出来的值没有被映射
在实体里还是没有得到值吧，所以建议你用SQL，hibernate支持的

下面是ORACLE的写法

select distinct ips.name,
                count(*) over(partition by ips.ip) all_count,
                sum(decode(ip.status, 0, 0, 1)) count1,
                sum(decode(ip.status, 1, 0, 1)) count2
  from table_ips ips, table_ip ip
 where ip.id = ips.ipid

[岁月之梦]

[Quote=引用 3 楼 sean1203 的回复:]
水平有限
只能顶
给我点分
我要升5角
[/Quote]

同上....

[ComputerHeart]

[Quote=引用 8 楼 awusoft 的回复:]
select a.sum1,b.sum2,c.sum3 from
( select count(*) as sum1 from IP group by 来源ID) as a,
(select count(*) as sum2 from IP where status=1 group by 来源ID) as b,
(select count(*) as sum3 from IP where status=0 group by 来源ID) as c
where a.来源id= b.来源ID and b.来源ID=c.来源ID
[/Quote]
顶这个。。

[islandrabbit]

mark

[awusoft]

select a.sum1,b.sum2,c.sum3 from
( select count(*) as sum1 from IP group by 来源ID) as a,
(select count(*) as sum2 from IP where status=1 group by 来源ID) as b,
(select count(*) as sum3 from IP where status=0 group by 来源ID) as c
where a.来源id= b.来源ID and b.来源ID=c.来源ID

[ssc752000322]

路过

[全能码农-ss]

晕了

[awusoft]

where a.来源id= b.来源ID and b.来源ID=c.来源ID

这里不是吗?