以a到z排序。

I have one table customers which has one field 'name' with a to z names records.

我有一个表客户，它有一个字段名，有一个to z的名字记录。

I get records from a to z with asc query

我用asc查询从a到z的记录。

SELECT * FROM `customers` ORDER BY name ASC

But how can i get 5 records which starts with all a to z alphabets with only one query?

但是，我如何才能得到5条记录，这些记录从a到z字母，只有一个查询?

Output:

输出:

1. a

   一个

2. a

   一个

3. a

   一个

4. a

   一个

5. a

   一个

6. b

   b

7. b

   b

8. b

   b

9. b

   b

10. b and so on to z. Thanks in advance.

    b等到z。提前谢谢。

2 个解决方案

#1

Try this:

试试这个:

SELECT c.name
FROM (SELECT c.name, IF(@lastLetter=@lastLetter:=LEFT(c.name, 1), @cnt:=@cnt+1, @cnt:=0) letterCnt
      FROM customers c, (SELECT @lastLetter:='', @cnt:=0) A 
      ORDER BY c.name ASC
    ) AS c
WHERE c.letterCnt < 5

#2

When you use SQL you can use functions like Rank, DENSE_RANK and ROW_NUMBER

使用SQL时，可以使用Rank、DENSE_RANK和ROW_NUMBER等函数。

DECLARE @Customer AS TABLE
(
    Id int,
    Name varchar(50)
)

INSERT INTO @Customer VALUES
(1, 'aa'),
(2, 'ab'),
(3, 'ac'),
(4, 'ba'),
(5, 'bb'),
(6, 'bc'),
(7, 'ca'),
(8, 'cb'),
(9, 'cc')

select *
from (
    select *, 
        RANK() OVER(PARTITION BY SUBSTRING(Name, 1, 1) ORDER BY Name ASC) AS [Rank] 
    from @Customer
) tableWithRank
WHERE tableWithRank.Rank <= 2

Output:

输出:

1|  aa|1

2|  ab|2

4|  ba|1

5|  bb|2

7|  ca|1

8|  cb|2