SQL UNION 和 ORDER BY

发布于 2024-07-13 00:55:45 字数 753 浏览 9 评论 0原文

我有一个烦人的 SQL 语句,看起来很简单,但看起来很糟糕。 我希望 sql 返回一个结果集,其中用户数据已排序,以便某个用户是结果集中的第一行(如果该用户的电子邮件地址位于公司表中)。

我有这个返回我想要的 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

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

你有什么想法吗改进该语句?

我使用 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!

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.

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?

Im using Microsoft SQL Server 2000.

Edit:
Im using this one:

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

Its way more elegant than mine. Thanks Richard L!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

青朷 2024-07-20 00:55:46

你可以做这样的事情..

        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

You could do something like this..

        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
惯饮孤独 2024-07-20 00:55:46

这会起作用吗?:

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

will this work?:

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
与风相奔跑 2024-07-20 00:55:46

我不确定这更好,但这是一种替代方法

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

I am not sure this is better, but it is an alternative approach

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

Edit: if there can be many emails across different companies that match, and you are only interested in the given company, this becomes

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文