返回字段的 DISTINCT 第一个字符（MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/837058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning the DISTINCT first character of a field (MySQL)
提问by chadgh
I would like to produce a character list of all of the first letters of column in my database. The SQL below illistrats what I would like to return.
我想生成数据库中所有列的第一个字母的字符列表。下面的 SQL 说明了我想返回的内容。
SELECT DISTINCT first_character(name) FROM mydatabase
Is there a way to do this in MySQL?
有没有办法在 MySQL 中做到这一点？
EDITWhat is the advantage of using SUBSTRING over LEFT and vice versa?
编辑使用 SUBSTRING 优于 LEFT 的优势是什么，反之亦然？
EDITCurrently there are about 1700 records in the table and growing.
编辑目前表中有大约 1700 条记录并且还在增长。
回答by chadgh
Sorry to do this, but I figured out exactly what I needed to do just now.
很抱歉这样做，但我刚刚弄清楚我需要做什么。
SELECT DISTINCT LEFT(name, 1) FROM mydatabase
This returned a list of the first, distinct, single characters that each row in the column started with. I added changed it to the following to get it the list in alpha-numeric order:
这将返回列中每一行开头的第一个不同的单个字符的列表。我添加将其更改为以下内容以按字母数字顺序获取列表：
SELECT DISTINCT LEFT(name, 1) as letter FROM mydatabase ORDER BY letter
Works like a charm.
奇迹般有效。
回答by Andomar
Sounds simple:
听起来很简单：
select distinct substring(field,1,1) as char
from mytable
回答by Quassnoi
For your current table of 1,700rows your solution is OK.
对于您当前的1,700行表，您的解决方案是可以的。
If you will have like 100,000of rows, the DISTINCTmay become inefficient.
如果你有喜欢100,000的行，DISTINCT可能会变得效率低下。
Here's the article in my blog that shows how to do it efficiently:
这是我博客中的文章，展示了如何有效地做到这一点：
This solution employs an index on name. It will jump over the index keys, selecting each first letter at most once.
此解决方案在 上使用索引name。它将跳过索引键，最多选择每个第一个字母一次。
First, you'll need to create a function:
首先，您需要创建一个函数：
CREATE FUNCTION fn_get_next_code(initial INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _next VARCHAR(200);
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT ORD(SUBSTRING(name, 1, 1))
INTO _next
FROM t_names
WHERE name >= CHAR(initial + 1)
ORDER BY
name
LIMIT 1;
RETURN _next;
END
This function, given a code of a starting letter, returns the first starting letter next to the given from your table.
这个函数，给定一个起始字母的代码，返回表中给定值旁边的第一个起始字母。
Second, use this function in a query:
其次，在查询中使用这个函数：
SELECT CHAR(@r) AS starting,
@r := fn_get_next_letter(@r) AS next
FROM (
SELECT @r := ORD(LEFT(MIN(name), 1))
) vars, mytable
WHERE @r IS NOT NULL
On each iteration, session variable @rwill skip to the next starting letter using an index.
在每次迭代中，会话变量@r将使用索引跳到下一个起始字母。
This will be very fast, but it pays for itself only if you have hundreds of thousands of rows.
这将非常快，但只有当您拥有数十万行时，它才能物有所值。
Otherwise just use DISTINCT.
否则只需使用DISTINCT.
