时间戳

首页 > 解决方案 > COALESCE 没有为 Null 和 0 值返回额外的行

php - COALESCE 没有为 Null 和 0 值返回额外的行

问题描述

目前我有 2 个表，第一个表显示状态计数，refno。和agent_id（负责人）和第二个表有一个id和agent_name。因此，要在 refno 旁边引用特定代理。在表 1 中，您可以通过代理表的 id 来引用它。

dbfiddle：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8b92273ef2bb807e3a23e4b8a2ce6d6b

现在我发现我的一些列表的 agent_id 为 0 和 null，在我的代理表中没有引用。所以在这里我使用 COALESCE 添加一个名为 Unassigned 的额外行，并在该列中插入所有 agent_id 为 0 或 null 的变量。我在我的 codeigniter 模型中尝试过同样的方法：

function get_totalagentstatus(){
$this->db->select("SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft,
SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish,
SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish,
SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action,
SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted,
SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold,
SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, COALESCE(c.display_name,'Unassigned'),
SUM(t.status = 'D') +SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') +
        SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, t.agent_id, c.display_name");
$this->db->from('crm_listings t');
$this->db->join('crm_clients_users c','t.agent_id = c.id');
$this->db->where('archive="N"');
$this->db->group_by('COALESCE(c.display_name,"Unassigned")');
$results = $this->db->get();
  return $results;
}

控制器类：

$content['total_agent_status'] = $this->leads_model->get_totalagentstatus()->result();

查看类：

<?php
            foreach($total_agent_status as $row ){
              $draft = $row->draft ? $row->draft : 0;
              $unpublish = $row->unpublish ? $row->unpublish : 0;
              $publish = $row->publish ? $row->publish : 0;
              $action = $row->action ? $row->action : 0;
              $unlisted = $row->unlisted ? $row->unlisted : 0;
              $sold = $row->sold ? $row->sold : 0;
              $let = $row->let ? $row->let : 0; 
              $total = $row->total ? $row->total : 0;                           
          ?>
          <tr>
                <td><?= $row->display_name ?></td>
                <td><?= $draft ?></td>
                <td><?= $unpublish ?></td>
                <td><?= $publish ?></td>
                <td><?= $action ?></td>
                <td><?= $unlisted ?></td>
                <td><?= $sold ?></td>
                <td><?= $let ?></td>
                <td><?= $total ?></td>
          </tr>

我已经完成了 $this->db->last_query 并得到了以下查询：

SELECT SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft, 
SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish, 
SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish, 
SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action, 
SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted, 
SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold, 
SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, 
COALESCE(c.display_name, 'Unassigned'), SUM(t.status = 'D') 
+SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') 
+ SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, 
`t`.`agent_id`, `c`.`display_name` FROM `crm_listings` `t` 
JOIN `crm_clients_users` `c` ON `t`.`agent_id` = `c`.`id` 
WHERE `archive` = "N" GROUP BY COALESCE(c.display_name, "Unassigned")

现在这将返回除我想要的未分配行之外的所有内容。我还在我的 phpmyadmin 中输入了这个以查看结果，它也没有在那里返回它，而是显示带有这些标题的输出，并且这里的任何条目中都没有 Unassigned：

在此处输入图像描述

标签： phpmysqlcodeigniter

解决方案

如果您希望在结果中没有匹配in的行，则需要LEFT加入listingsto 。 此外，您必须分组以涵盖和in的两种情况：agentslistingsidagents
COALESCE(t.agent_id, 0)0nullagent_id

SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       SUM(CASE WHEN t.status IN ('D', 'N', 'Y', 'U', 'L', 'S', 'T') THEN 1 ELSE 0 END) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

我ELSE 0在所有CASE表达式中添加了一个部分，以便您0在结果中获得 s 而不是s，并使用 operatorNULL将表达式更改为列的仅 1 SUM ，但是如果 'D'、'N'、'Y'、'U ', 'L', 'S' 和 'T' 是then 唯一可能的值，你可以使用：totalINstatusCOUNT(*)

SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       COUNT(*) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

请参阅演示。

推荐阅读