急！急！单表查询问题！在线等

[hahahyOo]

id email address lastdate
id email 地址 最后更新时间
int nvarchar int(0/1) datetime

1:地址：０备用地址　　１默认地址　　　
2:没有默认地址，最后更新时间设为默认地址
3:只有一条记录，取这一条
4:取得多条记录，取最后更新时间
5:id为指定id

先谢谢！

[hahahyOo]

没人么？
自己顶啊

[水族杰纶]

[Quote=引用楼主 hahahyOo 的帖子:]
id email address lastdate
id email 地址 最后更新时间
int nvarchar int(0/1) datetime

1:地址：０备用地址　　１默认地址　　　
2:没有默认地址，最后更新时间设为默认地址
3:只有一条记录，取这一条
4:取得多条记录，取最后更新时间
5:id为指定id

先谢谢！
[/Quote]
看的暈暈的
貼數據及結果~~

[chenjunsheep]

什么意思,我MS来到了火星..

[等不到来世]

declare @id int
set @id=100

select top 1 id,email,address,lastdate 
from tb
where id=@id
order by lastdate desc

[hahahyOo]

更正：根据email 查询
id email address lastdate
1 a@a.a 0 2008-05-05
2 a@a.a 0 2008-05-07
3 b@b.b 0 2008-05-05
4 b@b.b １ 2008-05-08
５ c@c.c 0 2008-05-05
______________________________

查a@a.a的数据：
第一种情况：
１.没有address为1 数据　那么　我就取lastdate 为最大的
结果就是
a@a.a 0 2008-05-07

第二种情况：
查c@c.c的数据
１.有address 为１　那么就取这条数据
b@b.b １ 2008-05-08

第三种情况：
查c@c.c的数据：
１.如果只有一条，那我就取这条．
结果就是：

c@c.c 0 2008-05-05

[fcuandy]

DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME)
INSERT @t SELECT 1,'[e-mail]',0,GETDATE()
UNION ALL SELECT 1,'[e-mail]',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'[e-mail]',0,GETDATE()
UNION ALL SELECT 2,'[e-mail]',0,'2008-1-1'
UNION ALL SELECT 3,'[e-mail]',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	[e-mail]	2008-10-07 11:39:04.543
2	[e-mail]	2008-11-07 11:39:04.543
3	[e-mail]	2008-11-07 11:39:04.543
*/

[hahahyOo]

表达不好．．莫怪

[fcuandy]

--sql2000. 又成了同组选一个的问题 了，组合写法太多，不一一写了。

DECLARE @t TABLE(id INT,email NVARCHAR(100),address INT CHECK(address IN(0,1) /*并且程序逻辑上控制一个人只能有一个默认地址*/),lastDate DATETIME)
INSERT @t SELECT 1,'[e-mail]',0,GETDATE()
UNION ALL SELECT 1,'[e-mail]',1,DATEADD(mm,-1,GETDATE())
UNION ALL SELECT 2,'[e-mail]',0,GETDATE()
UNION ALL SELECT 2,'[e-mail]',0,'2008-1-1'
UNION ALL SELECT 3,'[e-mail]',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	[e-mail]	1	2008-10-07 11:42:54.437
2	[e-mail]	0	2008-11-07 11:42:54.437
3	[e-mail]	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	[e-mail]	2008-10-07 11:39:04.543
2	[e-mail]	2008-11-07 11:39:04.543
3	[e-mail]	2008-11-07 11:39:04.543
*/

[hahahyOo]

非常感谢　fcuandy
我先看看．呵

[水族杰纶]

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
*/
   

[等不到来世]

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)
*/

[Herb2]

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 

[hahahyOo]

问题解决：
非常谢谢各位的解答
szx1999
wufeng4552
fcuandy
．．．