有这样的一个表,有用户名name 物品号ID 物品使用日期date等属性,一个用户有多个物品,
现在我想要按照用户名分组,找出每个用户拥有的日期最大的两个物品,有如下sql语句
SELECT *
FROM (SELECT *, row_number() OVER (partition BY name
ORDER BY Date DESC) rn
FROM Tb1)
AS t1
WHERE rn = 2 OR rn = 1但是此查询语句如果出现用户有三个使用日期一样大的物品的话 只能取出前两个 请问高手怎么改进呢
现在我想要按照用户名分组,找出每个用户拥有的日期最大的两个物品,有如下sql语句
SELECT *
FROM (SELECT *, row_number() OVER (partition BY name
ORDER BY Date DESC) rn
FROM Tb1)
AS t1
WHERE rn = 2 OR rn = 1但是此查询语句如果出现用户有三个使用日期一样大的物品的话 只能取出前两个 请问高手怎么改进呢
FROM (SELECT *, row_number() OVER (partition BY name
ORDER BY Date DESC) rn
FROM Tb1)
AS t1
WHERE rn <=2
date in(
select top 2 date from tb1 t
where t.name=name group by date order by date desc )
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-07 14:40:38
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
--------------开始查询--------------------------
SELECT
id,col
FROM
(
SELECT rid=ROW_NUMBER() OVER(PARTITION BY ID ORDER BY col DESC),*
FROM tb
) AS T
WHERE rid<=2
----------------结果----------------------------
/*id col
----------- -----------
1 4
1 3
2 5
2 3
3 6
3 5(6 行受影响)
*/
(
SELECT *, row_number() OVER (partition BY name ORDER BY Date DESC) rn
FROM Tb1)
AS t1
WHERE rn<=2
from tb1 t
where
[date]=(select top 2 [date] from tb1 where name=t.name order by [date] desc)
go
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
SELECT [id],[col] FROM
(
SELECT *, row_number() OVER (partition BY [id] ORDER BY [col] DESC) rn
FROM [tb])
AS t1
WHERE rn<=2id col
----------- -----------
1 4
1 3
2 5
2 3
3 6
3 5(6 行受影响)
select *
from tb1 t
where
date IN (select top 2 date from (SELECT DISTINCT date FROM tb1 where name=t.name) d order by d.date DESC))
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
SELECT [id],[col] FROM
(
SELECT *, row_number() OVER (partition BY [id] ORDER BY [col] DESC) rn
FROM [tb])
AS t1
WHERE rn>2id col
----------- -----------
1 2
2 3
3 4(3 行受影响)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
select *
from tb t
where
col IN (select top 2 col from
(SELECT DISTINCT col FROM tb where id=t.id) d
order by d.col DESC
)id col
----------- -----------
1 3
1 4
2 3
2 3
2 5
3 5
3 6(7 行受影响)
(
select t.* , px = (select count(distinct date) from a where name = t.name and date >= t.date) from a t
) m
where px <= 2
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
select * from tb a where col in (select top 2 col from tb where a.id=id order by col desc)
/*
id col
----------- -----------
1 3
1 4
2 3
2 3
2 5
3 5
3 6(7 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] int)
insert [tb]
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,3 union all
select 2,3 union all
select 2,5 union all
select 3,4 union all
select 3,5 union all
select 3,6
SELECT [id],[col] FROM
(
SELECT *, dense_rank() OVER (partition BY [id] ORDER BY [col] DESC) rn
FROM [tb])
AS t1
WHERE rn<=2
/*
id col
----------- -----------
1 4
1 3
2 5
2 3
2 3
3 6
3 5(7 行受影响)
*/2K5的这样.
原来是dense_rank(),明白了,thx