有表如下
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要怎么弄啊。
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要怎么弄啊。
order by a desc, b desc
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 行受影响)*/
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,1create 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 DESCinsert 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 DESCselect a,b from #result
楼主,因为你的排序规则是先后按照两个列去排序,所以就分成两部分查询并输出即可。
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 行受影响)*/
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
这样不对么??
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 行受影响)*/
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
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 desca b
----------- -----------
2 7
4 6
4 1
1 3(4 行受影响)