declare @t table(name varchar(10), type varchar(10),[group] varchar(10),date datetime)
insert @t select '张三', '类型1' , '组别1', '2008-08-08'
union all select '张三', '类型2' , '组别2' , '2008-08-10'
union all select '张三', '类型3' , '组别3' , '2008-08-28'
union all select '王五', '类型2' , '组别2' , '2008-08-18'
union all select '王五', '类型3' , '组别3' , '2008-08-21'
union all select '王五', '类型4', '组别4' , '2008-08-23' SELECT CASE WHEN (RANK() OVER (PARTITION BY name order by date))=1 THEN name ELSE '' END name,type,[group],date FROM @t
[code=SQL]王五 类型2 组别2 2008-08-18 00:00:00.000
类型3 组别3 2008-08-21 00:00:00.000
类型4 组别4 2008-08-23 00:00:00.000
张三 类型1 组别1 2008-08-08 00:00:00.000
类型2 组别2 2008-08-10 00:00:00.000
类型3 组别3 2008-08-28 00:00:00.000
[/code]
insert @t select '张三', '类型1' , '组别1', '2008-08-08'
union all select '张三', '类型2' , '组别2' , '2008-08-10'
union all select '张三', '类型3' , '组别3' , '2008-08-28'
union all select '王五', '类型2' , '组别2' , '2008-08-18'
union all select '王五', '类型3' , '组别3' , '2008-08-21'
union all select '王五', '类型4', '组别4' , '2008-08-23' SELECT CASE WHEN (RANK() OVER (PARTITION BY name order by date))=1 THEN name ELSE '' END name,type,[group],date FROM @t
[code=SQL]王五 类型2 组别2 2008-08-18 00:00:00.000
类型3 组别3 2008-08-21 00:00:00.000
类型4 组别4 2008-08-23 00:00:00.000
张三 类型1 组别1 2008-08-08 00:00:00.000
类型2 组别2 2008-08-10 00:00:00.000
类型3 组别3 2008-08-28 00:00:00.000
[/code]
解决方案 »
- 无法把一个表中的内容复制到另一个表中
- 连表查询
- Sqlserver报表服务器RDL文件如何在文本中输出一个Tab和导出到Excel后Padding属性无效
- SQL Server 已为 8 个并发查询而优化。1 个查询超过了此限制,因而性能可能会受到不良影响
- java访问sqlserver2005存储过程的临时表报错:(
- select * into #EtamShopIniStock from EtamShopIniStock where 1=2
- 请教关于 命令textcopy 中 ERROR: Argument '/' not recognized 的报错问题
- Sql server 2005 express使用windows验证
- Sql两张表,统计的问题。很急!求各位赐教哦
- 十万火急!请教各位,触发器中能不能用临时表!!!
- 关于数据库连接问题
- 求教:sql server 2005 中取得系统盘符的方法
insert @t select '张三', '类型1' , '组别1', '2008-08-08'
union all select '张三', '类型2' , '组别2' , '2008-08-10'
union all select '张三', '类型3' , '组别3' , '2008-08-28'
union all select '王五', '类型2' , '组别2' , '2008-08-18'
union all select '王五', '类型3' , '组别3' , '2008-08-21'
union all select '王五', '类型4', '组别4' , '2008-08-23' SELECT CASE WHEN (RANK() OVER (PARTITION BY name order by date))=1 THEN name ELSE '' END name,type,[group],date FROM @t/*
王五 类型2 组别2 2008-08-18 00:00:00.000
类型3 组别3 2008-08-21 00:00:00.000
类型4 组别4 2008-08-23 00:00:00.000
张三 类型1 组别1 2008-08-08 00:00:00.000
类型2 组别2 2008-08-10 00:00:00.000
类型3 组别3 2008-08-28 00:00:00.000*/
insert @t select '张三', '类型1' , '组别1', '2008-08-08'
union all select '张三', '类型2' , '组别2' , '2008-08-10'
union all select '张三', '类型3' , '组别3' , '2008-08-28'
union all select '王五', '类型2' , '组别2' , '2008-08-18'
union all select '王五', '类型3' , '组别3' , '2008-08-21'
union all select '王五', '类型4', '组别4' , '2008-08-23' SELECT CASE WHEN (RANK() OVER (PARTITION BY name order by date))=1 THEN name ELSE '' END name,type,[group],date FROM @t/*
王五 类型2 组别2 2008-08-18 00:00:00.000
类型3 组别3 2008-08-21 00:00:00.000
类型4 组别4 2008-08-23 00:00:00.000
张三 类型1 组别1 2008-08-08 00:00:00.000
类型2 组别2 2008-08-10 00:00:00.000
类型3 组别3 2008-08-28 00:00:00.000*/
declare @t table(name varchar(10), type varchar(10),[group] varchar(10),date datetime)
insert @t select '张三', '类型1' , '组别1', '2008-08-08'
union all select '张三', '类型2' , '组别2' , '2008-08-10'
union all select '张三', '类型3' , '组别3' , '2008-08-28'
union all select '王五', '类型2' , '组别2' , '2008-08-18'
union all select '王五', '类型3' , '组别3' , '2008-08-21'
union all select '王五', '类型4', '组别4' , '2008-08-23' SELECT CASE WHEN EXISTS(SELECT 1 FROM @t WHERE name=a.name AND date<a.date) THEN '' ELSE name END name,type,[group],date FROM @t a如果同name 的date有重复值,最好还是需要标识列。
declare @t table
(
[name] varchar(50),
[type] varchar(50),
[group] varchar(50),
[date] datetime
)
insert into @t
select '张三','类型1','组别1','2008-08-08' union all
select '张三','类型2','组别2','2008-08-10' union all
select '张三','类型3','组别3','2008-08-28' union all
select '王五','类型2','组别2','2008-08-18' union all
select '王五','类型3','组别3','2008-08-21' union all
select '王五','类型4','组别4','2008-08-23' SELECT
CASE WHEN RANK() OVER (PARTITION BY [name] order by [type])=1 THEN [name]
ELSE ''
END [name],
[type],
[group],
[date]
FROM @t
declare @t table
(
[name] varchar(50),
[type] varchar(50),
[group] varchar(50),
[date] datetime
)insert into @t
select '张三','类型1','组别1','2008-08-08' union all
select '张三','类型2','组别2','2008-08-10' union all
select '张三','类型3','组别3','2008-08-28' union all
select '王五','类型2','组别2','2008-08-18' union all
select '王五','类型3','组别3','2008-08-21' union all
select '王五','类型4','组别4','2008-08-23' SELECT
CASE WHEN (RANK() OVER (PARTITION BY [name] order by [type],[group],[date]))=1 THEN [name]
ELSE ''
END [name],
[type],
[group],
[date]
FROM @t