如何在全名栏中按姓氏排序?

我会这样做:

SELECT FullName
FROM TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE(FullName), 0, CHARINDEX(' ', REVERSE(FullName)))) 

不必每次运行查询时都计算姓氏,您可以使用一个计算列将实际值持久化到一个列中,该列可以像任何其他列一样使用。

ALTER TABLE Customer
    ADD LastName AS 
        RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) PERSISTED

编辑: 要处理没有空格的值:

ALTER TABLE Customer
    ADD LastName AS 
    case when CHARINDEX(' ', REVERSE(FullName)) > 0
    then RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) 
    else
    FullName
    end
    PERSISTED

尽管您可以随意摆弄这个函数来确定在这种情况下会发生什么。我的目的是证明case语句可以被使用。您可能还希望将所有输出路径强制转换为同一类型,以避免类型不匹配。

DECLARE @YourTable table (FullNamevarchar(30))

INSERT @YourTable VALUES ('Harry Smith');
INSERT @YourTable VALUES ('John Davis');
INSERT @YourTable VALUES ('Allision Thomas Williams');

SELECT
    FullName
        ,RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS SortBy
    FROM @YourTable
    ORDER BY RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)

输出:

FullName                       SortBy
------------------------------ ------------------------------
John Davis                     Davis
Harry Smith                    Smith
Allision Thomas Williams       Williams

(3 row(s) affected)

查询

SELECT stringrowname FROM tablename 
ORDER BY SUBSTRING_INDEX((stringrowname)," ",-1);

它将按最后一个单词的顺序返回字符串。

create table foo(fullname varchar(100))
go

insert into foo values ('Harry Smith');
insert into foo values ('John Davis');
insert into foo values ('Allision Thomas Williams');

SELECT fullname
     , REVERSE(left(REVERSE(fullname), charindex(' ',REVERSE(fullname))-1))
  FROM foo
ORDER BY REVERSE(left(REVERSE(fullname), charindex(' ',REVERSE(fullname))-1))

输出:

fullname                   (No column name)
John Davis                  Davis
Harry Smith                 Smith
Allision Thomas Williams    Williams

有疑问时,自己动手:

Select
*
From
Users
Order By
LTrim(Reverse(Left(Reverse(FullName), CharIndex(' ', Reverse(FullName))))) Asc,
FullName Asc -- Fall-over for when there isn't a last name

create table #tmpTable
(
    ID int identity(1,1) primary key,
    FullName varchar(100) not null
);

insert into #tmpTable(FullName) values('Big John Sansom');
insert into #tmpTable(FullName) values('Mike Douglas Reid');
insert into #tmpTable(FullName) values('First Second Last');
insert into #tmpTable(FullName) values('JustOneTokenForName');

select 
    FullName,
    LastName = case 
        when CHARINDEX(FullName,' ') = 0 THEN FullName
        else RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)
    end
from #tmpTable
order by LastName

drop table #tmpTable

order by substring(0, charindex(',', FullName))

你可能得稍微摆弄一下,但我相信那应该行得通。