MySQL查询与case语句

问题描述：

我有按日期排序difference.my排序当前工作查询，但我想使用情况下的条件类似MySQL查询与case语句

如果priority<=10 THEN 10将与目前的当务之急

我目前查询添加返回修改的优先级值的默认异常值

我的查询是

SELECT videoid,priority, 
     CASE WHEN priority<=10 THEN (priority+10) 
      WHEN priority>10 and priority<=20 THEN (priority+9) 
      WHEN priority<=30 THEN (priority+8) 
     END AS 'modified_priority', 
     DATEDIFF(CURDATE(), 
     DATE(from_unixtime(submittime))) AS d 
FROM 
    `datatable` 
ORDER BY d

应该不是最后部分优先> = 30？ –

@ user3660286：定义*'默认异常值'*。 –

是否有任何答案可以解决您的问题？ –

答

你的最后情况下，所有匹配。试试这个

SELECT videoid,priority, 
     CASE WHEN priority <=10 THEN (priority+10) 
      WHEN priority>10 and priority <=20 THEN (priority+9) 
      WHEN priority>20 AND priority <=30 THEN (priority+8) 
     END AS `modified_priority`, 
     DATEDIFF(CURDATE(), 
     DATE(from_unixtime(submittime))) AS d 
FROM 
    `datatable` 
ORDER BY d

是的，我认为是一样的。 +1 –

'ELSE'也会这样做 – georstef

我不认为相同http://sqlfiddle.com/#!2/03dfb/1 –

答

按顺序处理案例语句，返回第一个匹配条件。你可以用你的优势，并简化了声明：

SELECT videoid, priority, 
     (CASE WHEN priority <= 10 THEN (priority+10) 
      WHEN priority <= 20 THEN (priority+9) 
      WHEN priority <= 30 THEN (priority+8) 
     END) AS modified_priority, 
     DATEDIFF(CURDATE(), DATE(from_unixtime(submittime))) AS d 
FROM `datatable` 
ORDER BY d;

如果你不想返回NULL优先值大于30，那么你就需要一个ELSE条款。也许：

SELECT videoid, priority, 
     (CASE WHEN priority <= 10 THEN (priority+10) 
      WHEN priority <= 20 THEN (priority+9) 
      WHEN priority <= 30 THEN (priority+8) 
      ELSE priority 
     END) AS modified_priority, 
     DATEDIFF(CURDATE(), DATE(from_unixtime(submittime))) AS d 
FROM `datatable` 
ORDER BY d;

此外，我删除了从modified_priority左右的单引号。您应该只为字符串和日期常量使用单引号。

MySQL查询与case语句

相关推荐