我的问题是:
我有一段数据:由用户名和字符串(按字典序)组成,我现在需要查询出来同一个用户名下字符串最大值
例如:
ID UserName Descripe
1 zhangsan abc
2 zhangsan bcd
3 lisi efg
4 lisi xge
5 lisi sdf
...................
查出来应该是:
2 zhangsan bcd
4 lisi xge这个用SQL语句怎么表达?
我有一段数据:由用户名和字符串(按字典序)组成,我现在需要查询出来同一个用户名下字符串最大值
例如:
ID UserName Descripe
1 zhangsan abc
2 zhangsan bcd
3 lisi efg
4 lisi xge
5 lisi sdf
...................
查出来应该是:
2 zhangsan bcd
4 lisi xge这个用SQL语句怎么表达?
go
create table [TB] (ID int,UserName nvarchar(16),Descripe nvarchar(6))
insert into [TB]
select 1,'zhangsan','abc' union all
select 2,'zhangsan','bcd' union all
select 3,'lisi','efg' union all
select 4,'lisi','xge' union all
select 5,'lisi','sdf'select * from [TB]
select distinct B.id,B.UserName,B.Descripe
from TB A
cross apply(select top 1 id,UserName,Descripe from TB where A.UserName = UserName order by UserName,Descripe desc)B
/*
id UserName Descripe
----------- ---------------- --------
2 zhangsan bcd
4 lisi xge(2 行受影响)*/
select *
from TB A
where ID in (select top(1) ID from TB where A.UserName = UserName order by UserName,Descripe desc)/*
ID UserName Descripe
----------- ---------------- --------
2 zhangsan bcd
4 lisi xge(2 行受影响)*/
CREATE FUNCTION fn_MaxStr(@str varchar(20))
RETURNS int
begin
DECLARE @dic VARCHAR(max)
DECLARE @sum INT
DECLARE @len INT
DECLARE @i INT
SET @dic='abcdefghijklmnopqrstuvwxyz';
SET @sum=0;
SET @len=LEN(@str);
SET @i=0;
IF(@len>0)
BEGIN
WHILE(@i<@len)
BEGIN
SET @sum=@sum+CHARINDEX(SUBSTRING(@str,@i+1,1),@dic)
SET @i=@i+1
END
END
RETURN @sum
END
GO
DECLARE @table TABLE(id INT,UserName VARCHAR(20),Descripe VARCHAR(26))
INSERT @table
SELECT 1 ,'zhangsan', 'abc' UNION ALL
SELECT 2,'zhangsan', 'bcd' UNION ALL
SELECT 3 ,'lisi', 'efg' UNION ALL
SELECT 4 ,'lisi', 'xge' UNION ALL
SELECT 5 ,'lisi', 'sdf'
SELECT id,UserName,descripe FROM
(
SELECT ID,UserName,dbo.fn_MaxStr(Descripe) AS value,Descripe,
ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY dbo.fn_MaxStr(Descripe) DESC) AS num FROM @table
GROUP BY UserName,Descripe,id
) AS T
WHERE num=1
ORDER BY id
/*id UserName descripe
----------- -------------------- --------------------------
2 zhangsan bcd
4 lisi xge(2 行受影响)*/