mysql - SQL 按名称字母顺序排序

Question

我在按字母排序时遇到问题，但我希望最后使用排序的名称我想要的是首先按字母排序，然后最后使用重复排序的名称。重复项可能超过 1 个。

我所做的结果:

$sql  = 'SELECT DISTINCT venue_id, name FROM venues ';
$sql .= 'ORDER BY CASE ';
$sql .= 'WHEN name NOT LIKE "%[DUPLICATE]" THEN "1" ';
$sql .= 'WHEN name LIKE "%[DUPLICATE]" THEN "100" ';
$sql .= 'END ASC ';


   *----------*------------------*
    |     ID   |  venue_name     |
    *----------*------------------*
    | 1       | Axxxx            |
    | 2       | Assss            |
    | 5       | Assss [duplicate]| // this is actually the whole name of the venue
    | 6       | Bzzzz            |
    | 7       | Bzzzz [duplicate]|
    | 8       | Fzzzz            |
    | 21      | Fzzzz [duplicate]|
    *----------*-----------------*

结果是否可能是这样的:

*----------*------------------*
|     ID   |  venue_name     |
*----------*------------------*
| 1       | Axxxx            |
| 2       | Assss            |
| 6       | Bzzzz            |
| 8       | Fzzzz            |
| 5       | Assss [duplicate]|
| 7       | Bzzzz [duplicate]|
| 21      | Fzzzz [duplicate]|
*----------*-----------------*

Answer 1

希望这会有所帮助

Set Nocount On

Declare @Table Table
(
     Id             Int
    ,venue_name     Varchar(100)
)

Insert Into @Table(Id,venue_name) Values
 (1,'Axxxx')
,(2,'Assss')
,(5,'Assss')
,(6,'Bzzzz')
,(7,'Bzzzz')
,(8,'Fzzzz')
,(21,'Fzzzz')

;With t1 As
(
    Select   t.Id
            ,t.venue_name
    From    @Table As t
            Join
            (
                Select  Min(t.Id) As Id
                From    @Table As t
                Group By t.venue_name
            ) As t1 On t.Id = t1.Id

    Union All

    Select   t.Id
            ,t.venue_name
    From    @Table As t
            Join 
            (
                Select   Min(t.Id) As Id
                        ,t.venue_name
                From    @Table As t
                Group By t.venue_name
            ) As t2 On t.venue_name = t2.venue_name And t.Id > t2.Id
)

Select  *
From    t1

根据您的要求进行编辑:-

Insert Into @Table(Id,venue_name) Values
 (1,'Axxxx')
,(2,'Assss')
,(5,'Assss [duplicate]')
,(6,'Bzzzz')
,(7,'Bzzzz [duplicate]')
,(8,'Fzzzz')
,(21,'Fzzzz [duplicate]')

;With t1 As
(
    Select   t.Id
            ,t.venue_name
    From    @Table As t
            Join
            (
                Select  Min(t.Id) As Id
                From    @Table As t
                Group By Replace(t.venue_name,' [duplicate]','')
            ) As t1 On t.Id = t1.Id

    Union All

    Select   t.Id
            ,t.venue_name
    From    @Table As t
            Join 
            (
                Select   Min(t.Id) As Id
                        ,Replace(t.venue_name,' [duplicate]','') As venue_name
                From    @Table As t
                Group By Replace(t.venue_name,' [duplicate]','')
            ) As t2 On Replace(t.venue_name,' [duplicate]','') = t2.venue_name And t.Id > t2.Id
)

Select  *
From    t1