在mysql中用零替换空值

evrscar2 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(372)

我知道以前有人问过类似的问题，我检查了大部分问题。但我不能让这个查询工作。我有几个空值的列，我想在html表中将它们显示为零。但我不能得到我想要的结果。
我的问题是：

$query = "SELECT Id, IFNULL(D0c,0), IFNULL(D1c,0), IFNULL(D2c,0), IFNULL(D3c,0) AS D0c, D1c, D2c, D3c FROM Findata Order by Id Desc Limit 100";

运行查询时，会得到以下结果：

['889', , 1, 4, ],['888', , 1, 2, ],['887', 1, 1, 3, 1],['886', 2, 2, 1, 2],['885', , 1, 2, ], etc

将显示仍然为空的结果，而不是零。我需要一点帮助。谢谢。

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 06, 2018 at 11:35 PM
-- Server version: 5.7.14
-- PHP Version: 5.6.25

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `Nexel`
--

-- --------------------------------------------------------

--
-- Table structure for table `temptable`
--

CREATE TABLE `temptable` (
  `D0c` varchar(5) DEFAULT NULL,
  `D1c` varchar(5) DEFAULT NULL,
  `D2c` varchar(5) DEFAULT NULL,
  `D3c` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `temptable`
--

INSERT INTO `temptable` (`D0c`, `D1c`, `D2c`, `D3c`) VALUES
('1', '2', '1', '1'),
('2', '1', '', '2'),
('2', '2', '1', ''),
('2', '2', '1', ''),
('1', '', '4', ''),
('', '2', '1', '2'),
('3', '', '1', '1'),
('', '2', '2', '1'),
('3', '1', '1', ''),
('2', '1', '', '2'),
('2', '2', '1', ''),
('1', '3', '1', ''),
('2', '', '2', '1'),
('', '2', '3', ''),
('1', '3', '1', ''),
('1', '2', '1', '1'),
('1', '3', '1', ''),
('2', '1', '2', ''),
('', '1', '2', '2'),
('1', '1', '2', '1'),
('1', '2', '1', '1'),
('3', '1', '', '1'),
('2', '2', '', '1'),
('2', '2', '1', ''),
('1', '', '4', ''),
('1', '4', '', ''),
('', '2', '', '3'),
('1', '', '3', '1'),
('2', '1', '2', ''),
('1', '2', '1', '1'),
('', '3', '1', '1'),
('2', '2', '1', ''),
('2', '1', '1', '1'),
('2', '1', '2', ''),
('2', '1', '2', ''),
('', '2', '1', '2'),
('', '1', '3', '1'),
('2', '1', '2', ''),
('', '1', '4', '');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

mysql

来源：https://stackoverflow.com/questions/51218580/replacing-null-values-with-zeros-in-mysql

3条答案

按热度按时间

nhaq1z211#

而不是

IFNULL(D0c,0)

尝试

REPLACE(Doc,NULL,0)

赞(0）回复(0）举报 2021-06-24

jw5wzhpr2#

如果是空字符串：

$query = "SELECT  Id, CASE when D0c like '' then '0' else D0c end as D0c, CASE when D1c like '' then '0' else D1c end as D1c, CASE when D2c like '' then '0' else D2c end as D2c, case when D3c like '' then '0' else D3c end as D3c FROM Mytable Order by Id Desc Limit 100" ;

赞(0）回复(0）举报 2021-06-24

zdwk9cvp3#

请参阅下面的更新
请尝试coalesce（），而不是ifnull：

SELECT 
    Id, 
    COALESCE(D0c,0) AS `FixedD0c`,
    COALESCE(D1c,0) AS `FixedD1c`,
    COALESCE(D2c,0) AS `FixedD2c`,
    COALESCE(D3c,0) AS `FixedD3c`,
    D0c,
    D1c, 
    D2c, 
    D3c 
FROM Findata 
Order by Id Desc 
Limit 100;

注意：顺便说一句，在您的问题中显示的查询中，您似乎试图一次分配列别名'，但您只是重命名了第4列的结果，然后再次包含第2-4列，没有ifnulls。查询的结果不会如问题中所示，但在输出中会有7列。
编辑：
sql小提琴
mysql 5.6架构设置：

CREATE TABLE `Findata` (
  `D0c` varchar(5) DEFAULT NULL,
  `D1c` varchar(5) DEFAULT NULL,
  `D2c` varchar(5) DEFAULT NULL,
  `D3c` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `temptable`
--

INSERT INTO `Findata` (`D0c`, `D1c`, `D2c`, `D3c`) VALUES
('1', '2', '1', '1'),
('2', '1', NULL, '2'),
('2', '2', '1', NULL),
('2', '2', '1', NULL),
('1', NULL, '4', NULL),
(NULL, '2', '1', '2'),
('3', NULL, '1', '1'),
(NULL, '2', '2', '1'),
('3', '1', '1', NULL),
('2', '1', NULL, '2'),
('2', '2', '1', NULL),
('1', '3', '1', NULL),
('2', NULL, '2', '1'),
(NULL, '2', '3', NULL),
('1', '3', '1', NULL),
('1', '2', '1', '1'),
('1', '3', '1', NULL),
('2', '1', '2', NULL),
(NULL, '1', '2', '2'),
('1', '1', '2', '1'),
('1', '2', '1', '1'),
('3', '1', NULL, '1'),
('2', '2', NULL, '1'),
('2', '2', '1', NULL),
('1', NULL, '4', NULL),
('1', '4', NULL, NULL),
(NULL, '2', NULL, '3'),
('1', NULL, '3', '1'),
('2', '1', '2', NULL),
('1', '2', '1', '1'),
(NULL, '3', '1', '1'),
('2', '2', '1', NULL),
('2', '1', '1', '1'),
('2', '1', '2', NULL),
('2', '1', '2', NULL),
(NULL, '2', '1', '2'),
(NULL, '1', '3', '1'),
('2', '1', '2', NULL),
(NULL, '1', '4', NULL);

查询1：

SELECT 
    COALESCE(D0c,0) AS `FixedD0c`,
    COALESCE(D1c,0) AS `FixedD1c`,
    COALESCE(D2c,0) AS `FixedD2c`,
    COALESCE(D3c,0) AS `FixedD3c`,
    D0c,
    D1c, 
    D2c, 
    D3c 
FROM Findata 
Limit 100

结果：

| FixedD0c | FixedD1c | FixedD2c | FixedD3c |    D0c |    D1c |    D2c |    D3c |
|----------|----------|----------|----------|--------|--------|--------|--------|
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        2 |        1 |        0 |        2 |      2 |      1 | (null) |      2 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        0 |        4 |        0 |      1 | (null) |      4 | (null) |
|        0 |        2 |        1 |        2 | (null) |      2 |      1 |      2 |
|        3 |        0 |        1 |        1 |      3 | (null) |      1 |      1 |
|        0 |        2 |        2 |        1 | (null) |      2 |      2 |      1 |
|        3 |        1 |        1 |        0 |      3 |      1 |      1 | (null) |
|        2 |        1 |        0 |        2 |      2 |      1 | (null) |      2 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        2 |        0 |        2 |        1 |      2 | (null) |      2 |      1 |
|        0 |        2 |        3 |        0 | (null) |      2 |      3 | (null) |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        1 |        2 |        2 | (null) |      1 |      2 |      2 |
|        1 |        1 |        2 |        1 |      1 |      1 |      2 |      1 |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        3 |        1 |        0 |        1 |      3 |      1 | (null) |      1 |
|        2 |        2 |        0 |        1 |      2 |      2 | (null) |      1 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        0 |        4 |        0 |      1 | (null) |      4 | (null) |
|        1 |        4 |        0 |        0 |      1 |      4 | (null) | (null) |
|        0 |        2 |        0 |        3 | (null) |      2 | (null) |      3 |
|        1 |        0 |        3 |        1 |      1 | (null) |      3 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        0 |        3 |        1 |        1 | (null) |      3 |      1 |      1 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        2 |        1 |        1 |        1 |      2 |      1 |      1 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        2 |        1 |        2 | (null) |      2 |      1 |      2 |
|        0 |        1 |        3 |        1 | (null) |      1 |      3 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        1 |        4 |        0 | (null) |      1 |      4 | (null) |

赞(0）回复(0）举报 2021-06-24

我来回答

在mysql中用零替换空值

3条答案

相关问题

热门标签

最新问答