现在遇见个问题，现在生成的一张表中：A B C
1 2 1
3 4 1
5 6 2
7 8 2.......也就是说，每行数据除了C字段可能相同外，其他字段不会完全相同，现在要根据c分组，然后每个组中任意选择一行生成结果，比如结果可以是A B C
1 2 1
5 6 2
也可以是：
A B C
3 4 1
7 8 2这个查询如何实习呢， 我知道有一个关键字叫做distinct 但是好像只能对查询一个字段有用吧 比如可以查到
distinct C 用作distinct * 就不行呀
1 2 1
3 4 1
5 6 2
7 8 2.......也就是说，每行数据除了C字段可能相同外，其他字段不会完全相同，现在要根据c分组，然后每个组中任意选择一行生成结果，比如结果可以是A B C
1 2 1
5 6 2
也可以是：
A B C
3 4 1
7 8 2这个查询如何实习呢， 我知道有一个关键字叫做distinct 但是好像只能对查询一个字段有用吧 比如可以查到
distinct C 用作distinct * 就不行呀
select '1' as A, '2'as B, '1' as C from dual union
select '3' as A, '4'as B, '1' as C from dual union
select '5' as A, '6'as B, '2' as C from dual union
select '7' as A, '8'as B, '2' as C from dual)
select (select tab.a
from tab
where tab.c = t.c
and rownum = 1) A,
(select tab.b
from tab
where tab.c = t.c
and rownum = 1) B,
t.c
from (select c from tab group by c) t
根据C分组，然后任取一个A和B
这样sql写起来简单 但是只能查min或者max
如果可以任取的话 那取min或者max也没问题吧
一个查询可以有两个结果集么？如卤煮所言：比如结果可以是A B C
1 2 1
5 6 2
也可以是：
A B C
3 4 1
7 8 2
我觉着一个sql能够查询出不同的结果集，这个有点难度。要引入一些变动的量么？
select 'a' as objid,'1' as A, '2'as B, '1' as C from dual union
select 'b' as objid,'3' as A, '4'as B, '1' as C from dual union
select 'c' as objid,'5' as A, '6'as B, '2' as C from dual union
select 'd' as objid,'7' as A, '8'as B, '2' as C from dual)
select *
from tab t2
where t2.objid in (select (select t.objid
from tab t
where tab.c = t.c
and rownum = 1)
from tab
group by c)
你的表里有没有唯一标识啊 如果有的话 可以分组后任取一个 查出来
然后根据这些唯一标识再去查其他字段，这样就不用一个一个转换了
否则 我就没有好办法了
with t as(
SELECT 1 AS A,2 AS B,1 AS C FROM DUAL union ALL
SELECT 3 AS A,4 AS B,1 AS C FROM DUAL union ALL
SELECT 5 AS A,6 AS B,2 AS C FROM DUAL union ALL
SELECT 7 AS A,8 AS B,2 AS C FROM DUAL
)
SELECT O.A,O.B,O.C FROM
(SELECT T.A,T.B,T.C,ROW_NUMBER() OVER(PARTITION BY T.C ORDER BY T.C) D FROM T) O
WHERE O.D=1;
结果如下:
A B C
1 2 1
5 6 2
with t as(
SELECT 1 AS A,2 AS B,1 AS C FROM DUAL union ALL
SELECT 3 AS A,4 AS B,1 AS C FROM DUAL union ALL
SELECT 5 AS A,6 AS B,2 AS C FROM DUAL union ALL
SELECT 7 AS A,8 AS B,2 AS C FROM DUAL
)
SELECT O.A,O.B,O.C FROM
(SELECT T.A,T.B,T.C,ROW_NUMBER() OVER(PARTITION BY T.C ORDER BY T.C) D FROM T) O
WHERE O.D=2;
结果如下:
A B C
3 4 1
7 8 2
嗯嗯， 好的， 不过还有一个问题，我要查询的对象是又几个表联合查询生成的， 也就是A,B,C分别来自于不同的表， 现在我称生成的表为T ，A来自于T1，B来自于T2，C来自于T3， 我现在直接用T.C或者C去访问的话提示为明确定义列，用T3.C去访问又提示无效的标示符，怎么能访问到C呢
求解
你先使用T1 T2 T3 构建一个结果集,替代T1即可.
利用分析函数依据每个c字段增加序号，每个都从1开始。然后，仅仅取序号为1的就行了。
你参考下，我是以emp表为例的
SELECT * FROM
(SELECT t.*,row_number() OVER (
PARTITION BY t.deptno ORDER BY t.empno DESC
) rn FROM emp t )
WHERE rn=1
(SELECT T.*,ROW_NUMBER() OVER(PARTITION BY LOGICALCODE ORDER BY LOGICALCODE) D FROM (SELECT * FROM
GISOPTIC T1 ,GISPORTSH T2,(SELECT MIN(ID) ID FROM GISOPTIC GROUP BY LOGICALCODE) T3
WHERE T1.ID=T3.ID AND (T1.APORTID=T2.ID OR T1.ZPORTID=T2.ID)) T) O
WHERE O.D=1;现在就是PARTITION BY LOGICALCODE ORDER BY LOGICALCODE中LOGICALCODE如何访问的问题，这里面LOGICALCODE是T1里面的一个字段