mysql - SQL 查询在 SQLite 中有效，但在 MySQL 中无效

Question

我有以下查询在 SQLite 中有效但在 MySQL 中无效

select t.range as [range], count(*) as frequency                        
  from (select case
                 when x.sum between 0.5 and 100 then '0.5 - 100'                
                 when x.sum between 100.5 and 1000 then '100.5 - 1000'        
                 when x.sum between 1000.5 and 2000 then '1000.5 - 2000'
               end as range                                                  
        from (select l.id, sum(i.price) sum                                     
                from lists l
                  join items i                                                  
                    on i.list_id = l.id                                         
                  join line_items li                                            
                    on li.item_id = i.id and li.reversal_id is null             
              group by l.id) x ) t                                             
group by t.range;

运行MySQL出现如下错误

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '[range], count(*) as frequency
from (select case
               when x.sum b' at line 1

Answer 1

这很容易修复。 MySQL 不将方括号识别为转义字符。您可以使用反引号或双引号:

select t.`range` as `range`, count(*) as frequency  

range 是 reserved word in MySQL因此您需要在整个查询过程中对标识符进行转义。

或者只是使用另一个不需要转义的名称，例如sum_range。

您可以将查询简化为:

select (case when x.sum between 0.5 and 100 then '0.5 - 100'                
             when x.sum between 100.5 and 1000 then '100.5 - 1000'        
             when x.sum between 1000.5 and 2000 then '1000.5 - 2000'
        end) as sum_range,
       count(*) as frequency                                                 
from (select l.id, sum(i.price) sum                                     
      from lists l join
           items i                                                  
           on i.list_id = l.id join                                        
           line_items li                                            
           on li.item_id = i.id and li.reversal_id is null             
      group by l.id
     ) x
group by sum_range;