id name addr
1 h 3
2 d 4
3 j 5
3 h 4显示效果
id name addr
1 h 3
2 d 4
3 j 5只要一条SQL能搞定它吗?
1 h 3
2 d 4
3 j 5
3 h 4显示效果
id name addr
1 h 3
2 d 4
3 j 5只要一条SQL能搞定它吗?
解决方案 »
- 表变量可以做为存储过程的输入或输出参数吗?
- SQL Server2005 安装问题,(以安装VS2005)
- 帮忙,看一下语句 char(13) 作用
- 请问怎么开1433!服务器网络实用工具里面tcp已经是1433了!放火墙关过!也没用!谢谢大家!(一定给分)
- 为什么dump transaction [TableName] WITH NO_LOG 无法执行?
- SQL语句 查询
- 千分解决此问题。up有分。
- 请教一个max degree of parallelism设置的问题
- 高分求购一条SQL语句的写法
- 请问在存储过程中怎么把int转成datetime?(在线急等)
- 求助,SQLServer2005 问题? 头疼...
- MS 2008跨域链接MS 2000
(select row_number() over(partition by id order by addr desc) as num,*
from tb) a
where num=1
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[name] varchar(1),[addr] int)
insert [TB]
select 1,'h',3 union all
select 2,'d',4 union all
select 3,'j',5 union all
select 3,'h',4
GO--> 查询结果
SELECT * FROM [TB] Y WHERE NOT EXISTS (SELECT 1 FROM TB WHERE [id]=Y.id AND addr>Y.addr)
--> 删除表格
--DROP TABLE [TB]
(SELECT number = ROW_NUMBER() OVER(PARTITION BY id ORDER BY GETDATE()), * FROM table_name) a
WHERE number = 1
create table [TB]([id] int,[name] varchar(1),[addr] int)
insert [TB]
select 1,'h',3 union all
select 2,'d',4 union all
select 3,'j',5 union all
select 3,'h',4SELECT DISTINCT b.* FROM [TB] a
CROSS APPLY
(SELECT TOP(1) * FROM [TB] WHERE id = a.id) b
declare @table table (id int,name varchar(1),addr int)
insert into @table
select 1,'h',3 union all
select 2,'d',4 union all
select 3,'j',5 union all
select 3,'h',4select id,MAX(name) AS name,MAX(addr) AS addr from @table GROUP BY id
/*
id name addr
----------- ---- -----------
1 h 3
2 d 4
3 j 5
*/