关于SQL查询的问题.不能用UNION ALL

Question

select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '1' or b.alarm_type='2')
order by a.instance_id

select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '3' or b.alarm_type='4' or b.alarm_type='5')
order by a.instance_id

要先把b.alarm_type='1'和'2'的查出来.按照id排序.然后再把其他的查出来按照id排序
要求这两个SQL语句查询出来的结果显示在一个表里面.能实现么
就是说这两句合并成一句.急求.谢谢了
因为是用的HQL.不支持UNION ALL
如果在HQL里能有其他方法类似UNION的也可以.谢谢

Answer 1

这个方法可以显示在一个表的两个列里，但前提条件是这两个个列的ID要相同

select * from
(
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '1' or b.alarm_type='2')
order by a.instance_id
)aa
left join
(
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '3' or b.alarm_type='4' or b.alarm_type='5')
order by a.instance_id
)bb
on aa.instance_id=bb.instance_id

Answer 2

联合查询不支持排序，多嵌套一次查询就可以了，如果要去掉重复的就不要All
select * from (
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '1' or b.alarm_type='2')
union all
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '3' or b.alarm_type='4' or b.alarm_type='5')
)
order by instance_id追问

是用的HQL.所以用不了UNION ALL.

追答

SQL SERVER?

select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type
order by case when a.alarm_type='1' then 1
when a.alarm_type='2' then 1
else 3 end, a.instance_id

Answer 3

是这个意思吗？
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '1' or b.alarm_type='2' or b.alarm_type = '3' or b.alarm_type='4' or b.alarm_type='5')
order by a.alarm_type, a.instance_id追问

不是.我意思是把b.alarm_type = '1' 和'2'的排序.然后='2','3','4'的排序
就是要分别查询出来排序

追答

那case应该支持吧：
select a.instance_id,a.taskname,b.alarm_type_name from etl_alarm_info a,etl_alarm_type b
where a.alarm_type = b.alarm_type and (b.alarm_type = '1' or b.alarm_type='2' or b.alarm_type = '3' or b.alarm_type='4' or b.alarm_type='5')
order by case when a.alarm_type='1' or a.alarm_type='2' then 0 else 1 end , a.instance_id

追问

好像可以了.我先去吃饭.等同事回来让他弄几条数据测试下.

本回答被提问者采纳