select AMCRINO , AMCD , AMCRIORD from T004ApldMdlCri a where AMCD =(select max(AMCD) from T004ApldMdlCri where AMCRINO=a.AMCRINO and AMCRIORD= (select max(AMCRIORD) from T004ApldMdlCri where AMCRINO=a.AMCRINO) );这个方法只返回AMCRINO 不重复的纪录 AMCD , AMCRIORD 这两列没有
to jcq(疯子弟) 列于列无关那有这个问题 如果三个条件的记录数目不相同,那就无法正常显示
select distinct a.AMCRINO , b.AMCD , c.AMCRIORD from T004ApldMdlCri a,T004ApldMdlCri b,T004ApldMdlCri c
昨天ORACLE版有位兄弟告诉我一个方法可以实现,但纪录较少。 SELECT a.ao, b.am, c.ad from ( select max(rowid) id, AMCRINO ao from T004ApldMdlCri group by AMCRINO ) a, ( select max(rowid) id, AMCD am from T004ApldMdlCri group by AMCD ) b, ( select max(rowid) id, AMCRIORD ad from T004ApldMdlCri group by AMCRIORD ) c WHERE a.id=b.id AND a.id=c.id ;
TO xmvigour(微电--有狼的气质 没狼的勇气 真郁闷) 欢迎讨论,但你的方法根本不对
TO jcq(疯子弟) 不是那样的结果是 1 2 3 2 3 4 3 4 5 4 5 6
select AMCRINO,AMCD,T004ApldMdlCri from T004ApldMdlCri where AMCRINO in (select distinct AMCRINO from T004ApldMdlCri) and AMCD in (select distinct AMCD from T004ApldMdlCri) and T004ApldMdlCri in (select distinct T004ApldMdlCri from T004ApldMdlCri)
select * from tablename a where AMCD =(select max(AMCD) from table1 where AMCRINO=a.AMCRINO and AMCRIORD=(select max(AMCRIORD) from table1 where AMCRINO=a.AMCRINO) )
to : beyond_xiruo(离开csdn)你说的很对,就是这个难题,但这个方法好用,但纪录少了点,这是必然的, 现在是有没有完善一点的方法?昨天ORACLE版有位兄弟告诉我一个方法可以实现,但纪录较少。 SELECT a.ao, b.am, c.ad from ( select max(rowid) id, AMCRINO ao from T004ApldMdlCri group by AMCRINO ) a, ( select max(rowid) id, AMCD am from T004ApldMdlCri group by AMCD ) b, ( select max(rowid) id, AMCRIORD ad from T004ApldMdlCri group by AMCRIORD ) c WHERE a.id=b.id AND a.id=c.id ;
呵呵我的不对 你的数据库是什么?如果oracle的好办一点select distinct a.AMCRINO , b.AMCD , c.AMCRIORD from (select distinct AMCRINO , AMCD , AMCRIORD ,rownum id from T004ApldMdlCri) d, (select distinct AMCRINO,rownum id from T004ApldMdlCri) a, (select distinct AMCD,rownum id from T004ApldMdlCri) b, (select distinct AMCRIORD,rownum id from T004ApldMdlCri) c where d.id+=b.id and d.id+=c.id and d.id+=a.id
select distinct nvl(a.AMCRINO,' ') , nvl(b.AMCD,' ') , nvl(c.AMCRIORD,' ') from (select distinct AMCRINO , AMCD , AMCRIORD ,rownum id from T004ApldMdlCri) d, (select distinct AMCRINO,rownum id from T004ApldMdlCri) a, (select distinct AMCD,rownum id from T004ApldMdlCri) b, (select distinct AMCRIORD,rownum id from T004ApldMdlCri) c where d.id(+)=b.id and d.id(+)=c.id and d.id(+)=a.id and (d.id<=nvl(a.id,0) or (d.id<=nvl(b.id,0) or d.id<=nvl(c.id,0))第一行的nvl里面的默认值你根据你的具体类型改一下 应该没问题
第一行的nvl里面的默认值是因为如果该列没记录应该显示什么 d是取出看看有几条记录 abc分别取出各自不重复的记录还有一个rownum从一开始在查询中d就是协调一下 一条记录应该按那个id从1开始连接 最后一个(d.id<=nvl(a.id,0) or (d.id<=nvl(b.id,0) or d.id<=nvl(c.id,0)) 是为了把d中多余的id记录过虑掉 因为我这里没有oracle 所以有些语句可能有问题 但思路应该没错
再试试下面的SQL,返回的记录可能会多一些,其思想是 先保证AMCRINO不重复,有重复的取rowid最大的,然后在此基础上保证 AMCD,然后是AMCRIORD,你可以根据实际情况,选择筛选的顺序,比如记录 重复的情况。SELECT f.AMCRINO ao, f.AMCD am, f.AMCRIORD ad from ( select max(rowid) id from ( select max(rowid) id from ( select max(rowid) id from T004ApldMdlCri group by AMCRINO ) a, T004ApldMdlCri b where a.id=b.rowid group by b.AMCD ) c, T004ApldMdlCri d ) where c.id = d.rowid group by b.AMCRIORD ) e, T004ApldMdlCri f where f.rowid=e.id;
to Lastdrop(空杯) 你的思想很对,但是如果返回列长度不同那数据保证不全这个问题一句SQL解决不了吗?
怎么简单怎么实现,没有必要去苛求必须要一条sql语句去解决所有问题。
select * from too4apldmdlcri rowid in (select max(rowid) from (select a.amcrino,a.amcd from (select amcrino,amcd,amcriord from too4apldmdlcri group by amcrino,amcd,amcriord) a group by a.amcrino,a.amcd) b group by b.amcrino ;
select AMCRINO , AMCD , AMCRIORD from T004ApldMdlCri a
where AMCD =(select max(AMCD) from T004ApldMdlCri where AMCRINO=a.AMCRINO and AMCRIORD=
(select max(AMCRIORD) from T004ApldMdlCri where AMCRINO=a.AMCRINO) );这个方法只返回AMCRINO 不重复的纪录 AMCD , AMCRIORD 这两列没有
如果三个条件的记录数目不相同,那就无法正常显示
SELECT a.ao, b.am, c.ad from
( select max(rowid) id, AMCRINO ao from T004ApldMdlCri
group by AMCRINO ) a,
( select max(rowid) id, AMCD am from T004ApldMdlCri
group by AMCD ) b,
( select max(rowid) id, AMCRIORD ad from T004ApldMdlCri
group by AMCRIORD ) c
WHERE a.id=b.id AND a.id=c.id ;
1 2 3
2 3 4
3 4 5
4 5 6
where AMCD =(select max(AMCD) from table1 where AMCRINO=a.AMCRINO and AMCRIORD=(select max(AMCRIORD) from table1 where AMCRINO=a.AMCRINO) )
你是回答过了,但是不好用,只有第一列不重复
如果三个条件的记录数目不相同,那就无法正常显示
原来那个方法的根本无法满足条件,当三个条件得到的结果不相同的话,是以记录数最多的那一列为标准,将另外两列的相应字段填满
-------------------------------------------
在上个帖子里我都说过,完全不相关的记录结果集在同意个sql语句里是无法被正常打印的,我上面写那个方法也是相关的,就是把条件加到最苛刻那种
现在是有没有完善一点的方法?昨天ORACLE版有位兄弟告诉我一个方法可以实现,但纪录较少。
SELECT a.ao, b.am, c.ad from
( select max(rowid) id, AMCRINO ao from T004ApldMdlCri
group by AMCRINO ) a,
( select max(rowid) id, AMCD am from T004ApldMdlCri
group by AMCD ) b,
( select max(rowid) id, AMCRIORD ad from T004ApldMdlCri
group by AMCRIORD ) c
WHERE a.id=b.id AND a.id=c.id ;
from (select distinct AMCRINO , AMCD , AMCRIORD ,rownum id from T004ApldMdlCri) d,
(select distinct AMCRINO,rownum id from T004ApldMdlCri) a,
(select distinct AMCD,rownum id from T004ApldMdlCri) b,
(select distinct AMCRIORD,rownum id from T004ApldMdlCri) c
where d.id+=b.id and d.id+=c.id and d.id+=a.id
from (select distinct AMCRINO , AMCD , AMCRIORD ,rownum id from T004ApldMdlCri) d,
(select distinct AMCRINO,rownum id from T004ApldMdlCri) a,
(select distinct AMCD,rownum id from T004ApldMdlCri) b,
(select distinct AMCRIORD,rownum id from T004ApldMdlCri) c
where d.id(+)=b.id and d.id(+)=c.id and d.id(+)=a.id
and (d.id<=nvl(a.id,0) or (d.id<=nvl(b.id,0) or d.id<=nvl(c.id,0))第一行的nvl里面的默认值你根据你的具体类型改一下 应该没问题
d是取出看看有几条记录
abc分别取出各自不重复的记录还有一个rownum从一开始在查询中d就是协调一下 一条记录应该按那个id从1开始连接
最后一个(d.id<=nvl(a.id,0) or (d.id<=nvl(b.id,0) or d.id<=nvl(c.id,0))
是为了把d中多余的id记录过虑掉 因为我这里没有oracle 所以有些语句可能有问题 但思路应该没错
先保证AMCRINO不重复,有重复的取rowid最大的,然后在此基础上保证
AMCD,然后是AMCRIORD,你可以根据实际情况,选择筛选的顺序,比如记录
重复的情况。SELECT f.AMCRINO ao, f.AMCD am, f.AMCRIORD ad from
( select max(rowid) id from
( select max(rowid) id from
( select max(rowid) id from T004ApldMdlCri group by AMCRINO ) a, T004ApldMdlCri b
where a.id=b.rowid group by b.AMCD
) c, T004ApldMdlCri d
) where c.id = d.rowid group by b.AMCRIORD
) e, T004ApldMdlCri f
where f.rowid=e.id;
(select max(rowid) from (select a.amcrino,a.amcd from
(select amcrino,amcd,amcriord from too4apldmdlcri group by amcrino,amcd,amcriord) a group by a.amcrino,a.amcd) b group by b.amcrino ;