34,592
社区成员
发帖
与我相关
我的任务
分享
--我觉得这个不错啊
UPDATE t
SET STATUS=(CASE WHEN EXISTS(SELECT 1 FROM #Table WHERE NAME= t.NAME AND BEGIN_DATE<t.BEGIN_DATE) THEN 'n' ELSE 'y' END)
FROM #Table t
/*
ID NAME BEGIN_DATE STATUS
----------- ---------- ----------------------- ----------
1 A 2001-01-23 00:00:00.000 y
2 B 2002-01-11 00:00:00.000 y
3 B 2003-01-08 00:00:00.000 n
4 C 2004-01-01 00:00:00.000 n
5 A 2005-01-07 00:00:00.000 n
6 D 2006-01-06 00:00:00.000 y
7 A 2007-01-01 00:00:00.000 n
8 C 2008-01-05 00:00:00.000 n
9 C 2002-02-01 00:00:00.000 y
10 A 2003-04-01 00:00:00.000 n
11 Z 2008-01-08 00:00:00.000 y
(11 行受影响)
*/
declare @t table([ID] int primary key,[name] varchar(2),begin_date datetime, status varchar(1))
insert into @t values(1,'A','2001-01-23','y')
insert into @t values(2,'B','2002-01-11','y')
insert into @t values(3,'B','2003-01-08','y')
insert into @t values(4,'C','2004-01-01','y')
insert into @t values(5,'A','2005-01-07','y')
insert into @t values(6,'D','2006-01-06','y')
insert into @t values(7,'A','2007-01-01','n')
insert into @t values(8,'C','2008-01-05','n')
insert into @t values(9,'C','2002-02-01','n')
insert into @t values(10,'A','2003-04-01','n')
select * from @t order by 2,3
update @t
set status='n'
update @t
set status='y'
where id in (
select a.id
from @t a
where not exists(select 1 from @t where name = a.name and begin_date < a.begin_date)
)
select * from @t order by 2,3
----------------------------------------------------------
ID name begin_date status
----------- ---- ----------------------- ------
1 A 2001-01-23 00:00:00.000 y
10 A 2003-04-01 00:00:00.000 n
5 A 2005-01-07 00:00:00.000 y
7 A 2007-01-01 00:00:00.000 n
2 B 2002-01-11 00:00:00.000 y
3 B 2003-01-08 00:00:00.000 y
9 C 2002-02-01 00:00:00.000 n
4 C 2004-01-01 00:00:00.000 y
8 C 2008-01-05 00:00:00.000 n
6 D 2006-01-06 00:00:00.000 y
(10 個資料列受到影響)
(10 個資料列受到影響)
(4 個資料列受到影響)
ID name begin_date status
----------- ---- ----------------------- ------
1 A 2001-01-23 00:00:00.000 y
10 A 2003-04-01 00:00:00.000 n
5 A 2005-01-07 00:00:00.000 n
7 A 2007-01-01 00:00:00.000 n
2 B 2002-01-11 00:00:00.000 y
3 B 2003-01-08 00:00:00.000 n
9 C 2002-02-01 00:00:00.000 y
4 C 2004-01-01 00:00:00.000 n
8 C 2008-01-05 00:00:00.000 n
6 D 2006-01-06 00:00:00.000 y
(10 個資料列受到影響)
CREATE TABLE #Table(
ID int identity(1,1),
NAME varchar(10),
BEGIN_DATE datetime,
STATUS varchar(10));
GO
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('A','2001-01-23','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('B','2002-01-11','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('B','2003-01-08','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('C','2004-01-01','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('A','2005-01-07','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('D','2006-01-06','y');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('A','2007-01-01','n');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('C','2008-01-05','n');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('C','2002-02-01','n');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('A','2003-04-01','n');
INSERT #Table(NAME,BEGIN_DATE,STATUS) VALUES('Z','2008-01-08','y');
GO
SELECT * FROM #Table
--测试数据
/*
ID NAME BEGIN_DATE STATUS
----------- ---------- ----------------------- ----------
1 A 2001-01-23 00:00:00.000 y
2 B 2002-01-11 00:00:00.000 y
3 B 2003-01-08 00:00:00.000 y
4 C 2004-01-01 00:00:00.000 y
5 A 2005-01-07 00:00:00.000 y
6 D 2006-01-06 00:00:00.000 y
7 A 2007-01-01 00:00:00.000 n
8 C 2008-01-05 00:00:00.000 n
9 C 2002-02-01 00:00:00.000 n
10 A 2003-04-01 00:00:00.000 n
11 Z 2008-01-08 00:00:00.000 y
(11 行受影响)
*/
--更新
UPDATE #Table
SET STATUS='n';
GO
UPDATE #Table
SET STATUS='y'
WHERE BEGIN_DATE IN (SELECT MIN(BEGIN_DATE) FROM #Table GROUP BY NAME)
SELECT * FROM #TABLE
--结果:
/*
ID NAME BEGIN_DATE STATUS
----------- ---------- ----------------------- ----------
1 A 2001-01-23 00:00:00.000 y
2 B 2002-01-11 00:00:00.000 y
3 B 2003-01-08 00:00:00.000 n
4 C 2004-01-01 00:00:00.000 n
5 A 2005-01-07 00:00:00.000 n
6 D 2006-01-06 00:00:00.000 y
7 A 2007-01-01 00:00:00.000 n
8 C 2008-01-05 00:00:00.000 n
9 C 2002-02-01 00:00:00.000 y
10 A 2003-04-01 00:00:00.000 n
11 Z 2008-01-08 00:00:00.000 y
(11 行受影响)
*/
update a
set Status=case when BEGIN_DATE =(select min(BEGIN_DATE) from t where Name=a.Name)
then 'Y'
else 'N' end
from
t a
update a
set status=case when exists (select 1 from [table] where name=a.name and BEGIN_DATE<a.BEGIN_DATE) then 'n' else 'y' end
from [table] a