DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME) INSERT @t SELECT 1,'[email protected]',0,GETDATE() UNION ALL SELECT 1,'[email protected]',1,DATEADD(mm,-1,GETDATE()) UNION ALL SELECT 2,'[email protected]',0,GETDATE() UNION ALL SELECT 2,'[email protected]',0,'2008-1-1' UNION ALL SELECT 3,'[email protected]',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 [email protected] 2008-10-07 11:39:04.543 2 [email protected] 2008-11-07 11:39:04.543 3 [email protected] 2008-11-07 11:39:04.543 */
--sql2000. 又成了同组选一个的问题 了,组合写法太多,不一一写了。DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME) INSERT @t SELECT 1,'[email protected]',0,GETDATE() UNION ALL SELECT 1,'[email protected]',1,DATEADD(mm,-1,GETDATE()) UNION ALL SELECT 2,'[email protected]',0,GETDATE() UNION ALL SELECT 2,'[email protected]',0,'2008-1-1' UNION ALL SELECT 3,'[email protected]',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 [email protected] 1 2008-10-07 11:42:54.437 2 [email protected] 0 2008-11-07 11:42:54.437 3 [email protected] 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 [email protected] 2008-10-07 11:39:04.543 2 [email protected] 2008-11-07 11:39:04.543 3 [email protected] 2008-11-07 11:39:04.543 */
非常感谢 fcuandy 我先看看.呵
DECLARE @T TABLE (id INT, email VARCHAR(10) , address INT, lastdate VARCHAR(10)) INSERT @T SELECT 1, '[email protected]' , 0 , '2008-05-05' INSERT @T SELECT 2, '[email protected]' , 0 , '2008-05-07' INSERT @T SELECT 3, '[email protected]' , 0 , '2008-05-05' INSERT @T SELECT 4, '[email protected]' , 1, '2008-05-08' INSERT @T SELECT 5, '[email protected]' , 0 , '2008-05-05' IF NOT EXISTS(SELECT 1 FROM @T WHERE email='[email protected]' and address=1 ) select top 1 * from @t order by lastdate desc IF EXISTS(SELECT 1 FROM @T WHERE email='[email protected]' and address=1 ) select * FROM @T WHERE email='[email protected]' and address=1 ELSE IF (SELECT COUNT(*) FROM @T WHERE email='[email protected]')=1 select * FROM @T WHERE email='[email protected]' /*id email address lastdate ----------- ---------- ----------- ---------- 4 [email protected] 1 2008-05-08id email address lastdate ----------- ---------- ----------- ---------- 5 [email protected] 0 2008-05-05 */
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,'[email protected]',0,'2008-05-05' union all select 2,'[email protected]',0,'2008-05-07' union all select 3,'[email protected]',0,'2008-05-05' union all select 4,'[email protected]',1,'2008-05-08' union all select 5,'[email protected]',0,'2008-05-05'--select * from [tb]declare @email varchar(10) set @email='[email protected]' --set @email='[email protected]' --set @email='[email protected]'select top 1 email,address,lastdate from tb where email=@email order by address desc,lastdate desc --测试结果: /* email address lastdate ---------- ----------- -------------------- [email protected] 0 2008-05-07(1 row(s) affected) */
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
id email address lastdate
1 [email protected] 0 2008-05-05
2 [email protected] 0 2008-05-07
3 [email protected] 0 2008-05-05
4 [email protected] 1 2008-05-08
5 [email protected] 0 2008-05-05
______________________________查[email protected]的数据:
第一种情况:
1.没有address为1 数据 那么 我就取lastdate 为最大的
结果就是
[email protected] 0 2008-05-07第二种情况:
查[email protected]的数据
1.有address 为1 那么就取这条数据
[email protected] 1 2008-05-08第三种情况:
查[email protected]的数据:
1.如果只有一条,那我就取这条.
结果就是:[email protected] 0 2008-05-05
INSERT @t SELECT 1,'[email protected]',0,GETDATE()
UNION ALL SELECT 1,'[email protected]',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'[email protected]',0,GETDATE()
UNION ALL SELECT 2,'[email protected]',0,'2008-1-1'
UNION ALL SELECT 3,'[email protected]',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 [email protected] 2008-10-07 11:39:04.543
2 [email protected] 2008-11-07 11:39:04.543
3 [email protected] 2008-11-07 11:39:04.543
*/
INSERT @t SELECT 1,'[email protected]',0,GETDATE()
UNION ALL SELECT 1,'[email protected]',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'[email protected]',0,GETDATE()
UNION ALL SELECT 2,'[email protected]',0,'2008-1-1'
UNION ALL SELECT 3,'[email protected]',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 [email protected] 1 2008-10-07 11:42:54.437
2 [email protected] 0 2008-11-07 11:42:54.437
3 [email protected] 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 [email protected] 2008-10-07 11:39:04.543
2 [email protected] 2008-11-07 11:39:04.543
3 [email protected] 2008-11-07 11:39:04.543
*/
我先看看.呵
INSERT @T SELECT 1, '[email protected]' , 0 , '2008-05-05'
INSERT @T SELECT 2, '[email protected]' , 0 , '2008-05-07'
INSERT @T SELECT 3, '[email protected]' , 0 , '2008-05-05'
INSERT @T SELECT 4, '[email protected]' , 1, '2008-05-08'
INSERT @T SELECT 5, '[email protected]' , 0 , '2008-05-05'
IF NOT EXISTS(SELECT 1 FROM @T WHERE email='[email protected]' and address=1 )
select top 1 * from @t order by lastdate desc
IF EXISTS(SELECT 1 FROM @T WHERE email='[email protected]' and address=1 )
select * FROM @T WHERE email='[email protected]' and address=1
ELSE IF (SELECT COUNT(*) FROM @T WHERE email='[email protected]')=1
select * FROM @T WHERE email='[email protected]'
/*id email address lastdate
----------- ---------- ----------- ----------
4 [email protected] 1 2008-05-08id email address lastdate
----------- ---------- ----------- ----------
5 [email protected] 0 2008-05-05
*/
go
create table [tb]([id] int,[email] varchar(10),[address] int,[lastdate] varchar(20))
insert [tb]
select 1,'[email protected]',0,'2008-05-05' union all
select 2,'[email protected]',0,'2008-05-07' union all
select 3,'[email protected]',0,'2008-05-05' union all
select 4,'[email protected]',1,'2008-05-08' union all
select 5,'[email protected]',0,'2008-05-05'--select * from [tb]declare @email varchar(10)
set @email='[email protected]'
--set @email='[email protected]'
--set @email='[email protected]'select top 1 email,address,lastdate
from tb
where email=@email
order by address desc,lastdate desc
--测试结果:
/*
email address lastdate
---------- ----------- --------------------
[email protected] 0 2008-05-07(1 row(s) affected)
*/
group by id,mail,address
非常谢谢各位的解答
szx1999
wufeng4552
fcuandy
...