怎样写这样的查询语句，解决问题立刻给100分

[cvinx]

表结构是这样的
a, b, c， d
E1 1 off 1
E1 2 on 2
E1 3 on 3
E2 1 off 1
E2 2 off 2
E2 3 off 3
E3 1 on 1
E3 2 on 2
E3 3 on 3

a,b列是主键，
想要对每一个不重复的a查出一条记录
这条记录的条件为：
1.当a所对应的记录中c的值为on的的情况存在时候，查出的记录为b数值最小的那条
2.当a所对应的记录中都为off的时候，查出记录为b数值为1的那条
3.查出的记录中d的值为该a对应的记录中d最大的值

不知道描述的清楚不清楚
以上面的数据为例
查出的结果应该是
a, b, c， d
E1 2 on 3
E2 1 off 3
E3 1 on 3


想了好久也不知道该如何写这个语句，希望能得到各位的指教，非常渴望得到答案
先谢谢大家了

[zlp321002]

--on 存在的时候和全部是on的是否矛盾啊??

[vivianfdlpw]

select t.a
,t.b
,t.c
,(select max(d) from 表 where a=t.a)
from 表 t
where t.b=(
case when exists(select 1 from 表 where a=t.a and c='on')
then (select min(b) from 表 where a=t.a and c='on')
else 1
end
)

[cvinx]

--on 存在的时候和全部是on的是否矛盾啊??

zlp321002(飘过)你好

on 存在的时候和全部是on的时候按同样的情况对待

[vivianfdlpw]

declare @tb table
(
a varchar(10),
b int,
c varchar(10),
d int
)
insert @tb
select 'E1', 1 ,'off', 1 union
select 'E1', 2 ,'on', 2 union
select 'E1', 3 ,'on', 3 union
select 'E2', 1 ,'off', 1 union
select 'E2', 2 ,'off', 2 union
select 'E2', 3 ,'off', 3 union
select 'E3', 1 ,'on', 1 union
select 'E3', 2 ,'on', 2 union
select 'E3', 3 ,'on', 3


--查询
select t.a
,t.b
,t.c
,(select max(d) from @tb where a=t.a) 'd'
from @tb t
where t.b=(
case when exists(select 1 from @tb where a=t.a and c='on')
then (select min(b) from @tb where a=t.a and c='on')
else 1
end
)
order by t.a

--结果
/*
a b c d
---------- ----------- ---------- -----------
E1 2 on 3
E2 1 off 3
E3 1 on 3

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

[cvinx]

谢谢楼上的，能否给一个oracle语法的呢？这个语句需要在两个数据库上执行

[zzit0721]

create table a (a varchar(10),b int,c varchar(10),d int)---测试数据，游标判断法
insert into a select 'E1',1,'off',1
insert into a select 'E1',2,'on',2
insert into a select 'E1',3,'on',3
insert into a select 'E2',1,'off',1
insert into a select 'E2',2,'off',2
insert into a select 'E2',3,'off',3
insert into a select 'E3',1,'on',1
insert into a select 'E3',2,'on',2
insert into a select 'E3',3,'on',3
select *
from a

declare @x table (a varchar(10),b int,c varchar(10),d int)
declare
@a varchar(10)
declare y cursor for
select distinct a from a

open y
fetch next from y into @a

while(@@fetch_status=0)
begin
declare
@b int,
@c varchar(10),
@d int
set @b=0 set @c='' set @d=0
if (select count(*) from a where a=@a and c='on')>(select count(*) from a where a=@a and c='off')
begin
select @b=min(b),@c=c,@d=max(d) from a where a=@a and c='on' group by a ,c
end
else
select @b=min(b),@c=c,@d=max(d) from a where a=@a and c='off' group by a ,c

insert into @x select @a,@b,@c,@d

fetch next from y into @a
end
select *
from @x
close y
deallocate y
drop table a

[zzit0721]

我可以判断出，但某个值的时候，ON的条数多还是OFF条数多
如果ON多就用ON的最小值
如果OFF多就用OFF的最小值

[zoubsky]

--测试代码
declare @tttt table
(
a varchar(10),
b int,
c varchar(10),
d int
)
insert @tttt
select 'E1', 1 ,'off', 1 union
select 'E1', 2 ,'on', 2 union
select 'E1', 3 ,'on', 3 union
select 'E2', 1 ,'off', 1 union
select 'E2', 2 ,'off', 2 union
select 'E2', 3 ,'off', 3 union
select 'E3', 1 ,'on', 1 union
select 'E3', 2 ,'on', 2 union
select 'E3', 3 ,'on', 3


--查询
select t.a
,t.b
,t.c
,(select max(d) from @tttt where a=t.a) 'd'
from @tttt t
where t.b=(
case when exists(select 1 from @tttt where a=t.a and c='on')
then (select top 1 b from @tttt where a=t.a and c='on' order by b asc)
else 1
end
)

/*
测试结果
a b c d
---------- ----------- ---------- -----------
E1 2 on 3
E2 1 off 3
E3 1 on 3
*/

[quhaihua]

create function getb(@str varchar(20))
returns int
as
begin
declare @counton int,@countoff int,@txt int
select @counton=count(*) from newtb where c='on' and a =@str
select @countoff=count(*) from newtb where c='off' and a=@str
if(@counton >0)
begin
select @txt =min(b) from newtb where c='on' and a =@str
end
else if(@countoff =3)
begin
set @txt =1

end
return @txt
end

create function getc(@str varchar(20))
returns varchar(20)
as
begin
declare @counton int,@countoff int,@txtc varchar(20)
select @counton=count(*) from newtb where c='on' and a =@str
if @counton >0
set @txtc = 'on'
else
set @txtc = 'off'
return @txtc
end



select t.a,dbo.getb(t.a) as b,dbo.getc(t.a) as c,(select max(d) from newtb where a =t.a) as d from newtb t group by t.a

[zhao606]

我的可能比较 复杂 但是比较好理解
运行过的 可以达到你的要求
select distinct t.col1,t.col2,t.col3,t.col4
from (select col1,col2=(case when exists (select * from toupiao b
where a.col1=b.col1 and b.col3='on') then (select top 1 col2 from toupiao b
where a.col1=b.col1 and b.col3='on'
order by col4 )
when not exists(select * from toupiao b
where a.col1=b.col1 and b.col3='on') then (select top 1 col2 from toupiao b
where a.col1=b.col1 and b.col3='off' and b.col2=1)
end),
col3=( case when exists (select * from toupiao b
where a.col1=b.col1 and b.col3='on') then 'on'
when not exists (select * from toupiao b
where a.col1=b.col1 and b.col3='on') then 'off'
end ),
col4=( case when exists (select * from toupiao b
where a.col1=b.col1 and b.col3='on') then (select top 1 col4 from toupiao b
where a.col1=b.col1 and b.col3='on'
order by col4 desc)
when not exists(select * from toupiao b
where a.col1=b.col1 and b.col3='on') then (select top 1 col4 from toupiao b
where a.col1=b.col1 and b.col3='off'
order by col4 desc)
end)
from toupiao a) t