如何解决含CASE条件的SQL查询返回NULL值的问题

问题 #

编程经验有限，首次提问。需要为Datatable编写带CASE条件的SQL查询，按**订单完成天数（≤3天、>5天、3-5天）和业务线（lineanegocio）**分组统计订单数量并映射到对应列，通过AJAX加载到Datatable。但查询返回NULL值，导致表格中如‘linea negocio 11’等行无法显示预期百分比数据（比如50%、35%、15%），尝试用COALESCE处理CASE结果无效。

现有代码 #

AJAX中的SQL查询代码

$table = <<<EOT
 (
    SELECT ce.lineanegocio AS linea, COUNT(c.entity) AS qty, 
    DATE_FORMAT(c.date_creation,'%Y-%c') AS date_creation1,
    DATE_FORMAT(pe.date_creation,'%Y-%c') AS date_creation2,
    CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 3 AND ce.lineanegocio = '11' THEN 1
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 3 AND ce.lineanegocio = '15' THEN 2
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 3 AND ce.lineanegocio = '12' THEN 3
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 3 AND ce.lineanegocio = '20' THEN 4
    ELSE 5 END AS date_diff1,
    CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 5 AND ce.lineanegocio = '11' THEN 6
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 5 AND ce.lineanegocio = '15' THEN 7
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 5 AND ce.lineanegocio = '12' THEN 8
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 5 AND ce.lineanegocio = '20' THEN 9
    ELSE 10 END AS date_diff2,
    CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 3 AND TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 5 AND ce.lineanegocio = '11' THEN 11
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 3 AND TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 5 AND ce.lineanegocio = '15' THEN 12
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 3 AND TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 5 AND ce.lineanegocio = '12' THEN 13
    WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 3 AND TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 5 AND ce.lineanegocio = '20' THEN 14
    ELSE 15 END AS date_diff3
    FROM llx_commande c
    INNER JOIN llx_commande_extrafields ce ON c.rowid = ce.fk_object
    INNER JOIN llx_pedidosestado_fechaestado pe ON c.rowid = pe.id_pedido
    WHERE (c.fk_statut = '9' OR c.fk_statut = '11') AND (pe.estado = '11' OR pe.estado = '9')
    GROUP BY linea
    ORDER BY qty DESC
 ) temp
EOT;

Datatable前端代码

<script type="text/javascript">
     $(document).ready(function() {
 var table = $('#lineapedidos').DataTable( {
       "processing": true,
       "stateSave": true,
       dom: '',
       "bInfo": false,
       "serverSide": true,
       "ajax": "getPedidos2.php",
       "columns": [
     { "data": 0 },
     { "data": 1 },
     { "data": 2 },
     { "data": 3 },
     { "data": 4 },
       ],
       columnDefs: [
       {
         targets: [2, 3, 4],
         render: function ( data, type, row, meta ) {
           return type === 'display' ?
             data + '%' :
             data;
           }
         }
       ]
   } );

 } );

解决方案 #

核心问题分析 #

当前SQL逻辑完全错误：

1. CASE语句仅给单行标记数字，未做分组统计，GROUP BY后这些列只会取分组内某一行的值，大概率返回NULL或错误结果
2. 需求是按业务线分组后，统计各天数区间订单数占该业务线总订单数的百分比，而非标记单行

修改后的SQL查询 #

$table = <<<EOT
 (
    SELECT 
        ce.lineanegocio AS linea,
        COUNT(c.entity) AS total_qty,
        -- 计算≤3天订单占比（百分比，保留两位小数）
        ROUND(
            SUM(CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <= 3 THEN 1 ELSE 0 END) 
            / COUNT(c.entity) * 100, 2
        ) AS pct_3days_or_less,
        -- 计算>5天订单占比
        ROUND(
            SUM(CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 5 THEN 1 ELSE 0 END) 
            / COUNT(c.entity) * 100, 2
        ) AS pct_over_5days,
        -- 计算3-5天订单占比
        ROUND(
            SUM(CASE WHEN TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) > 3 AND TIMESTAMPDIFF(DAY, c.date_creation, pe.date_creation) <=5 THEN 1 ELSE 0 END) 
            / COUNT(c.entity) * 100, 2
        ) AS pct_3_to_5days
    FROM llx_commande c
    INNER JOIN llx_commande_extrafields ce ON c.rowid = ce.fk_object
    INNER JOIN llx_pedidosestado_fechaestado pe ON c.rowid = pe.id_pedido
    WHERE (c.fk_statut IN ('9','11')) AND (pe.estado IN ('9','11'))
    GROUP BY ce.lineanegocio
    ORDER BY total_qty DESC
 ) temp
EOT;

对应修改Datatable前端代码 #

调整列映射，匹配新查询字段，同时处理NULL值：

<script type="text/javascript">
     $(document).ready(function() {
 var table = $('#lineapedidos').DataTable( {
       "processing": true,
       "stateSave": true,
       dom: '',
       "bInfo": false,
       "serverSide": true,
       "ajax": "getPedidos2.php",
       "columns": [
           { "data": "linea", "title": "业务线" },
           { "data": "total_qty", "title": "总订单数" },
           { "data": "pct_3days_or_less", "title": "≤3天占比" },
           { "data": "pct_over_5days", "title": ">5天占比" },
           { "data": "pct_3_to_5days", "title": "3-5天占比" }
       ],
       columnDefs: [
           {
               targets: [2, 3, 4],
               render: function ( data, type, row, meta ) {
                   // 处理NULL值，显示0%
                   data = data || 0;
                   return type === 'display' ? data + '%' : data;
               }
           }
       ]
   } );

 } );

额外说明 #

1. 用SUM(CASE...)统计各区间订单数，除以总订单数得到百分比，ROUND保留两位小数
2. 前端render函数增加data = data || 0，避免某业务线无对应区间订单时显示NULL%
3. WHERE条件用IN替代OR，语法更简洁
4. GROUP BY明确指定字段，避免隐式分组导致的异常

内容的提问来源于stack exchange，提问作者Erick