在SQL中执行ORDER时，空字符串前面会出现空值。

Question

我试图从一个表Local_LsIr_Temp中选择一些数据。我需要这样的数据：

1. dir_email和dir_tele都在场。
2. 至少有dir_email存在(dir_tele可以是null或empty)
3. 至少有dir_tele存在(dir_email可以是null或empty)

SELECT ROW_NUMBER() OVER 
(PARTITION BY inst_iconum ORDER BY 
    CASE WHEN (dir_email = '' OR dir_email IS NULL) THEN 1 ELSE 0 END, dir_email,
    CASE WHEN (dir_tele = ''  OR dir_tele  IS NULL) THEN 1 ELSE 0 END, dir_tele
) rn,
* 
FROM Local_LsIr_Temp

我编写了上面的查询，结果并不如预期的那样。

下面附加的是示例结果，用蓝色标记的行应该在行的上方带有两个空值。

​

​

请查找此问题的SQL。SQL Fiddle

Answer 1

必须首先指定CASE语句。话虽如此，您可以通过重写ORDER子句来达到预期的结果：

ORDER BY CASE 
    WHEN dir_email <> '' AND dir_tele <> '' THEN 1 -- Both dir_email and dir_tele are present
    WHEN dir_email <> ''                    THEN 2 -- At least dir_email is present
    WHEN dir_tele  <> ''                    THEN 3 -- At least dir_tele is present 
    ELSE                                         4
END --, additional columns here

Answer 2

您的问题是，订单列的顺序是错误的-如果dir_email是非空白的，则dir_tele基本上会被忽略。

试试这个：

...
order by 
  case when (dir_email = '' or dir_email is null) then 1 else 0 end,
  case when (dir_tele = '' or dir_tele is null) then 1 else 0 end,
  dir_enail,
  dir_tele