简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
mysql - 这个SQL哪不对
黄舟
黄舟 2017-04-17 15:35:58
0
3
447

1.统计任务提交情况
2.A（id，name）人员表，B（bid，aid， mission）任务提交记录表，aid 对应id，mission为字符串字段，一个人可提交多次任务，示例数据
id name
1 张三
2 李四
3 王五

bid aid mission
1 1 xxxxx
2 1 yyyyy
3 2 zzzz

sql： select A.name,if(mission<>null,1,0) as mnum from A leftjion B on B.aid= A.id orderby id
得到是
name mnum
张三 0
张三 0
李四 0
王五 0

这个if怎么不起作用的？？？？

本来最终想得到如下效果，那么SQL怎么写
name 任务次数
张三 2
李四 1
王五 0

黄舟
黄舟

人生最曼妙的风景，竟是内心的淡定与从容！

reply all(3)
大家讲道理
大家讲道理2017-04-17 15:37:58 3 floor

This sentence was tested successfully

select A.name,sum(if(mission is not null,1,0)) as '任务次数' from A leftjion B on B.aid= A.id  group by A.id order by A.id desc
Like +0
Add Reply
左手右手慢动作
左手右手慢动作2017-04-17 15:37:58 2 floor

SELECT NULL IS NULL, NULL IS NOT NULL, NULL = NULL, NULL <> NULL
You will know why your if does not work if you run them separately.

Like +0
Add Reply
大家讲道理
大家讲道理2017-04-17 15:37:58 1 floor

According to SQL specifications, the result of all operations with NULL is NULL, so special methods need to be used to judge NULL

1. IFNULL function
2. Mission<>null is changed to mession is not null

Like +0
Add Reply
Popular Topics
More>
Popular Articles
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！