22,301
社区成员




select id,mail,case when max(lastdate) is null then 1 else address end,max(lastdate) from tb where id = @id
group by id,mail,address
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[email] varchar(10),[address] int,[lastdate] varchar(20))
insert [tb]
select 1,'a@a.a',0,'2008-05-05' union all
select 2,'a@a.a',0,'2008-05-07' union all
select 3,'b@b.b',0,'2008-05-05' union all
select 4,'b@b.b',1,'2008-05-08' union all
select 5,'c@c.c',0,'2008-05-05'
--select * from [tb]
declare @email varchar(10)
set @email='a@a.a'
--set @email='b@b.b'
--set @email='c@c.c'
select top 1 email,address,lastdate
from tb
where email=@email
order by address desc,lastdate desc
--测试结果:
/*
email address lastdate
---------- ----------- --------------------
a@a.a 0 2008-05-07
(1 row(s) affected)
*/
DECLARE @T TABLE (id INT, email VARCHAR(10) , address INT, lastdate VARCHAR(10))
INSERT @T SELECT 1, 'a@a.a' , 0 , '2008-05-05'
INSERT @T SELECT 2, 'a@a.a' , 0 , '2008-05-07'
INSERT @T SELECT 3, 'b@b.b' , 0 , '2008-05-05'
INSERT @T SELECT 4, 'b@b.b' , 1, '2008-05-08'
INSERT @T SELECT 5, 'c@c.c' , 0 , '2008-05-05'
IF NOT EXISTS(SELECT 1 FROM @T WHERE email='a@a.a' and address=1 )
select top 1 * from @t order by lastdate desc
IF EXISTS(SELECT 1 FROM @T WHERE email='c@c.c' and address=1 )
select * FROM @T WHERE email='c@c.c' and address=1
ELSE IF (SELECT COUNT(*) FROM @T WHERE email='c@c.c')=1
select * FROM @T WHERE email='c@c.c'
/*id email address lastdate
----------- ---------- ----------- ----------
4 b@b.b 1 2008-05-08
id email address lastdate
----------- ---------- ----------- ----------
5 c@c.c 0 2008-05-05
*/
--sql2000. 又成了同组选一个的问题 了,组合写法太多,不一一写了。
DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME)
INSERT @t SELECT 1,'fcuandy@111.com',0,GETDATE()
UNION ALL SELECT 1,'fcuandy@122.com',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'roy@xx.com',0,GETDATE()
UNION ALL SELECT 2,'roy@yy.com',0,'2008-1-1'
UNION ALL SELECT 3,'haiwer@vv.com',0,GETDATE()
SELECT id,email,lastDate FROM @t a
WHERE NOT EXISTS(SELECT 1 FROM @t WHERE id=a.id AND CASE WHEN address=1 THEN '9999-1-1' ELSE lastDate END > CASE WHEN a.address=1 THEN '9999-1-1' ELSE a.lastDate END)
/*
1 fcuandy@122.com 1 2008-10-07 11:42:54.437
2 roy@xx.com 0 2008-11-07 11:42:54.437
3 haiwer@vv.com 0 2008-11-07 11:42:54.437
*/
SELECT a.* FROM @t a
INNER JOIN
(SELECT id,MAX(CASE WHEN address=1 THEN '9999-1-1' ELSE lastDate END) md FROM @t GROUP BY id) b
ON a.id=b.id AND md=CASE WHEN address=1 THEN '9999-1-1' ELSE lastDate END
/*
1 fcuandy@122.com 2008-10-07 11:39:04.543
2 roy@xx.com 2008-11-07 11:39:04.543
3 haiwer@vv.com 2008-11-07 11:39:04.543
*/
DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME)
INSERT @t SELECT 1,'fcuandy@111.com',0,GETDATE()
UNION ALL SELECT 1,'fcuandy@122.com',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'roy@xx.com',0,GETDATE()
UNION ALL SELECT 2,'roy@yy.com',0,'2008-1-1'
UNION ALL SELECT 3,'haiwer@vv.com',0,GETDATE()
;WITH fc
AS
(
SELECT id,email,CASE WHEN address=1 THEN '9999-1-1' ELSE lastDate END theDay,lastDate FROM @t
)
SELECT id,email,lastDate FROM fc a
WHERE NOT EXISTS(SELECT 1 FROM fc WHERE id=a.id AND theDay > a.theDay)
/*
1 fcuandy@122.com 2008-10-07 11:39:04.543
2 roy@xx.com 2008-11-07 11:39:04.543
3 haiwer@vv.com 2008-11-07 11:39:04.543
*/
declare @id int
set @id=100
select top 1 id,email,address,lastdate
from tb
where id=@id
order by lastdate desc