SQL 条件排序依据

问题描述

我正在对两个表进行连接.一个是用户表，另一个是高级用户列表.我需要让高级会员首先出现在我的查询中.但是，仅仅因为他们在高级用户表中并不意味着他们仍然是高级会员 - 还有一个 IsActive 字段需要检查.

I am doing a join on two tables. One is a user's table and the other a list of premium users. I need to have the premium members show up first in my query. However, just because they are in the premium user table doesn't mean they are still a premium member - there is an IsActive field that also needs to be checked.

所以基本上我需要按以下顺序返回结果:

So basically I need to return the results in the following order:

• 活跃的高级用户
• 普通和非活跃高级用户

现在我有它如下:

SELECT Users.MemberId, PremiumUsers.IsActive FROM Users
LEFT JOIN PremiumUsers ON PremiumUsers.UserId = Users.Id
ORDER BY PremiumUsers.IsActive DESC

问题在于，它将非活跃高级会员置于非高级会员之上.

The problem with this is that it places non-active premium members above non-premium members.

(为此我使用的是 MS SQL Server 2005)

(I'm using MS SQL Server 2005 for this)

推荐答案

ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC

这会将 NULL 与非活动分组.

That will group the NULLs with not-actives.