有一个table叫survey_responders, username这一列数据是null的,要把username那一列数据全部补齐,要求格式是first_name的第一个字母加上last_name
比如:first_name: Vanna last_name: Waters
username就要写成VWaters我写的是:
SELECT first_name, last_name, SUBSTRING((first_name, 1, 1), last_name) as username
FROM survey_responders;
但是运行后错误Error Code: 1241. Operand should contain 1 column(s)谢啦~~
比如:first_name: Vanna last_name: Waters
username就要写成VWaters我写的是:
SELECT first_name, last_name, SUBSTRING((first_name, 1, 1), last_name) as username
FROM survey_responders;
但是运行后错误Error Code: 1241. Operand should contain 1 column(s)谢啦~~
case when username is null then left(first_name,1)+last_name
else username end as username
FROM survey_responders;
SELECT first_name, last_name, case when username is null then (SUBSTRING(first_name, 1, 1)+ last_name) as username
FROM survey_responders;
这个可以运行,但是username的值全变成0了
FROM survey_responders;要注意函数的使用,别把最后的括号放大范围。
还是不对,提示需要end,但是加了end以后username变成0了都
SELECT first_name, last_name,
isnull(SUBSTRING(first_name,1,1),'')+last_name as username
FROM survey_responders--2SELECT first_name, last_name,
isnull(left(first_name,1),'')+last_name as username
FROM survey_responders
得到的username那一列全是0呢?
这两个都有问题
Error Code: 1582. Incorrect parameter count in the call to native function 'isnull'
是MySQL Workbench, 0的那个没错误提示,只是在table里面把所有username的null变成了0.
(case when SUBSTRING(first_name,1,1) is null then last_name
else SUBSTRING(first_name,1,1) + last_name end)as username
FROM survey_responders;