create table tb(a int,b int,c int,d int,e int, f int,g int)
insert into tb values(1,2,3,2,1,1,20)
insert into tb values(2,2,3,3,1,2,20)
insert into tb values(3,2,3,4,1,1,60)
insert into tb values(4,2,3,4,1,0,30)
insert into tb values(5,2,3,5,1,2,50)
insert into tb values(6,2,3,5,1,3,50)
insert into tb values(7,5,3,2,1,4,20)
insert into tb values(8,4,3,2,1,5,20)
表tb中 a,g是主建
现在想取b,c,d,e,f这5个子段 导入道另一个表tb1中,tb1的主建 是b,c,d,e 如果tb表中b,c,d,e相同时,比较g,取g大的那条,如果g也相同,比较a,取a大的那条谢谢了得到的结果是2 3 2 1 1
2 3 3 1 2
2 3 4 1 1
2 3 5 1 3
5 3 2 1 4
4 3 2 1 5大哥们 谢谢你们了
解决方案 »
- 查询设置
- The query cannet be executed because some files are either missing or not registered.
- 查询分析器 中直接select EXCEL表中的数据 问题?
- 无法处理了,请高手指明
- 这个复杂的SQL语句怎么写?高手请进!
- 请教sql server表空间如何回收
- 关于排序规则Collation
- asp与sql连接,windows认证方式如何连接?
- 大力老兄:我想将以前备份的数据拷到另一台机子上,怎么办?
- 在sql语句中如何使用IsNull()?——急!
- 求助!SQLServer2000触发器编写!
- 太无聊了,问一个问题,如果把两个表无条件的放在一个表里面!
create table tb(a int,b int,c int,d int,e int, f int,g int)
insert into tb values(1,2,3,2,1,1,20)
insert into tb values(2,2,3,3,1,2,20)
insert into tb values(3,2,3,4,1,1,60)
insert into tb values(4,2,3,4,1,0,30)
insert into tb values(5,2,3,5,1,2,50)
insert into tb values(6,2,3,5,1,3,50)
insert into tb values(7,5,3,2,1,4,20)
insert into tb values(8,4,3,2,1,5,20)
insert into tb values(9,4,3,2,1,6,20)select * from
(
select distinct m.b,m.c,m.d,m.e,m.f
from tb m
inner join (
select b,c,d,e from tb group by b,c,d,e having count(*)=1)n
on m.b=n.b and m.c=n.c and m.d=n.d and m.e=n.e union all select distinct b,c,d,e,f
from tb m
where exists (select 1 from tb n where m.b=n.b and m.c=n.c and m.d=n.d and m.e=n.e and m.g>n.g)
union all select distinct b,c,d,e,f
from tb m
where exists (select 1 from tb n where m.b=n.b and m.c=n.c and m.d=n.d and m.e=n.e and m.g=n.g and m.a>n.a ))t
insert into @tb values(1,2,3,2,1,1,20)
insert into @tb values(2,2,3,3,1,2,20)
insert into @tb values(3,2,3,4,1,1,60)
insert into @tb values(4,2,3,4,1,0,30)
insert into @tb values(5,2,3,5,1,2,50)
insert into @tb values(6,2,3,5,1,3,50)
insert into @tb values(7,5,3,2,1,4,20)
insert into @tb values(8,4,3,2,1,5,20)select b,c,d,e,f from @tb a
where a=(select top 1 a from @tb b where b=a.b and c=a.c and d=a.d and e=a.e order by (g+a) desc)
/*
2 3 2 1 1
2 3 3 1 2
2 3 4 1 1
2 3 5 1 3
5 3 2 1 4
4 3 2 1 5
*/
insert into @tb values(1,2,3,2,1,1,20)
insert into @tb values(2,2,3,3,1,2,20)
insert into @tb values(3,2,3,4,1,1,60)
insert into @tb values(4,2,3,4,1,0,30)
insert into @tb values(5,2,3,5,1,2,50)
insert into @tb values(6,2,3,5,1,3,50)
insert into @tb values(7,5,3,2,1,4,20)
insert into @tb values(8,4,3,2,1,5,20)select b,c,d,e,f from @tb a
where a=(select top 1 a from @tb b where b=a.b and c=a.c and d=a.d and e=a.e order by (g+a) desc)
/*
2 3 2 1 1
2 3 3 1 2
2 3 4 1 1
2 3 5 1 3
5 3 2 1 4
4 3 2 1 5
*/
insert into @tb values(3,2,3,4,1,1,60)
insert into @tb values(5,2,3,4,1,0,59)select b,c,d,e,f from @tb a
where a=(select top 1 a from @tb b where b=a.b and c=a.c and d=a.d and e=a.e order by (g+a) desc)
/*
b c d e f
2 3 4 1 0
*/
if object_id('tempdb..#t') is not null drop table #T
create table #t(a int,b int,c int,d int,e int, f int,g int)
insert into #t values(1,2,3,2,1,1,20)
insert into #t values(2,2,3,3,1,2,20)
insert into #t values(3,2,3,4,1,1,60)
insert into #t values(4,2,3,4,1,0,30)
insert into #t values(5,2,3,5,1,2,50)
insert into #t values(6,2,3,5,1,3,50)
insert into #t values(7,5,3,2,1,4,20)
insert into #t values(8,4,3,2,1,5,20)
set nocount offselect b,c,d,e,f from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g=t.g and a<t.a)) x
where 1> (select count(1) from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g=t.g and a<t.a)) z
where z.b=x.b and z.c=x.c and z.d=x.d and z.e=x.e and z.g<x.g)/*
b c d e f
2 3 2 1 1
2 3 3 1 2
2 3 4 1 0
2 3 5 1 2
5 3 2 1 4
4 3 2 1 5
*/
上面的写反了
set nocount on
if object_id('tempdb..#t') is not null drop table #T
create table #t(a int,b int,c int,d int,e int, f int,g int)
insert into #t values(1,2,3,2,1,1,20)
insert into #t values(2,2,3,3,1,2,20)
insert into #t values(3,2,3,4,1,1,60)
insert into #t values(4,2,3,4,1,0,30)
insert into #t values(5,2,3,5,1,2,50)
insert into #t values(6,2,3,5,1,3,50)
insert into #t values(7,5,3,2,1,4,20)
insert into #t values(8,4,3,2,1,5,20)
set nocount offselect b,c,d,e,f from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g=t.g and a>t.a)) x
where 1> (select count(1) from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g=t.g and a>t.a)) z
where z.b=x.b and z.c=x.c and z.d=x.d and z.e=x.e and z.g>x.g)/*
b c d e f
2 3 2 1 1
2 3 3 1 2
2 3 4 1 1
2 3 5 1 3
5 3 2 1 4
4 3 2 1 5
*/
if object_id('tempdb..#t') is not null drop table #T
create table #t(a int,b int,c int,d int,e int, f int,g int)
insert into #t values(1,2,3,2,1,1,20)
insert into #t values(2,2,3,3,1,2,20)
insert into #t values(3,2,3,4,1,1,60)
insert into #t values(4,2,3,4,1,0,30)
insert into #t values(5,2,3,5,1,2,50)
insert into #t values(6,2,3,5,1,3,50)
insert into #t values(7,5,3,2,1,4,20)
insert into #t values(8,4,3,2,1,5,20)
set nocount offselect b,c,d,e,f from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g>t.g)) x
where 1> (select count(1) from
(select * from #t t where not exists(select 1 from #t where b=t.b and c=t.c and d=t.d and e=t.e and g>t.g)) z
where z.b=x.b and z.c=x.c and z.d=x.d and z.e=x.e and z.g=x.g and z.a>x.a )/*
b c d e f
2 3 2 1 1
2 3 3 1 2
2 3 4 1 1
2 3 5 1 3
5 3 2 1 4
4 3 2 1 5
*/