Oracle - 返回一组行中最短的字符串值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2676741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Return shortest string value in a set of rows
提问by MNIK
I'm trying to write a query that returns the shortest string value in the column. For ex: if ColumnA has values ABCDE, ZXDR, ERC, the query should return "ERC". I've written the following query, but I'm wondering if there is any better way to do this?
我正在尝试编写一个返回列中最短字符串值的查询。例如：如果 ColumnA 具有值 ABCDE、ZXDR、ERC，则查询应返回“ERC”。我已经编写了以下查询，但我想知道是否有更好的方法来做到这一点？
The query should return a single value.
查询应返回单个值。
select distinct ColumnA from
(
select ColumnA, rank() over (order by length(ColumnA), ColumnA) len_rank
from TableA where ColumnB = 'XXX'
)
where len_rank <= 1
采纳答案by IMHO
How about:
怎么样：
select ColumnA
from
(
select ColumnA
from tablea
order by length(ColumnA) ASC
)
where rownum = 1
回答by DMS
This would help you get all the strings with the minimum length in the column.
这将帮助您获得列中长度最小的所有字符串。
select ColumnA
from TableA
where length(ColumnA) = (select min(length(ColumnA)) from TableA)
Hope this helps.
希望这可以帮助。
回答by Rob van Wijk
The simplest way, with a single table access and without subqueries:
最简单的方法，单表访问且没有子查询：
SQL> create table mytable (txt)
2 as
3 select 'ABCDE' from dual union all
4 select 'ZXDR' from dual union all
5 select 'ERC' from dual
6 /
Table created.
SQL> set autotrace on explain
SQL> select min(txt) keep (dense_rank first order by length(txt)) txt
2 from mytable
3 /
TXT
-----
ERC
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MYTABLE'
EDIT: I adjusted the example to fit your example query even more:
编辑：我调整了示例以更适合您的示例查询：
SQL> create table tablea (columna,columnb)
2 as
3 select 'ABCDE', 'XXX' from dual union all
4 select 'ZXDR', 'XXX' from dual union all
5 select 'ERC', 'XXX' from dual
6 /
Table created.
SQL> set autotrace on explain
SQL> select min(columna) keep (dense_rank first order by length(columna)) columna
2 from tablea
3 where columnb = 'XXX'
4 /
COLUM
-----
ERC
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TABLEA'
Regards, Rob.
问候，罗伯。
回答by doctorgu
I know this has very long answer and very old. But I think I know another good way.
我知道这有很长的答案而且很旧。但我想我知道另一个好方法。
Because all answer is using sub query, it was not suitable for my situation. So I found a way which was not used sub query.
因为所有答案都使用子查询，所以不适合我的情况。所以我找到了一种没有使用子查询的方法。
Say I have a data as following.
假设我有如下数据。
select *
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
x
aaaaa
If I select min of value it returns 'aaaaa' because 'a' is smaller than 'x' in ascii order.
如果我选择 min of value 它返回 'aaaaa' 因为 'a' 在 ascii 顺序中小于 'x'。
select min(a.f)
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
aaaaa
But if I select min of length it returns 1(which is for 'x' value) because 1 is smaller than 5 in numeric order.
但是，如果我选择 min of length 它会返回 1（用于 'x' 值），因为 1 按数字顺序小于 5。
select min(length(a.f))
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
1
And if I select min of length converted to padded value also returns '0000000001'(which is for 'x' value) because '0000000001' is smaller than '0000000005' in ascii order.
如果我选择转换为填充值的最小长度也会返回 '0000000001'（用于 'x' 值），因为 '0000000001' 在 ascii 顺序中小于 '0000000005'。
select min(lpad(length(a.f), 10, '0'))
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
0000000001
And I can bind it with value itself.
我可以将它与价值本身绑定。
select lpad(length(a.f), 10, '0') || a.f
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
0000000001x
0000000005aaaaa
Now I can select min of length and value together.
现在我可以一起选择长度和值的最小值。
select min(lpad(length(a.f), 10, '0') || a.f)
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
0000000001x
Now I can get only value by using substr.
现在我只能通过使用 substr 来获得价值。
select substr(min(lpad(length(a.f), 10, '0') || a.f), 11, 999)
from (select 'x' f from dual union all select 'aaaaa' from dual) a
--Output
- 输出
x
回答by Sriya
select city,length(city) from (select city from station ORDER BY length(city)
ASC, CITY ASC)where rownum=1;
select city,length(city) from (select city from station ORDER BY length(city)
DESC, CITY ASC)where rownum=1;
回答by eaolson
Expanding on APC's answer a little bit, I think this will be slightly better:
稍微扩展一下 APC 的答案，我认为这会稍微好一点：
SELECT DISTINCT columna
FROM tablea t1
WHERE EXISTS ( SELECT 1 FROM tablea t2
WHERE LENGTH(t2.columna) = MIN(LENGTH(t1.columna)) )
AND rownum = 1
IIRC, APC's subselect will be run once for each row in tablea. This, I believe, does not.
IIRC，APC 的子选择将针对 tablea 中的每一行运行一次。我相信，这不会。
Mind you, if you have multiple rows with the same length string in columna, you may not get consistent results from this query run multiple times.
请注意，如果 columna 中有多行具有相同长度的字符串，则多次运行此查询可能无法获得一致的结果。
回答by APC
There are two parts to this question. An alternative way of determining the shortest string is the old-fashioned sub-query:
这个问题有两个部分。确定最短字符串的另一种方法是老式子查询：
select distinct ColumnA
from tablea
where length(ColumnA) = ( select min(length(ColumnA))
from TableA
where ColumnB = 'XXX'
)
/
Which is better? It depends on the indexing, data volumes, etc but I would guess your version is likely to perform better. It might also give slightly different results, unless you duplicated the where ColumnB = 'XXX'in the outer query.
哪个更好？这取决于索引、数据量等，但我猜你的版本可能会表现得更好。它也可能给出略有不同的结果，除非您where ColumnB = 'XXX'在外部查询中复制了。
Like your solution this query will return one row for each value of ColumnA which is three characters long. If you want to return a single row you can do so by restricting it with rownum. It you want to apply some criterion to determine which is the first row you need to embed it in a further outer query (using my query but a variant on yours would work too) ...
与您的解决方案一样，此查询将为 ColumnA 的每个值返回一行，该值的长度为三个字符。如果你想返回单行，你可以通过限制它来实现rownum。如果您想应用一些标准来确定哪一行是您需要将其嵌入到进一步的外部查询中的第一行（使用我的查询，但您的变体也可以使用）...
select * from (
select ColumnA
from tablea
where length(ColumnA) = ( select min(length(ColumnA))
from TableA
where ColumnB = 'XXX'
)
order by ColumnA
)
where rownum = 1
/
