生成以下两个结果集:
1)。查询“职业”中所有姓名的字母顺序列表,紧随其后的是每个职业的第一个字母作为括号(即:括在括号中)。例如:AnActorName(A),ADoctorName(D),AProfessorName(P)和ASingerName(S)。
2)。查询“职业”中每个职业的发生次数。按升序对事件进行排序,并以以下格式输出:
There are total [occupation_count] [occupation]s.
表名称:职业
总计列:两列=“名称”和“职业”,演示表如下所示:
样本输出:
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are total 2 doctors.
There are total 2 singers.
There are total 3 actors.
There are total 3 professors.
我的方法:
(SELECT NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')'
FROM OCCUPATIONS ORDER BY NAME)
UNION ALL
(SELECT COUNT(*) FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY ASEC);
错误:
ERROR 1222 (21000) at line 1:
The used SELECT statements have a different number of columns
谢谢!
您忘记了使用CONCAT
功能粘合所选数据
(
SELECT CONCAT(NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')') as THETEXT, '1' as SELECTNUMBER
FROM OCCUPATIONS
)
UNION ALL
(
SELECT CONCAT('There are total ', COUNT(*),' ', OCCUPATION, (IF (COUNT(*) > 1, 's',''))) as THETEXT, '2' as SELECTNUMBER
FROM OCCUPATIONS GROUP BY OCCUPATION
)
ORDER BY SELECTNUMBER ASC, THETEXT ASC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句