34,575
社区成员
发帖
与我相关
我的任务
分享
-- 数据
create table tb(mm varchar(20),nn int)
insert tb values('我',1);
insert tb values('我',2);
insert tb values('你',3);
insert tb values('他',4);
insert tb values('她',5);
insert tb values('她',6);
-- 查询
select mm from (
select r=row_number() over (partition by mm order by nn), * from tb
) a
where a.r=1
order by nn desc
CREATE TABLE TEST2
(
Name VARCHAR(10),
Id INT
)
GO
INSERT INTO TEST2
SELECT '我',1 UNION
SELECT '我',2 UNION
SELECT '你',3 UNION
SELECT '他',4 UNION
SELECT '她',5 UNION
SELECT '她',6
SELECT NAME
FROM TEST2
GROUP BY Name
ORDER BY MAX(ID) DESC
declare @tb table (a nvarchar(12),b int)
insert @tb
select '我',1 union all
select '我',2 union all
select '你',3 union all
select '他',4 union all
select '她',5 union all
select '她',6
select a from (
select top (100) ROW_NUMBER() over(PARTITION by a order by a desc)rn ,a,b from @tb group by a,b order by b desc) aa
where rn=1
/*
a
她
他
你
我
*/
IF TYPE_ID('tyta') IS NOT NULL
DROP TYPE tyta;
GO
CREATE TYPE tyta AS TABLE
( a CHAR(4)
);
GO
DECLARE @a AS tyta;
BEGIN
INSERT INTO @a SELECT a FROM ta ORDER BY b DESC ;
SELECT DISTINCT * FROM @a;
END;
/*结果
a
她
你
他
我
*/