select top 1 a.*
from table1 a,table2 b
where a.code=b.code
and a.b<b.d
and a.c<b.e
and a.b=(select max(b) from table1 where code=a.code and b<b.d and c<b.e)
and a.c=(select max(c) from table1 where code=a.code and b=a.b and c<b.e)
and a.a=(select max(a) from table1 where code=a.code and b=a.b and c=a.c)
from table1 a,table2 b
where a.code=b.code
and a.b<b.d
and a.c<b.e
and a.b=(select max(b) from table1 where code=a.code and b<b.d and c<b.e)
and a.c=(select max(c) from table1 where code=a.code and b=a.b and c<b.e)
and a.a=(select max(a) from table1 where code=a.code and b=a.b and c=a.c)
解决方案 »
- ****************讨论一个问题.数据表一定要有主键吗?一定要建关系吗?请说说你的理由******************
- 关于MSSQL2000 权限问题:
- 求一高难度的sql
- SQL 怎么连接两个 查询结果??????
- 一个库添加了两个用户,结果当一个用户使用库时候,另外一个用户无法正常使用?
- 我要按一定的汉字 排序,该如何处理?
- 关于盗版的问题,win2k 高级服务器版,和SQLSERVER 企业版兄弟们一定要帮忙啊
- 数据表多对多关系 是用关联表还是用字段拼接?
- 在vb中用 *.sql初始化数据库失败!但可读写数据库的呀!!!急!
- 紧急求助:WinXP安装SQL2000!!!!!!!!
- 如何取得一个数组里面元素的个数?
- 在SQL语句中对URL进行分解的问题,请给完美出源代码,100分相送
go
insert table1 select 22,'20040304',35,'1-5B'
union all select 23,'20040304',35,'1-5B'
union all select 27,'20040308',40,'1-5B'
union all select 45,'20040708',45,'1-5B'
go
insert table1 select 52,'20040304',35,'2-5B'
union all select 53,'20040304',40,'2-5B'
union all select 57,'20040308',36,'2-5B'
union all select 55,'20040708',45,'2-5B'
gocreate table table2(D char(8),E int,code char(4))
go
insert table2 select '20040508',37,'1-5B'
go
insert table2 select '20040708',38,'2-5B'
go--查询
select a.*
from table1 a,table2 b
where a.code=b.code
and a.b<b.d
and a.c<b.e
and a.b=(select max(b) from table1 where code=a.code and b<b.d and c<b.e)
and a.c=(select max(c) from table1 where code=a.code and b=a.b and c<b.e)
and a.a=(select max(a) from table1 where code=a.code and b=a.b and c=a.c)
go--结果:
/*
A B C code
----------- -------- ----------- ----
23 20040304 35 1-5B
57 20040308 36 2-5B(所影响的行数为 2 行)*/
insert into #t1
-- A B C code
select 22 , '20040304' , 35 , '1-5B' union
select 23 , '20040304' ,35 , '1-5B' union
select 27 , '20040308' , 40 ,'1-5B' union
select 45, '20040708' ,45 , '1-5B'
--drop table #t1
create table #t2(d datetime ,e int ,code char(5) )
--有表table2结构和数据如下
--D E code
insert into #t2 select '20040508', 37 , '1-5B'
-----------------------------------------------------------
select * from ( select a=min(a) ,b,c ,code from #t1 group by b,c ,code ) a
where exists ( select 1 from #t2 where code=a.code and d>a.b and E>a.c )
--test
(影響 4 個資料列)
(影響 1 個資料列)a b c code
----------- ------------------------------------------------------ ----------- -----
22 2004-03-04 00:00:00.000 35 1-5B (影響 1 個資料列)
-------------------------------------------
create table #t1(a int , b datetime ,c int ,code char(5))
insert into #t1
-- A B C code
select 22 , '20040304' , 35 , '1-5B' union
select 23 , '20040304' ,35 , '1-5B' union
select 27 , '20040308' , 40 ,'1-5B' union
select 45, '20040708' ,45 , '1-5B'
--drop table #t1
create table #t2(d datetime ,e int ,code char(5) )
--有表table2结构和数据如下
--D E code
insert into #t2 select '20040508', 37 , '1-5B'
-----------------------------------------------------------
select * from ( select a=Max(a) ,b,c ,code from #t1 group by b,c ,code ) a
where exists ( select 1 from #t2 where code=a.code and d>a.b and E>a.c )drop table #t1
drop table #t2 -------------------------------------
(影響 4 個資料列)
(影響 1 個資料列)a b c code
----------- ------------------------------------------------------ ----------- -----
23 2004-03-04 00:00:00.000 35 1-5B (影響 1 個資料列)
select A=max(a.A),B,C
from table1 a join table2 b on a.code=b.code and a.B<b.D and a.C<b.E
group by B,C
select top 1 x.a,x.b,x.c from table1 x,(select d,e,code from table2) y where x.code=y.code and x.b<y.d and x.c<y.e order by x.b desc,x.c desc,x.a desc
create table table1(A int,B char(8),C int,code char(4))
insert table1 select 22,'20040304',35,'1-5B'
union all select 23,'20040304',35,'1-5B'
union all select 27,'20040308',40,'1-5B'
union all select 45,'20040708',45,'1-5B'create table table2(D char(8),E int,code char(4))
insert table2 select '20040508',37,'1-5B'
go--查询
select A=max(a.A),a.B,a.C --B,C相同,取A最大的
from table1 a,(--B相同的,取C最大
select a.code,a.B,C=max(a.C)
from table1 a,(--满足条件的,日期B最大
select a.code,B=max(a.B)
from table1 a,table2 b
where a.code=b.code
and a.B<b.D
and a.C<b.E
group by a.code
)b where a.code=b.code and a.B=b.B
group by a.code,a.B
)b where a.code=b.code and a.B=b.B and a.C=a.C
group by a.code,a.B,a.C
go--删除测试
drop table table1,table2/*--测试结果A B C
----------- -------- -----------
23 20040304 35(所影响的行数为 1 行)
--*/
insert table1 select 22,'20040304',35,'1-5B'
union all select 23,'20040304',35,'1-5B'
union all select 27,'20040308',40,'1-5B'
union all select 45,'20040708',45,'1-5B'create table table2(D char(8),E int,code char(4))
insert table2 select '20040508',37,'1-5B'
go
select max(L.a) as a,L.b,L.c,L.code
from table1 L join
(
select b,max(c) as c,L.code from table1 L join table2 R
on L.code=R.code and L.c<R.e and L.b<R.d
group by b,L.code
) R
on L.b=R.b and L.c=R.c and L.code=R.code
group by L.b,L.c,L.codedrop table table1,table2
select top 1 * from table1 where B<=(select D from table2 where code=table1.code) and C<=(select E from table2 where code=table1.code) order by B,C,(1-A) asc
select top 1 * from table1 where B<=(select D from table2 where code=table1.code) and C<=(select E from table2 where code=table1.code) order by B,C,A desc