34,594
社区成员
发帖
与我相关
我的任务
分享
create table [tb]([col1] varchar(8),[col2] varchar(6),[col3] varchar(8),[col4] numeric(3,2))
insert [tb]
select '05021604','臧海梅','假日加班',0.50 union all
select '05021604','臧海梅','平时加班',2.00
SELECT * into #tb FROM tb WHERE col1='05021604'
UPDATE #tb SET col1='' WHERE not exists (SELECT TOP 1 * FROM #tb t where t.col3>#tb.col3 )
select * from #tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(10),[b] varchar(10),[c] varchar(10),[d] dec(9,2))
insert [tb] select 5021604,'臧海梅','假日加班',0.5
union all select 5021604,'臧海梅','平时加班',2
go
select
a=isnull((select top 1 a from tb where a=t.a and c>t.c),''),
b,
c,
d
from
tb t
/*
a b c d
---------- ---------- ---------- ---------------------------------------
5021604 臧海梅 假日加班 0.50
臧海梅 平时加班 2.00
(2 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(10),[b] varchar(10),[c] varchar(10),[d] dec(9,2))
insert [tb] select 5021604,'臧海梅','假日加班',0.5
union all select 5021604,'臧海梅','平时加班',2
go
select
a=isnull((select top 1 a from tb where a=t.a and d>t.d),''),
b,
c,
d
from
tb t
/*
a b c d
---------- ---------- ---------- ---------------------------------------
5021604 臧海梅 假日加班 0.50
臧海梅 平时加班 2.00
(2 行受影响)
*/
DECLARE @TB TABLE([col1] VARCHAR(8), [col2] NVARCHAR(3), [col3] NVARCHAR(4), [col4] DECIMAL(10,1))
INSERT @TB
SELECT '05021604', N'臧海梅', N'假日加班', 0.50 UNION ALL
SELECT '05021604', N'臧海梅', N'平时加班', 2.00
SELECT CASE WHEN SEQ=1 THEN col1 else '' END AS col1,col2,col3,col4
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY GETDATE())
FROM @TB
) T
/*
col1 col2 col3 col4
-------- ---- ---- ---------------------------------------
05021604 臧海梅 假日加班 0.5
臧海梅 平时加班 2.0
*/
SELECT * into #tb FROM TA WHERE 序号='05021604'
UPDATE #tbSET 序号='' WHERE NOT EXISTS(SELECT TOP 1 * FROM #tb)
SELECT * into #tb FROM TA WHERE 序号='05021604'
UPDATE #tbSET 序号='' WHERE NOT EXISTS(SELECT TOP 1 * FROM #tb)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(8),[col2] varchar(6),[col3] varchar(8),[col4] numeric(3,2))
insert [tb]
select '05021604','臧海梅','假日加班',0.50 union all
select '05021604','臧海梅','平时加班',2.00
---查询---
select
col1=case when exists(select 1 from tb where col1=t.col1 and col3<t.col3) then '' else col1 end,
col2,
col3,
col4
from
tb t
---结果---
col1 col2 col3 col4
-------- ------ -------- -----
05021604 臧海梅 假日加班 .50
臧海梅 平时加班 2.00
(所影响的行数为 2 行)
UPDATE TB SET 序号='' WHERE NOT EXISTS(SELECT TOP 1 * FROM TB)