自己都认为奇葩的排序，要怎么实现呢。

[haochin]

有表如下
a b
1 3
2 4
4 6
4 1

要按b从大到小排，那么结果会是
a c
4 6
2 4
1 3
4 1

但我想在上面的基础上，
将最后一条移动到第一条后，意思就是说，a有重复时，不管其b的大小，统统排到一起去。
我要的最终结果
a b
4 6
4 1
2 4
1 3

要怎么弄啊。

[Vidor]

难道不是这样？ order by a desc, b desc

[Vidor]

这样就符合题意了：

-- data
if object_id('tempdb.dbo.#') is not null drop table #
create table #(a int, b int)
insert into #
select 1, 3 union all
select 2, 4 union all
select 4, 6 union all
select 4, 1

-- query
select * from # order by max(b)over(partition by a) desc, b desc

/*
a           b
----------- -----------
4           6
4           1
2           4
1           3
*/

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-01-14 23:52:37
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
--	Jun 17 2011 00:57:23 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] int,[b] int)
insert [huang]
select 1,3 union all
select 2,4 union all
select 4,6 union all
select 4,1
--------------开始查询--------------------------

select * 
from [huang]
ORDER BY CASE WHEN b>a THEN b ELSE a END  DESC 
----------------结果----------------------------
/* 
a           b
----------- -----------
4           6
4           1
2           4
1           3

(4 行受影响)

*/

[haochin]

忘记和大家说了。我用的sql 2000啊。。。。

[还在加载中灬]

--MSSQL 2000目测有效，因为没有MSSQL2000
USE tempdb
IF NOT OBJECT_ID('tempdb..#1') IS NULL
    DROP TABLE test..#1
CREATE TABLE #1(a INT, b INT)
INSERT INTO #1
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 4, 6 UNION ALL
SELECT 4, 1
GO
-----------------
-----------------
SELECT
 T1.a,T1.b
 FROM (
    SELECT a,MAX(b) b FROM #1 GROUP BY a
) CTE
 INNER JOIN #1 T1 ON CTE.a=T1.a
 ORDER BY CTE.b DESC,T1.b DESC

[Paddy]

create table #test(a int,b int)

insert into #test
select 5,3
union all
select 2,4
union all
select 4,6
union all
select 4,1

create table #result(id int identity,a int,b int)

insert into #result
select  A.* from #test A
join
(
select a,count(1)as count,sum(b)as sumb from #test group by a having count(1) > 1
) B
ON A.a = B.a
order by   b.count   DESC

insert into #result
select  A.* from #test A
join
(
select a,count(1)as count,sum(b)as sumb from #test group by a having count(1) = 1
) B
ON A.a = B.a
order by A.b  DESC

select a,b from #result

楼主，因为你的排序规则是先后按照两个列去排序，所以就分成两部分查询并输出即可。

[Paddy]

引用 3 楼 DBA_Huangzj 的回复:

SQL code?123456789101112131415161718192021222324252627282930313233343536------------------------------------------------------------------ Author :DBA_Huangzj(發糞塗牆)-- Date :2013-01-14……

斑竹~ 如果表中再插入一条 5,3，你的方法输出结果会有些问题的

[bihai]

if object_id('[DB]') is not null drop table [DB] go create table [DB]([a] int,[b] int) insert [DB] select 1,3 union all select 2,7 union all select 4,6 union all select 4,1 select * from DB as #1 order by (select MAX(b) from DB where DB.a=#1.a) desc,b desc a b ----------- ----------- 2 7 4 6 4 1 1 3 (4 行受影响)

[昵称被占用了]

2楼解决了吧

[發糞塗牆]

引用 4 楼 zc10151 的回复:

SQL code ? 123456789101112131415161718192021222324252627282930313233 create table #test(a int,b int) insert into #test select 5,3 union allselect 2,4 union allselect 4,6 union allselect 4……

我承认我那个在5，3的时候不行，不过你这个貌似也不行吧？我没改动过，你的原样数据，只是把数据贴出来而已：

create table #test(a int,b int) 
  
insert into #test 
select 5,3 
union all
select 2,4 
union all
select 4,6 
union all
select 4,1 
  
create table #result(id int identity,a int,b int) 
  
insert into #result 
select  A.* from #test A 
join
( 
select a,count(1)as count,sum(b)as sumb from #test group by a having count(1) > 1 
) B 
ON A.a = B.a 
order by   b.count   DESC
  
insert into #result 
select  A.* from #test A 
join
( 
select a,count(1)as count,sum(b)as sumb from #test group by a having count(1) = 1 
) B 
ON A.a = B.a 
order by A.b  DESC
  
select a,b from #result

/*
a           b
----------- -----------
4           6
4           1
2           4
5           3

(4 行受影响)

*/

[还在加载中灬]

--MSSQL 2005+有效
USE tempdb
IF NOT OBJECT_ID('tempdb..#1') IS NULL
	DROP TABLE test..#1
CREATE TABLE #1(a INT, b INT)
INSERT INTO #1
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 4, 6 UNION ALL
SELECT 4, 1
GO
-----------------
-----------------
WITH CTE AS(
	SELECT a,MAX(b) b FROM #1 GROUP BY a
)
SELECT T1.a,T1.b FROM CTE INNER JOIN #1 T1 ON CTE.a=T1.a ORDER BY CTE.b DESC,T1.b DESC

[开着拖拉机泡妞]

ORDER BY A DESC,B DESC
这样不对么？？

[小猴168]

2楼++++1

[Paddy]

引用 7 楼 DBA_Huangzj 的回复:

引用 4 楼 zc10151 的回复:SQL code ? 123456789101112131415161718192021222324252627282930313233 create table #test(a int,b int) insert into #test select 5,3 union allselect 2,4 union……

可能真有问题，大半夜写代码有点晕，回头看看~

[發糞塗牆]

快4点还没睡，那么早就起来，你也堪称奇葩啊

[haochin]

大家踊跃发言吧，我还有3000可用分。嘿嘿。。

[xikboy]

.......先A在了罗.

[xikboy]

先A在B罗........

[發糞塗牆]

引用 13 楼 haochin 的回复:

大家踊跃发言吧，我还有3000可用分。嘿嘿。。

2楼的不能解决？

[DBA_磊仔]

--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go 
create table #TA([a] int,[b] int)
insert #TA
select 1,3 union all
select 1,10 union all
select 2,4 union all
select 4,6 union all
select 4,1
--------------开始查询--------------------------
;WITH CET AS
(
select *,max(b)over(partition by a)RN from #TA 

)SELECT a, b FROM CET order by RN desc, b desc
----------------结果----------------------------
/* 

(5 行受影响)
a           b
----------- -----------
1           10
1           3
4           6
4           1
2           4

(5 行受影响)

*/