我想首先按字段对查询进行排序,然后对不在“按字段”列表中的查询按字母顺序排序。当我这样做时,结果首先给出了不在字段列表中的所有字段,在这些字段之后,结果显示了按字段排序的字段(正确)
这是我的问题:
SELECT merk FROM user WHERE merk <> '' group by merk
ORDER BY FIELD(merk,'Theo', 'Anne et Valentin') ASC, merk ASC结果如下所示(请看最后两条是ORDER BY字段记录)
'Binoche'
'Binoche Mini'
'Blac'
'Carrera'
'Christian Dior'
'D&G'
'Staffan Preutz Design'
'Tim Van Steenbergen'
'Tom Ford'
'Tom Tailor'
'Tommy Hilfiger'
'Vogue'
'Vuillet Vega'
'You\'s Eyeworks'
'Theo'
'Anne et Valentin'发布于 2012-11-21 00:07:26
您也可以这样做,这样就不必更改自定义字段的排序顺序:http://www.sqlfiddle.com/#!2/c9122/1
SELECT merk , FIELD(merk,'Theo', 'Anne et Valentin')
FROM user
WHERE merk <> '' group by merk
ORDER BY
COALESCE(
NULLIF(FIELD(merk,'Theo', 'Anne et Valentin'), 0)
, 99999)
ASC输出:
| MERK | FIELD(MERK,'THEO', 'ANNE ET VALENTIN') |
------------------------------------------------------------------
| Theo | 1 |
| Anne et Valentin | 2 |
| Tom Tailor | 0 |
| Staffan Preutz Design | 0 |
| Carrera | 0 |
| Binoche | 0 |
| You''s Eyeworks | 0 |
| Tommy Hilfiger | 0 |
| Tim Van Steenbergen | 0 |
| Christian Dior | 0 |
| Binoche Mini | 0 |
| Vogue | 0 |
| Tom Ford | 0 |
| D&G | 0 |
| Blac | 0 |
| Vuillet Vega | 0 |发布于 2012-11-20 23:14:03
这是因为当值不在列表中时,FIELD(merk,'Theo', 'Anne et Valentin')会返回zero,所以不使用FIELD(merk,'Theo', 'Anne et Valentin') ASC,而是按降序排序,这样出现在FIELD列表中的值将首先排序。
SELECT..
FROM..
WHERE....
ORDER BY FIELD(merk,'Theo', 'Anne et Valentin') DESC, merk ASC源
https://stackoverflow.com/questions/13476104
复制相似问题