Mysql - 如何搜索每条以字母开头的 26 条记录? [英] Mysql - How to search for 26 records that each begins with the letter of the alphabet?

问题描述

基本上，我正在尝试创建一个查询，该查询可以根据英文字母表的字母(26 个字母)从表中检索 26 个单词.所以苹果、香蕉、椰子……"等等.

Basically I'm trying to create a query that can retrieve 26 words from a table based on the letters of the English alphabet (26 letters). So "Apple, Banana, Coconut..." etc.

我一直在使用like a%"，所以:

I've been using 'like a%', so:

SELECT * from 'word' WHERE word_name like 'a%' limit 1 - 这给了我一个以 'a' 开头的单词.

SELECT * from 'word' WHERE word_name like 'a%' limit 1 - which gives me a word that begins with 'a'.

SELECT * from 'word' WHERE word_name like 'a%' or word_name like 'b%' - 这给了我多个以 'a' 或 'b' 开头的单词.

SELECT * from 'word' WHERE word_name like 'a%' or word_name like 'b%' - which gives me multiple words beginning with 'a' or 'b'.

SELECT * FROMwordWHERE word_name BETWEEN 'a' AND 'e' - 列出所有以 a-e 开头的单词.似乎更强大.从这里开始，我需要知道如何将查询限制为每个字母只查找一个单词，总共查找 26 个单词，我尝试了以下操作:

SELECT * FROMwordWHERE word_name BETWEEN 'a' AND 'e' - list all words that begins with a-e. Seems more powerful. From here, I need to know how to limit the query to finding just one word per letter and 26 words in total, I've tried the following:

SELECT DISTINCT word_name FROM `word` WHERE word_name BETWEEN 'a' AND 'z' limit 26;

SELECT DISTINCT word_name FROM `word` WHERE word_name like [a-z]% limit 26;

我觉得我得到了正确的想法，但这些都不适合我.我熟悉 php，所以我可以走很长的路，为每个字母创建一个查询并将其放入一个数组中，但是否已经有一个 mysql 解决方案?

I feel like I'm getting the right idea but neither of these are working for me. I'm familiar with php so I could go the really long route and create a query for every letter and put that into an array, but is there already a mysql solution for this?

推荐答案

这应该可行

SELECT DISTINCT LEFT(word_name, 1) as letter, word_name
FROM word
 GROUP BY (letter)
 ORDER BY letter

这篇关于Mysql - 如何搜索每条以字母开头的 26 条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！