求一条sql语句,内详
表
A B C
1 张 2007-01-01
2 王 2007-01-02
3 赵 2007-01-03
4 李 2007-01-04
5 赵 2007-01-05
6 李 2007-01-06
7 张 2007-01-07
8 李 2007-01-08
9 赵 2007-01-09
最终结果
A B C
2 王 2007-01-02
3 赵 2007-01-03
4 李 2007-01-04
3 赵 2007-01-03
查询条件 C 2007-01-02 到 2007-01-08之间[包括等于]
查询过程
1、先根据B 来 查询不重复的 这个时候要求C最小 [也就是这个人第一次出现的时间]
2、在1、的结果之上应用条件C 2007-01-02 到 2007-01-08之间[包括等于]
请问这种发训如何写?
------解决方案--------------------select * from 表 as a where not exists(select 1 from 表 where B=a.B and c <a.c)
----方法2:
select * from 表 as a where c= (select min(c) from 表 where B= a.B)
order by B
----方法3:
select a.* from 表 as a inner join (select B,min(C) as C from 表 group by B) as b
on b.B= a.B and a.C= b.C order by a.B
------解决方案--------------------create table t(id int identity, xm varchar(100),date datetime)
insert into t(xm,date)values( '张 ', '2007-07-01 ')
insert into t(xm,date)values( '张 ', '2007-07-02 ')
insert into t(xm,date)values( '王 ', '2007-07-01 ')
insert into t(xm,date)values( '王 ', '2007-07-03 ')
select * from t as a where exists(select top 1 * from t where a.xm=xm and a.date <date)
------解决方案--------------------select * from 表 as a 什么意思?
not exists?
exists?
----
用不存在 a.c> c, 就相当于a.c <=c
------解决方案--------------------有必要那么复杂吗?
create table t(id int identity, xm varchar(100),date datetime)
insert t values( '张 ', '2007-01-01 ')
insert t values( '王 ', '2007-01-02 ')
insert t values( '赵 ', '2007-01-03 ')
insert t values( '李 ', '2007-01-04 ')
insert t values( '赵 ', '2007-01-05 ')
insert t values( '李 ', '2007-01-06 ')
insert t values( '张 ', '2007-01-07 ')
insert t values( '李 ', '2007-01-08 ')
insert t values( '赵 ', '2007-01-09 ')
------------------------------
第一个查询:
select xm,min(date) as date from t group by xm order by date
第二个:
select xm,min(date) as date from t
where date> = '2007-01-02 ' and date <dateadd(day,1, '2007-01-08 ')
group by xm
order by date
------解决方案--------------------看错了你的意思,修正SQL语句如下:
select xm,min(date) as date into #temp from t
group by xm
select * from #temp where date> = '2007-01-02 ' and date <dateadd(day,1, '2007-01-08 ')
drop table #temp