Oracle子查询前1名的结果(Oracle subquery top 1 result)
我想为b的每个唯一值得到最高的1行,最小值为c的那个特定值。 即使可以有超过1行具有相同的最小值(只需选择第一个)
为myTable
一个整数(唯一)
b整数
c整数
我试过这个查询
SELECT t1.*
FROM myTable t1,
(SELECT b,
MIN(c) as c
FROM myTable
GROUP BY b) t2
WHERE t1.b = t2.b
AND t1.c = t2.c
然而,在这个表中,对于给定的b值,有可能存在c的最小值的多于1个实例。 上述查询在这些条件下生成重复项。
我有一种感觉,我需要在某处使用rownum,但我不太确定它在哪里。
I want to get the top 1 row for each unique value of b with the minimum value of c for that particular value of b. Even though there can be more than 1 row with the same min value (just chose the first one)
myTable
a integer (unique)
b integer
c integer
I've tried this query
SELECT t1.*
FROM myTable t1,
(SELECT b,
MIN(c) as c
FROM myTable
GROUP BY b) t2
WHERE t1.b = t2.b
AND t1.c = t2.c
However, in this table it's possible for there to be more than 1 instance of the minimum value of c for a given value of b. The above query generates duplicates under these conditions.
I've got a feeling that I need to use rownum somewhere, but I'm not quite sure where.
原文:https://stackoverflow.com/questions/4716438
更新时间:2019-12-12 06:54
最满意答案
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
FROM myTable
) AS T1
WHERE rn = 1
You can use ROW_NUMBER:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
FROM myTable
) AS T1
WHERE rn = 1
2011-01-17
相关问答
通过以下方式将此查询转换为联接: select tbl_a.col_a,
tbl_a.col_b,
max(most_recent_target_date.target_date)
from tbl_a
join (
select tbl_b.target_date, tbl_b.date_id
from tbl_b
where tbl_b.flag = 1
...
试试这个当前日期(SYSDATE): -- INSERT INTO TOP3SUPP( Top3_Year, Top3_Month, Supp_ID, Total_Expense)
select
to_char(SYSDATE, 'YYYY'), to_char(SYSDATE, 'MM'),
SUPPLIER_ID, total_per_supplier
from (
SELECT SUPPLIER_ID, SUM(TOTAL) AS total_per_supplier
...
这不是一个错误。 由于您没有对列进行限定,Oracle将其解释为来自表A的当前列值(在范围内)(参见相关子查询 )。 因此,子查询只会产生与表B中的行一样多的该列值的重复项。 基于此in入境条件将变为真或假。 当表B为空时,它总是错误的。 在Col_A为空的情况下也是错误的。 但在所有其他情况下,这是事实。 因此,除非表B为空,否则无法得到结果,所以您将获得与表A中具有Col_A的非空值的Col_A相同的结果。 It is not a bug. As you did not qualify the
...
有两种可能性:一种方法是修复子查询,使其始终只返回一行,另一种可能是使用IN或ANY : SELECT name from SUPPLIER
WHERE code in (SELECT code FROM STOCK GROUP BY code HAVING COUNT(DISTINCT code)>=3);
there are two possibilities: one thing is to repair the sub-query so that it would always retu
...
你可以使用ROW_NUMBER : SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
FROM myTable
) AS T1
WHERE rn = 1
You can use ROW_NUMBER: SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
FR
...
如果我正确理解你的问题,你可以在子查询中使用row_number : select orderid, orderlineid, statusid
from (
select o.orderid,
ol.orderlineid,
t.statusid,
row_number() over (partition by o.orderid order by t.taskid desc) rn
from order o
...
SELECT a.*
FROM schedule a
WHERE user_id = 0
AND NOT EXISTS (
SELECT NULL
FROM schedule b
WHERE b.user_id = 123456
AND b.start_date <= a.end_date
AND b.end_date >= a.start_date
)
SELECT a.*
FROM schedule a
WHERE us
...
只看重构现有的查询(而不是逻辑上或功能上不同的方法) 。 对我而言,最简单的方法就是将其作为嵌套查询来完成。 - 内部查询将是您的基本查询,不包含CASE语句 - 它也将包含您的相关子查询作为附加字段 - 外部查询可以将该字段嵌入到CASE语句中 SELECT
nested_query.ID,
nested_query.FULL_NAME,
nested_query.EarliestDate,
CASE
WHEN nested_query.Earli
...
SELECT
k.[mg_KarId] AS [mg_KarId],
k.[SymKar] AS [SymKar],
k.[OpiKar] AS [OpiKar],
k.[Status] AS [Status],
(
SELECT TOP 1 kml.SymLok
FROM dbo.mg_KarMagLok kml
WHERE kml.Mag LIKE 'GLS1'
AND kml ON k.SymKar = kmlg.SymKar
) ,
(
SELE
...
我同意Jonathan的观点,认为30000的价值不是去这里的方式,他的解决方案是一个好方法。 但这是另一个想法,Oracle有一个很好的功能,称为外部表。 使用外部表,您可以将文件视为表。 如果您的文件结构良好(如CSV),则可以轻松使用此功能。 谷歌“Oracle外部表”,你会发现很多信息和例子,但对于初学者,请阅读: 基本上,您需要一个目录对象来告诉Oracle您的文件所在的位置,然后您将创建外部表,可能如下所示: create directory DIR_MYFILE as '/my_so
...