我有一个NAMES
带有列id
和的表格name
。
查询是根据单词结尾数量最少的字母来获取结果。
例如:亨利,哈里,约翰,弗雷德里克是我桌子上的名字。n和k是只有1个单词的字母作为结尾字母。
这是我到目前为止所做的。
select substr(name, -1, 1) as character,
count(*) as wordscount
from names
group by substr(name, -1, 1)
order by count(*);
这使我的结果如下
character wordscount
----------- ------------
n 1
k 1
y 2
我的查询应获取如下结果
character wordscount
----------- ------------
n 1
k 1
我不想使用,rownum = 1
因为可能有多个字母,且字数最少。
select *
from (select substr(name, -1, 1) as character,
count(*) as wordsCount
from names
group by substr(name, -1, 1)
order by count(*))
where rownum = 1;
我不想使用上面的查询,因为这k 1
将从我的结果中省略。
您在寻找吗?
select substr(name, -1, 1) as character, count(*) as wordscount from names
group by substr(name, -1, 1) having count(*)= (
select min(count(*)) from names
group by substr(name, -1, 1)
)
或无需额外的包装器:
select * from (
select substr(name, -1, 1) as character, count(*) as wordscount from names
group by substr(name, -1, 1) ) A where wordscount = (
select min(count(*)) from names
group by substr(name, -1, 1)
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句