如何按照udate逆序将数据表中每个用户取 3 条数据?
注意字段都是字符串类型的
谢谢
6 个解决方案
#1
with userid(用户编号,字符串, udata, udate) as(
select 1,'a','20160301','2016-3-1' union all
select 2,'a','20160809','2016-8-9' union all
select 3,'a','20150703','2015-7-3' union all
select 4,'a','20160507','2016-5-7' union all
select 5,'b','20130304','2013-3-4' union all
select 6,'b','20130404','2013-4-4' union all
select 7,'b','20130304','2013-3-4' union all
select 8,'b','20130604','2013-6-4' union all
select 9,'b','20130504','2013-5-4')
select 用户编号,字符串, udata, udate from
(select ROW_NUMBER()over(partition by 字符串 order by udate desc) as id,* from userid) as a
where id<4
/*
用户编号 字符串 udata udate
----------------------------
2 a 20160809 2016-8-9
4 a 20160507 2016-5-7
1 a 20160301 2016-3-1
8 b 20130604 2013-6-4
9 b 20130504 2013-5-4
6 b 20130404 2013-4-4
*/
#2
呵呵,lz的字符串是多余的备注,字段名是用户编号.
partition by 用户编号
partition by 用户编号
#3
with userid(用户编号, udata) as(
select 'a','2016-3-1' union all
select 'a','2016-8-9' union all
select 'a','2015-7-3' union all
select 'a','2016-5-7' union all
SELECT 'b','2013-3-4' union all
select 'b','2013-4-4' union all
select 'b','2013-3-4' union all
select 'b','2013-6-4' union all
select 'b','2013-5-4')
SELECT *
FROM userid a
WHERE EXISTS(SELECT * FROM userid WHERE 用户编号 = a.用户编号 AND udata < a.udata HAVING COUNT(*) < 3)
#4
逆序,修正一下符号
with userid(用户编号, udata) as(
select 'a','2016-3-1' union all
select 'a','2016-8-9' union all
select 'a','2015-7-3' union all
select 'a','2016-5-7' union all
SELECT 'b','2013-3-4' union all
select 'b','2013-4-4' union all
select 'b','2013-3-4' union all
select 'b','2013-6-4' union all
select 'b','2013-5-4')
SELECT *
FROM userid a
WHERE EXISTS(SELECT * FROM userid WHERE 用户编号 = a.用户编号 AND udata < a.udata HAVING COUNT(*) < 3)
#5
或
SELECT b.* FROM (SELECT DISTINCT 用户编号 FROM Tab) AS a CROSS APPLY(SELECT TOP 3 * FROM Tab WHERE 用户编号=a.用户编号 ORDER BY udata DESC) AS b
#6
楼主的情况要高效还是要用排序高效
SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY 用户编号 ORDER BY udata) AS RN FROM Tab) AS a WHERE RN<=3
#1
with userid(用户编号,字符串, udata, udate) as(
select 1,'a','20160301','2016-3-1' union all
select 2,'a','20160809','2016-8-9' union all
select 3,'a','20150703','2015-7-3' union all
select 4,'a','20160507','2016-5-7' union all
select 5,'b','20130304','2013-3-4' union all
select 6,'b','20130404','2013-4-4' union all
select 7,'b','20130304','2013-3-4' union all
select 8,'b','20130604','2013-6-4' union all
select 9,'b','20130504','2013-5-4')
select 用户编号,字符串, udata, udate from
(select ROW_NUMBER()over(partition by 字符串 order by udate desc) as id,* from userid) as a
where id<4
/*
用户编号 字符串 udata udate
----------------------------
2 a 20160809 2016-8-9
4 a 20160507 2016-5-7
1 a 20160301 2016-3-1
8 b 20130604 2013-6-4
9 b 20130504 2013-5-4
6 b 20130404 2013-4-4
*/
#2
呵呵,lz的字符串是多余的备注,字段名是用户编号.
partition by 用户编号
partition by 用户编号
#3
with userid(用户编号, udata) as(
select 'a','2016-3-1' union all
select 'a','2016-8-9' union all
select 'a','2015-7-3' union all
select 'a','2016-5-7' union all
SELECT 'b','2013-3-4' union all
select 'b','2013-4-4' union all
select 'b','2013-3-4' union all
select 'b','2013-6-4' union all
select 'b','2013-5-4')
SELECT *
FROM userid a
WHERE EXISTS(SELECT * FROM userid WHERE 用户编号 = a.用户编号 AND udata < a.udata HAVING COUNT(*) < 3)
#4
逆序,修正一下符号
with userid(用户编号, udata) as(
select 'a','2016-3-1' union all
select 'a','2016-8-9' union all
select 'a','2015-7-3' union all
select 'a','2016-5-7' union all
SELECT 'b','2013-3-4' union all
select 'b','2013-4-4' union all
select 'b','2013-3-4' union all
select 'b','2013-6-4' union all
select 'b','2013-5-4')
SELECT *
FROM userid a
WHERE EXISTS(SELECT * FROM userid WHERE 用户编号 = a.用户编号 AND udata < a.udata HAVING COUNT(*) < 3)
#5
或
SELECT b.* FROM (SELECT DISTINCT 用户编号 FROM Tab) AS a CROSS APPLY(SELECT TOP 3 * FROM Tab WHERE 用户编号=a.用户编号 ORDER BY udata DESC) AS b
#6
楼主的情况要高效还是要用排序高效
SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY 用户编号 ORDER BY udata) AS RN FROM Tab) AS a WHERE RN<=3