MySQL SELECT IFNULL 然后将其与其他列进行数值比较-解网

问：

我尝试了多种方法在选择时将 NULL 转换为数字 0：

SELECT
  ss.id AS staff_id,
  u.realname AS realname,
  ss.amount AS salary_amount,
  IF(s.paid_amount IS NOT NULL, s.paid_amount,0.00) AS paid_amount,
  (ss.amount-s.paid_amount)
FROM f_salary_staff ss
  JOIN user u ON (u.id=ss.user_id)
  LEFT JOIN (
    SELECT staff_id,
      month_year,
      SUM(amount) AS paid_amount
    FROM f_salary
    WHERE month_year='2020-02-29'
    GROUP BY staff_id,month_year
  ) s ON (ss.id=s.staff_id)

我使用了 IFNULL，但这根本没有将 0 变成任何数字，因此选择甚至没有列出具有 NULL 值的那些。有没有办法在选择时将 NULL 值转换为数字并以数字方式将其与其他列进行比较？

顺便说一句..下面是两个表的 CREATE TABLE 语句。

f_salary_staff

CREATE TABLE `f_salary_staff` (  `id` int(11) NOT NULL AUTO_INCREMENT, `store_id` int(11) NOT NULL,  `user_id` int(11) NOT NULL,  `date_enter` date DEFAULT NULL,  `amount` decimal(12,2) DEFAULT NULL,  `updated` datetime DEFAULT NULL,  `updated_by` int(11) DEFAULT NULL,  `created` datetime DEFAULT NULL,  `created_by` int(11) DEFAULT NULL,  `last_activated` datetime DEFAULT NULL,  `last_inactivated` datetime DEFAULT NULL,  `status` varchar(16) DEFAULT NULL,  PRIMARY KEY (`id`), KEY `store_id` (`store_id`),  KEY `user_id` (`user_id`),  CONSTRAINT `f_salary_staff_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  CONSTRAINT `f_salary_staff_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

然后f_salary

CREATE TABLE `f_salary` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `staff_id` int(11) NOT NULL,  `amount` decimal(12,2) DEFAULT NULL,  `note` tinytext,  `ref_date` date DEFAULT NULL,  `month_year` date DEFAULT NULL,  `created` datetime DEFAULT NULL,  `created_by` int(11) DEFAULT NULL,  `updated` datetime DEFAULT NULL,  `updated_by` int(11) DEFAULT NULL,  `approved` datetime DEFAULT NULL,  `approved_by` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `staff_id` (`staff_id`),  CONSTRAINT `f_salary_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `f_salary_staff` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

希望这对那些需要了解表格结构的人有所帮助

mysql null 比较数值 ifnull

我不明白你的桌子。我的假设：有一个表，它的主键是 .有一个表，它的 pk 是 .但是：这两者有什么关系？该表是否具有使每个用户都属于一个员工？该表有一个而不是一个，所以工资是在员工身上而不是在用户身上？该员工中的每个用户都获得这笔薪水，还是他们自己分配？然后该表有一个，顾名思义，它应该有一个......useruser_idstaffstaff_iduserstaff_idf_salarystaff_iduser_idf_salary_staffuser_idstaff_id

0赞 Thorsten Kettner 2/17/2020

...请解释一下您的表格。它们代表什么？他们的钥匙是什么？

0赞 Jeebsion 2/17/2020

嘿，你好。。我已经把create table语句作为参考了。希望能有所帮助

答：

0赞 forpas 2/17/2020 #1

该选择甚至没有列出那些具有 NULL 值的

因为你有一个被子句变成一个。
将条件移到子句中。
此外，在左联接查询中，选择该列，尽管它未按应有的方式包含在子句中。
更改为：LEFT JOININNER JOINWHEREONstaff_idGROUP BY

SELECT ss.id AS staff_id, u.realname AS realname, ss.amount AS salary_amount, 
COALESCE(s.paid_amount, 0) AS paid_amount,
ss.amount - COALESCE(s.paid_amount, 0)
FROM f_salary_staff ss 
INNER JOIN user u ON (u.id=ss.user_id) 
LEFT JOIN (
  SELECT staff_id, ref_date, SUM(amount) AS paid_amount 
  FROM f_salary 
  WHERE month_year='2020-02-29' 
  GROUP BY staff_id, ref_date
) s ON (ss.id=s.staff_id) AND s.paid_amount > ss.amount

使用 COALESCE 不会更改返回的行数，它只是将 NULL 更改为 0。检查您的要求和代码。我回答中的代码是关于您发布的问题：（1）*为什么你没有得到带有 NULL 的行*，答案是因为你误用了 LEFT JOIN，以及（2）如何使 NULL 显示为 0，答案是 COALESCE。

0赞 Gosfly 2/17/2020 #2

如果我很好地理解您的需求，以下查询应该有效：

SELECT t.*
FROM (
  SELECT 
    ss.id AS staff_id, 
    u.realname AS realname, 
    ss.amount AS salary_amount, 
    COALESCE(s.paid_amount, 0) AS paid_amount 
  FROM f_salary_staff ss 
  INNER JOIN user u ON (u.id=ss.user_id) 
  LEFT JOIN (
    SELECT staff_id, ref_date, SUM(amount) AS paid_amount 
    FROM f_salary 
    WHERE month_year='2020-02-29' 
    GROUP BY staff_id, ref_date
  ) s ON (ss.id=s.staff_id) 
) t
WHERE paid_amount > salary_amount

SELECT
  ss.id AS staff_id,
  u.realname AS realname,
  ss.amount AS salary_amount,
  IFNULL(s.paid_amount,0.00) AS paid_amount,
  IF(s.paid_amount IS NULL,ss.amount,ss.amount-s.paid_amount) AS unpaid_amount
FROM f_salary_staff ss
  JOIN user u ON (u.id=ss.user_id)
  LEFT JOIN (
    SELECT staff_id,
      month_year,
      SUM(amount) AS paid_amount
    FROM f_salary
    WHERE month_year='2020-02-29'
    GROUP BY staff_id,month_year
  ) s ON (ss.id=s.staff_id)  
WHERE ss.amount != s.paid_amount OR s.paid_amount IS NULL

谢谢大家！

IFNULL(s.paid_amount,0.00)和是一样的所以有什么区别？此外，显然 COALESCE 或 IFNULL 甚至 IF 都无法将 NULL 更改为 NULL 以外的任何其他内容，或者不是 NOT NULL 是完全错误的。COALESCE(s.paid_amount,0.00)IF(s.paid_amount IS NULL,ss.amount,ss.amount-s.paid_amount)ss.amount - COALESCE(s.paid_amount, 0)

上一个：比较两个表不同数量的mysql查询

下一个：如何在python mysql中将索引值与数据库值进行比较？

MySQL SELECT IFNULL 然后将其与其他列进行数值比较

MySQL SELECT IFNULL then compare it numerically with other column

评论

评论

评论

评论