【mysql】case疑惑

来源：互联网发布：淘宝助理类目空白编辑：程序博客网时间：2024/06/06 17:48

创建以下表：

CREATE TABLE `case_test` (  uid int(11),  tid int(11),  status tinyint(3),  PRIMARY KEY (`uid`,tid)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO `case_test` VALUES (1,1,0),(1,2,0),(1,3,0),(1,4,0),(1,5,0),(1,6,0),(1,7,0),(1,8,0),(1,9,0),(1,10,0),(1,11,1),(1,12,1),(1,13,1),(1,14,1),(1,15,1),(1,16,1),(1,17,1),(1,18,1),(1,19,1),(1,20,1);

查询语句

select uid,sum(case status when 1 then 1 else 0 end) friend,sum(case status when status>0 then 1 else 0 end) concern   from case_test group by uid;

结果：

+-----+--------+---------+
| uid | friend | concern |
+-----+--------+---------+
| 1 | 10 | 20 |
+-----+--------+---------+

查询语句：

select uid,sum(case status when 1 then 1 else 0 end) friend,sum(case status when status>=0 then 1 else 0 end) concern   from case_test group by uid;

查询结果：

+-----+--------+---------+
| uid | friend | concern |
+-----+--------+---------+
| 1 | 10 | 10 |
+-----+--------+---------+

为什么case>0 和case>=0的结果是不一样的？

0 0