------------------------------------------------------------------------ -- Author: happyflystone -- Date : 2009-02-03 23:15:38 -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation -- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) -- -------------------------------------------------------------------------- Test Data: ta IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(col NVARCHAR(3)) Go INSERT INTO ta SELECT 'A' UNION ALL SELECT 'S' UNION ALL SELECT 'Z' UNION ALL SELECT 'T' UNION ALL SELECT 'AA' UNION ALL SELECT 'AB' UNION ALL SELECT 'AAA' UNION ALL SELECT 'AD' GO --Start SELECT * FROM TA order by len(col) desc,col desc --Result: /* --- AAA AD AB AA Z T S A(8 行受影响)*/ --End
if object_id('tempdb..#')is not null drop table # go create table #(col varchar(10)) insert # select 'A' insert # select 'S' insert # select 'Z' insert # select 'T' insert # select 'AA' insert # select 'AB' insert # select 'AAA' insert # select 'AD' SELECT * FROM # order by len(col) desc,col desc /*col ---------- AAA AD AB AA Z T S A(8 行受影响)*/
-- Author: happyflystone
-- Date : 2009-02-03 23:15:38
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(3))
Go
INSERT INTO ta
SELECT 'A' UNION ALL
SELECT 'S' UNION ALL
SELECT 'Z' UNION ALL
SELECT 'T' UNION ALL
SELECT 'AA' UNION ALL
SELECT 'AB' UNION ALL
SELECT 'AAA' UNION ALL
SELECT 'AD'
GO
--Start
SELECT
*
FROM
TA
order by len(col) desc,col desc
--Result:
/*
---
AAA
AD
AB
AA
Z
T
S
A(8 行受影响)*/
--End
go
create table #(col varchar(10))
insert # select 'A'
insert # select 'S'
insert # select 'Z'
insert # select 'T'
insert # select 'AA'
insert # select 'AB'
insert # select 'AAA'
insert # select 'AD'
SELECT * FROM # order by len(col) desc,col desc
/*col
----------
AAA
AD
AB
AA
Z
T
S
A(8 行受影响)*/