SELECT DISTINCT col1, col2 FROM table t ORDER BY col1;
这给了我col1
和col2
的独特组合。是否有其他方法编写Oracle SQL查询以获取col1
和col2
记录的唯一组合,而不使用关键字distinct?
SELECT UNIQUE col1, col2 FROM table t ORDER BY col1;
SELECT col1, col2 FROM table_t GROUP BY col1, col2 ORDER BY col1
select col1, col2
from table
group by col1, col2
order by col1
select col1,col2 from table
UNION
select col1,col2 from table
order by col1;
select a.col1, a.col2
from (select col1, col2 from table
UNION
select NULL, NULL) a
where a.col1 is not null
order by a.col1
order by
。 - ypercubeᵀᴹUNION
默认具有DISTINCT
,而SELECT
则默认具有ALL
操作。 - ypercubeᵀᴹselect *
from (
select col1,
col2,
row_number() over (partition by col1, col2 order by col1, col2) as rn
from the_table
)
where rn = 1
order by col1
ORDER BY
应该也在外部查询中吗?或者只在那里? - ypercubeᵀᴹ又一个...
select
col1,
col2
from
table t1
where
not exists (select *
from table t2
where t2.col1 = t1.col1 and
t2.col2 = t1.col2 and
t2.rowid > t1.rowid)
order by
col1;
INTERSECT
SELECT col1, col2 FROM tableX
INTERSECT
SELECT col1, col2 FROM tableX
ORDER BY col1;
MINUS
SELECT col1, col2 FROM tableX
MINUS
SELECT col1, col2 FROM tableX WHERE 0 = 1
ORDER BY col1;
MINUS
(第二个版本,如果存在(NULL, NULL)
分组,则返回的行数比其他版本少一行)
SELECT col1, col2 FROM tableX
MINUS
SELECT NULL, NULL FROM dual
ORDER BY col1;
另一个...
select col1,
col2
from (
select col1,
col2,
rowid,
min(rowid) over (partition by col1, col2) min_rowid
from table)
where rowid = min_rowid
order by col1;