SQL UNION 和 ORDER BY
我有一个烦人的 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
你可以做这样的事情..
You could do something like this..
这会起作用吗?:
will this work?:
我不确定这更好,但这是一种替代方法
编辑:如果不同公司之间有许多匹配的电子邮件,并且您只对给定公司感兴趣,则这将变为
I am not sure this is better, but it is an alternative approach
Edit: if there can be many emails across different companies that match, and you are only interested in the given company, this becomes