SQL UNION和ORDER BY

Question

我有一个令人讨厌的SQL语句，看起来很简单，但它看起来不可思议。 我想让sql返回一个结果集和userdata，这样如果用户emailaddress在公司表中，那么某个用户是结果集中的第一行。

我有这样的SQL返回我想要什么，但我认为它看起来可怕：

select 1 as o, * 
from Users u 
where companyid = 1 
and email = (select email from companies where id=1) 
union 
select 2 as o, * 
from Users u 
where companyid = 1 
and email <> (select email from companies where id=1) 
order by o 

顺便说一下，从用户表中EMAILADDRESS可以在许多企业因此不能是一个连接上在EMAILADDRESS :-(

你有任何想法如何提高该语句使用Microsoft SQL Server 2000中

编辑

林： 使用这一个：

select *, case when u.email=(select email from companies where Id=1) then 1 else 2 end AS SortMeFirst 
from Users u 
where u.companyId=1 
order by SortMeFirst 

它的方式比我的更优雅。感谢理查德L！

Answer 1

你可以做s omething这样的.. RDBMS是否使用的是

 select CASE 
       WHEN exists (select email from companies c where c.Id = u.ID and c.Email = u.Email) THEN 1 
       ELSE 2 END as SortMeFirst, * 
    From Users u 
    where companyId = 1 
    order by SortMeFirst 

Answer 2

将这项工作？：

select c.email, * 
from Users u 
    LEFT JOIN companies c on u.email = c.email 
where companyid = 1 
order by c.email desc 
-- order by case when c.email is null then 0 else 1 end 

Answer 3

我不知道这是更好的，但它是一种替代方法

select *, (select count(*) from companies where email = u.email) as o 
from users u 
order by o desc 

编辑：是否有可能跨越匹配不同的公司多封电子邮件，你只对给定的公司感兴趣，这变成

select *, 
(select count(*) from companies c where c.email = u.email and c.id = 1) as o 
from users u 
where companyid = 1 
order by o desc