获得前N行与在SQL Server 4列
假设我有4个列的表格:获得前N行与在SQL Server 4列
Col1 Col2 Col3 Col4
我的初始查询是:
SELECT Col1, Col2, Col3, Col4
FROM myTable
ORDER BY Col1, Col2, Col3 DESC, Col4
我期望的结果是所有4列,但与此条件,顶部N Col3不同的行当Col1,Col2是相等的。
实施例具有N = 2:
表的示例数据:
Col1 Col2 Col3 Col4
---------------------
1 a 2000 s
1 a 2002 c
1 a 2001 b
2 b 1998 s
2 b 2002 c
2 b 2000 b
3 c 2000 b
1 f 1998 n
1 g 1999 e
期望的结果:
1 a 2002 c
1 a 2001 b
1 f 1998 n
1 g 1999 e
2 b 2002 c
2 b 2000 b
3 c 2000 b
在另一个说明中,当(col1, col2)
在多个记录被重复,只是出口顶当按Col3降序排序时,这些记录有N行。
我可以用SQL脚本来做到这一点吗,不用硬编码?
declare @t table (Col1 int, Col2 char, Col3 int, Col4 char);
insert into @t values
(1, 'a', 2000, 's'),
(1, 'a', 2002, 'c'),
(1, 'a', 2001, 'b'),
(2, 'b', 1998, 's'),
(2, 'b', 2002, 'c'),
(2, 'b', 2000, 'b'),
(3, 'c', 2000, 'b'),
(1, 'f', 1998, 'n'),
(1, 'g', 1999, 'e');
declare @N int = 2; -- number per "top"
with cte as
(
select *,
row_number() over(partition by col1, col2 order by col3 desc) as rn
from @t
)
select *
from cte c
where rn <= @N;
感谢您的详细和测试答案,这是非常好的答案 – HelloMasters
METHOD FOR 1- MSSQL
http://sqlfiddle.com/#!6/4bda39/6
with a as (
select ROW_NUMBER() over(partition by t.col1,t.col2 order by t.col3 desc) as row,t.*
from myTable as t)
select * from a where a.row <= 2
替换a.row < = 2(2用N)
METHOD 2-对于MySQL
http://sqlfiddle.com/#!9/79e81a/63
SELECT myTable.Col1, myTable.Col2, myTable.Col3, myTable.Col4
FROM (
Select Col1 as Col1, Col2 as Col2, count(Col1) as cc, AVG(Col3) as aa
From myTable
group by Col1, Col2) as tt
join myTable on myTable.Col1 = tt.Col1 and myTable.Col2 = tt.Col2
where myTable.Col3 >= tt.aa
Order by Col1 ,Col2 ,Col3 Desc,Col4
METHOD 3-对于MySQL
http://sqlfiddle.com/#!9/79e81a/79
SELECT * FROM (
SELECT CASE Col1
WHEN @Col1 THEN
CASE Col2
WHEN @Col2 THEN @curRow := @curRow + 1
ELSE @curRow := 1
END
ELSE @curRow :=1
END AS rank,
@Col1 := Col1 AS Col1,
@Col2 := Col2 AS Col2,
Col3, Col4
FROM myTable p
JOIN (SELECT @curRow := 0, @Col1 := 0, @Col2 := '') r
ORDER BY Col1, Col2, Col3 DESC) as tt
WHERE tt.rank <= 2
替换tt.rank < = 2通过您的期望的索引代替2
请注意问题,当(Col1,Col2)在多行重复时,我的愿望结果是前N记录,现在Col3 – HelloMasters
的订单作出的变化很小 –
我认为下面代码与预期的一样
declare @tab table (Col1 int, Col2 char(1), Col3 int, Col4 char(1))
declare @N int
insert into @tab
select 1, 'a' , 2000, 's'
union all
select 1 , 'a' , 2002 , 'c'
union all
select 1 , 'a' , 2001 , 'b'
union all
select 2 , 'b' , 1998 , 's'
union all
select 2 , 'b' , 2002 ,'c'
union all
select 2 , 'b' , 2000 ,'b'
union all
select 3 , 'c' , 2000 ,'b'
union all
select 1 , 'f' , 1998 ,'n'
union all
select 1 , 'g' , 1999 ,'e'
;with tab as
(
select ROW_NUMBER() over(partition by t.col1,t.col2 order by t.col3 desc) as row,t.*
from @tab t
)
select Col1,Col2,Col3,Col4
from tab
where row < 3
输出
Col1 Col2 Col3 Col4
1 a 2002 c
1 a 2001 b
1 f 1998 n
1 g 1999 e
2 b 2002 c
2 b 2000 b
3 c 2000 b
Thanks但Max返回顶部Col3值但是我在示例中描述的Col3命令的时候希望N记录 – HelloMasters
谢谢,但是条件是:当有多个记录(Col1,Col2)相等时,只要输出Top N这条记录记录那个Col3降序的对不起,请问,它明确还是我必须描述更多? – HelloMasters
对列使用GROUP BY子句,然后使用ORDER BY COL3递减得到我们想要的输出 – Ajay
4 _columns_,并不领域。 – jarlh
group by在我的问题中SQL不适合,因为字段有不同的值,当按列分组时,每个值都去自己的组,并且不能访问做Top N Col3! – HelloMasters