怎样完美解决union的排序和去重问题
前几天有咨询过这个UNION的排序问题：
select id,pro,price From pros Where pro like '%A%B%C%'
Union
select id,pro,price From pros Where pro like '%A%' OR pro like '%B%' OR pro like '%C%' 

要求上一子句总是排在前面，后面子句则用id倒序。

用下面方法可以解决排序：
select id,pro,price,0 as ord From pros Where pro like '%A%B%C%'
Union
(select id,pro,price,1 as ord From pros Where pro like '%A%' OR pro like '%B%' OR pro like '%C%')
order by ord ASC,id DESC

但是问题也来了，由于增加了一个字段ord，整体结果并不会去重，造成至少一条以上重复，即上一子句的结果，也有可能在下一子句出现。

我试了用distinct:

select distinct id,pro,price from
(select id,pro,price,0 as ord From pros Where pro like '%A%B%C%'
Union
(select Top 500 id,pro,price,1 as ord From pros Where pro like '%A%' OR pro like '%B%' OR pro like '%C%')
order by ord ASC,id DESC
) t

现在是去重了，但排序又乱了,如果在子表t后面增加order by ord, id DESC，则ord这个新增的辅助字段必须也加入select 清单中，又去不了重。如下：

select distinct id,pro,price,ord from
(select id,pro,price,0 as ord From pros Where pro like '%A%B%C%'
Union
(select Top 500 id,pro,price,1 as ord From pros Where pro like '%A%' OR pro like '%B%' OR pro like '%C%')
order by ord ASC,id DESC
) t
order by ord, id DESC

唉，超级郁闷。请教各位高手。

------解决方案--------------------

SQL code

select distinct id,pro,price
from (
select id,pro,price,flag=0 From pros Where pro like '%A%B%C%'
Union
select id,pro,price,flag=1 From pros Where pro like '%A%' OR pro like '%B%' OR pro like '%C%' )g
order by flag id

------解决方案--------------------
先嵌套 再排序...

------解决方案--------------------

SQL code
select id,pro,price From pros Where pro like '%A%B%C%'
Union ALL
select id,pro,price From pros Where pro like '%[^A]%[BC]%' OR pro like '%[AC]%[^B]%' OR pro like '%[AB]%[^C]%'