Mysql按列全名时的姓氏排序 [英] Mysql Order by last name when full name for column

问题描述

我有以下内容:

 SELECT * FROM users LEFT JOIN user_info ON users.id=user_info.user_id 
                WHERE 
                    ((user_info.tester != 1) OR (user_info.tester is null)) AND 
                    id in (SELECT explicituser_id FROM user_login WHERE (created < '2012-12-17' OR created >= date_add('2012-12-17', interval 1 day))) AND 
                    id IN (SELECT participte_id FROM roster WHERE roster_id IN (6)) 
                order by 
                    substring_index(users.name, ' ', -1)

我只是想按用户的姓氏排序.

I'm simply trying to sort by the users' last name.

但是，虽然可以按名字排序，但是姓氏是越野车.如果用户在其姓名前后加上引号(例如，"Abigail Martinez")，则会使排序不正确.如果用户仅提供一个名称，并且又是昵称(例如，Juan)，则也将使其不正确.

However, while it can sort by the first name, the last name is buggy. If the user has quotes around their name (ie. "Abigail Martinez" it will make the sorting incorrect. If the user provides only one name, and it's a nickname (ie. Juan), then it will also make it incorrect. And then there's middle initials (ie. Tiffany S Villa or Steve de la Makinov). Unfortunately, this uses only one column for the full name (users.name).

感谢您的帮助.谢谢！

推荐答案

substring_index(TRIM(users.name), ' ', -1)添加TRIM将删除尾随空格.之后，将按预期进行排序.

substring_index(TRIM(users.name), ' ', -1) Adding TRIM will remove trailing spaces. After that, sorting occurs as expected.

这篇关于Mysql按列全名时的姓氏排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！