关于Count的问题,急~

[wysfair]

有表t1结构如下:

AutoID SID STimes SDate
-----------------------
1 s1 1 2009-4-1
2 s2 1 2009-4-1
3 s2 2 2009-4-20
4 s3 1 2009-4-1
10 s4 1 2009-4-1
11 s4 2 2009-4-10
12 s4 3 2009-4-20

当我输入SDate>'2009-3-20' and SDate<'2009-4-15' 时 要得以下(以STimes=最大值的SDate作为条件)

AutoID SID STimes SDate
-----------------------
1 s1 1 2009-4-1
4 s3 1 2009-4-1


当我输入SDate>'2009-3-20' and SDate<'2009-4-25' 时 要得以下(以STimes=最大值的SDate作为条件)

AutoID SID STimes SDate
-----------------------
1 s1 1 2009-4-1
3 s2 2 2009-4-20
4 s3 1 2009-4-1
12 s4 3 2009-4-20

不知道我是否表达清楚了..请求各位大大不吝赐教

[sdhdy]

没说清楚，第一个结果就让人看不懂。

[liangCK]

没看懂.

[rucypli]

select *
from tb T1
where not exists (select 1 from tb T2 wher T1.s1=T2.s1 and T1.sdate<t2.sdate)
and sid not in
(
select sid
from tb
group by sid
where sdate between @date1 and @date2
)

[wysfair]

第一个结果的
AutoID SID STimes SDate
-----------------------
1 s1 1 2009-4-1 //s1只有一条记录,当然是以这条的记录的SDate为条件 且'2009-3-20'>SDate>'2009-4-15'
2 s2 1 2009-4-1 //s2有两记录,要以STtimes=最大的为准,就是下一条
3 s2 2 2009-4-20 //s2以这条为准,但日期不符
4 s3 1 2009-4-1 //s3同第一条
10 s4 1 2009-4-1 //s4有三条,以STimes=3(最大)的记录为准
11 s4 2 2009-4-10 //s4有三条,以STimes=3(最大)的记录为准
12 s4 3 2009-4-20 //s4以这条为准,但日期不符

第二结果就不要解释了...不知道我说清楚没有...汗....

[wysfair]

[Quote=引用 3 楼 rucypli 的回复:]
SQL codeselect *
from tb T1
where not exists (select 1 from tb T2 wher T1.s1=T2.s1 and T1.sdate<t2.sdate)
and sid not in
(
select sid
from tb
group by sid
where sdate between @date1 and @date2
)
[/Quote]
朋友..你那个T1.s1是什么意思啊?是不是写错了?是不是SID?

[sdhdy]

select * from (
select * from t1 a where not exists(select 1 from t1 where sid=a.sid and STimes>a.STimes)) b where SDate>'2009-3-20' and SDate <'2009-4-15' 

[sdhdy]

--6楼是第一个结果。
--这个是第二个结果

select * from (
select * from t1 a where not exists(select 1 from t1 where sid=a.sid and STimes>a.STimes)) b where SDate>'2009-3-20' and SDate <'2009-4-25' 

[sdhdy]

create table t1(AutoID int, SID varchar(5), STimes int, SDate datetime)
go
insert t1 select 1,  's1', 1, '2009-4-1' 
insert t1 select 2 , 's2', 1, '2009-4-1' 
insert t1 select 3 , 's2', 2, '2009-4-20' 
insert t1 select 4,  's3', 1, '2009-4-1' 
insert t1 select 10 ,'s4', 1 ,'2009-4-1' 
insert t1 select 11 ,'s4' ,2, '2009-4-10' 
insert t1 select 12 ,'s4' ,3 ,'2009-4-20' 
go
select AutoID,SID,STimes, SDate=convert(varchar(10),SDate,120) from (
select * from t1 a where not exists(select 1 from t1 where sid=a.sid and STimes>a.STimes)) b where SDate>'2009-3-20' and SDate <'2009-4-15' 
/*
AutoID      SID   STimes      SDate      
----------- ----- ----------- ---------- 
1           s1    1           2009-04-01
4           s3    1           2009-04-01

（所影响的行数为 2 行）
*/
go
select AutoID,SID,STimes, SDate=convert(varchar(10),SDate,120) from (
select * from t1 a where not exists(select 1 from t1 where sid=a.sid and STimes>a.STimes)) b where SDate>'2009-3-20' and SDate <'2009-4-25' 
/*
AutoID      SID   STimes      SDate      
----------- ----- ----------- ---------- 
1           s1    1           2009-04-01
3           s2    2           2009-04-20
4           s3    1           2009-04-01
12          s4    3           2009-04-20

（所影响的行数为 4 行）
*/

go
drop table t1

[等不到来世]

if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([AutoID] int,[SID] varchar(2),[STimes] int,[SDate] datetime)
insert [t1]
select 1,'s1',1,'2009-4-1' union all
select 2,'s2',1,'2009-4-1' union all
select 3,'s2',2,'2009-4-20' union all
select 4,'s3',1,'2009-4-1' union all
select 10,'s4',1,'2009-4-1' union all
select 11,'s4',2,'2009-4-10' union all
select 12,'s4',3,'2009-4-20'
go
--select * from [t1]
--1.
select * from t1 t
where SDate>'2009-3-20' and SDate <'2009-4-15'
and AutoID=(select top 1 AutoID from t1 where sid=t.sid  order by SDate desc)
/*
AutoID      SID  STimes      SDate
----------- ---- ----------- -----------------------
1           s1   1           2009-04-01 00:00:00.000
4           s3   1           2009-04-01 00:00:00.000

(2 行受影响)
*/

--2.
select * from t1 t
where SDate>'2009-3-20' and SDate <'2009-4-25'
and AutoID=(select top 1 AutoID from t1 where sid=t.sid  order by SDate desc)
/*
AutoID      SID  STimes      SDate
----------- ---- ----------- -----------------------
1           s1   1           2009-04-01 00:00:00.000
3           s2   2           2009-04-20 00:00:00.000
4           s3   1           2009-04-01 00:00:00.000
12          s4   3           2009-04-20 00:00:00.000

(4 行受影响)
*/

[wysfair]

结贴

[wysfair]

[Quote=引用 8 楼 sdhdy 的回复:]
SQL codecreate table t1(AutoID int, SID varchar(5), STimes int, SDate datetime)
go
insert t1 select 1, 's1', 1, '2009-4-1'
insert t1 select 2 , 's2', 1, '2009-4-1'
insert t1 select 3 , 's2', 2, '2009-4-20'
insert t1 select 4, 's3', 1, '2009-4-1'
insert t1 select 10 ,'s4', 1 ,'2009-4-1'
insert t1 select 11 ,'s4' ,2, '2009-4-10'
insert t1 select 12 ,'s4' ,3 ,'2009-4-20'
go
selec…
[/Quote]

呵呵,不用写这么长拉..我这边已经有结果了...谢谢哈