通过联合使用订单 [英] Using Order by With Union

问题描述

  ALTER   proc  [dbo].[SP_Med_SelectAllDisease_ActiveMatByMedID]
 @ P_MedID   int 
为
开始
选择 不同英文名， NULL   AS  DiseaseName
来自 Med，Active_material，Active_mat_med，疾病，Med_disease
其中 Med.Med_ID = Active_mat_med.Med_ID
和 Active_material.Activemat_ID = Active_mat_med.Activemat_ID
和 Diseases.Disease_ID = Med_disease.Disease_ID
和 Med.Med_ID = Med_disease.Med_ID
和 Med.Med_ID=@P_MedID
订购 依据英文名称 ASC 
- 按activemat_name，Disease_name 分组
联盟 全部
选择 不同 > NULL ，疾病名称
来自 Med，Active_material，Active_mat_med，疾病，Med_disease
其中 Med.Med_ID = Active_mat_med.Med_ID
和 Active_material.Activemat_ID = Active_mat_med.Activemat_ID
和 Diseases.Disease_ID = Med_disease.Disease_ID
和 Med.Med_ID = Med_disease.Med_ID
和 Med.Med_ID=@P_MedID
订单 依据 Disease_name  ASC 
- 按activemat_name，Disease_name 分组
结束 

这会引发错误<消息156，级别15，状态1，过程SP_Med_SelectAllDisease_ActiveMatByMedID，第14行 关键字'union'附近的语法不正确.

我如何将Union与Order by一起使用?
在高级中感谢...

解决方案

只需删除内部选择的顺序(Order by EnglishName ASC).否则将根本无法订购结果.否则您会期望什么?将两个有序结果集的乘积粘贴在一起?

首先进行完整选择，然后订购.如果EnglishName不包含空值(另一个对DiseaseName也是如此)，则没有关系，因为在订购它们时，空值将保持在一起.然后，您可以这样做:

  ORDER   BY 英文名称，疾病名称

另一个选择是将1 as union_id添加到第一个选择中，然后将2 as union_id添加到第二个选择和第一个选择上.

顺便说一句，您是否注意到DiseaseName和Disease_name名称不匹配?

祝你好运！

看看通过ORDER BY获得UNION的最佳示例；
http://www.techonthenet.com/sql/union.php [ http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90153 [ ^ ]
http://www.java2s.com/Code/Oracle/Result-Set/AUNIONquerythatusesanORDERBYclause.htm [ ^ ]

在union中，您仅定义一个顺序，该顺序将应用于整个结果.同样，您可以按顺序查看列的位置.另外要注意的是，您不必为两个查询都定义"distinct".如果不全部使用联合，则仅返回不同的记录.

因此，另一种选择可能是:

 选择英文名称， NULL   AS  DiseaseName
来自 Med，Active_material，Active_mat_med，疾病，Med_disease
其中 Med.Med_ID = Active_mat_med.Med_ID
和 Active_material.Activemat_ID = Active_mat_med.Activemat_ID
和 Diseases.Disease_ID = Med_disease.Disease_ID
和 Med.Med_ID = Med_disease.Med_ID
和 Med.Med_ID=@P_MedID
联盟 全部
选择  NULL ，疾病名称
来自 Med，Active_material，Active_mat_med，疾病，Med_disease
其中 Med.Med_ID = Active_mat_med.Med_ID
和 Active_material.Activemat_ID = Active_mat_med.Activemat_ID
和 Diseases.Disease_ID = Med_disease.Disease_ID
和 Med.Med_ID = Med_disease.Med_ID
和 Med.Med_ID=@P_MedID
订单 依据  1  

ALTER proc [dbo].[SP_Med_SelectAllDisease_ActiveMatByMedID]
@P_MedID int
as 
begin
select Distinct EnglishName,NULL AS DiseaseName
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
Order by EnglishName ASC
--group by activemat_name,Disease_name
union all
select Distinct NULL , Disease_name 
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID 
Order by Disease_name ASC
--group by activemat_name,Disease_name
end

that is throw the error

Msg 156, Level 15, State 1, Procedure SP_Med_SelectAllDisease_ActiveMatByMedID, Line 14
Incorrect syntax near the keyword 'union'.

how can i use Union with Order by ?
thanks in advanced...

解决方案

Just remove the order by of the inner selection (Order by EnglishName ASC). The result would otherwise not be ordered at all. What would you otherwise expect as a result? The product of two ordered result sets pasted together?

First do the complete selection and after that you order it. If the EnglishName doesn''t contain null values (and the same goes for DiseaseName in the other) it wouldn''t even matter because the null values will stay together when ordering them. You could then just do:

ORDER BY EnglishName, DiseaseName

Another option could be to add 1 as union_id to the first select and 2 as union_id to the second select and first order on that.

By the way, did you notice that the names DiseaseName and Disease_name mismatch?

Good luck!

Hi,
Have a look to get best examples of UNION with ORDER BY;
http://www.techonthenet.com/sql/union.php[^]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90153[^]
http://www.java2s.com/Code/Oracle/Result-Set/AUNIONquerythatusesanORDERBYclause.htm[^]

In union you define only one order by which is applied for the whole result. Also in order by you can refer to columns by their position. And another note, you don''t have to define distinct for both queries. If you use union without all, only distinct records are returned.

So an alternative could be like:

select  EnglishName,NULL AS DiseaseName
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
union all
select NULL , Disease_name
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
Order by 1

这篇关于通过联合使用订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！