本文介绍了SQL UNION和ORDER BY的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个令人讨厌的SQL语句,它看起来很简单,但是看起来很糟糕.我希望sql返回带有排序的userdata的结果集,以便如果该用户的电子邮件地址在companies表中,则该用户是结果集中的第一行.

I have an annoying SQL statement that seem simple but it looks awfull.I want the sql to return a resultset with userdata ordered so that a certain user is the first row in the resultset if that users emailaddress is in the companies table.

我有此SQL返回我想要的内容,但我认为它看起来很糟糕:

I have this SQL that returns what i want but i think it looks awful:

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

顺便说一句,用户表中的电子邮件地址可能存在于许多公司中,因此该电子邮件地址上不能存在联接:-(

And by the way, the emailaddress from the user table can be in many companies so there cant be a join on the emailaddress :-(

您是否有任何想法可以改善这一说法?

Do you have any ideas how to improve that statement?

我正在使用Microsoft SQL Server 2000.

Im using 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

它的方式比我的更优雅.谢谢Richard L!

Its way more elegant than mine. Thanks Richard L!

推荐答案

您可以执行以下操作.

        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

这篇关于SQL UNION和ORDER BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 07:43