oracle SQL：是否可以根据“喜欢”函数的结果“分组”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3299330/
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
SQL: Is it possible to 'group by' according to 'like' function's results?
提问by someone
I am using Oracle SQL and I want to group some different rows that 'like' function results. To elaborate with an example:
我正在使用 Oracle SQL 并且我想对一些“喜欢”函数结果的不同行进行分组。举例说明：
Let's assume I have a table MESA with one of the columns is a huge string. And I am counting the number of rows matching particular patterns:
假设我有一个表 MESA，其中一列是一个巨大的字符串。我正在计算匹配特定模式的行数：
SELECT m.str, count(*)
FROM MESA m
WHERE m.str LIKE '%FRUIT%'
AND (m.str LIKE '%APPLE%' OR m.str LIKE '%ORANGE%')
So let's assume the result of this query is:
所以让我们假设这个查询的结果是：
FRUIT..afsafafasfa...RED_APPLE 20
水果..afsafafasfa...RED_APPLE 20
FRUIT..afsafafasfa...YELLOW_APPLE 12
FRUIT..afsafafasfa...YELLOW_APPLE 12
FRUIT..afsafafasfa...GREEN_APPLE 3
FRUIT..afsafafasfa...GREEN_APPLE 3
FRUIT..afsafafasfa...PURPLE_ORANGE 4
FRUIT..afsafafasfa...PURPLE_ORANGE 4
FRUIT..afsafafasfa...RED_ORANGE 45
水果..afsafafasfa...RED_ORANGE 45
But I want my results to be:
但我希望我的结果是：
APPLE 35
苹果 35
ORANGE 49
橙色 49
Is this possible to do? If so, how so? : )
这有可能吗？如果是这样，怎么会这样？:)
Comments and code snippets are much appreciated.
非常感谢评论和代码片段。
PS: Of course the query and the results are more complicated than the above example. I just wrote it like for the sake of simplicity to explain.
PS：当然查询和结果比上面的例子复杂。我只是为了简单解释才写出来的。
Cheers..
干杯..
回答by Dave Markle
Sure:
当然：
WITH Fruits AS (
SELECT
CASE
WHEN m.str LIKE '%APPLE%' THEN 'Apple'
WHEN m.str LIKE '%ORANGE%' THEN 'Orange'
END AS FruitType
FROM MESA m
WHERE m.str LIKE '%FRUIT%')
SELECT FruitType, COUNT(*)
FROM Fruits
WHERE FruitType IN ('Apple', 'Orange')
GROUP BY FruitType;
回答by ThinkJet
Another variant of David Markle answer:
大卫马克尔回答的另一个变体：
SELECT
fruit_name,
count(1) as fruit_count
FROM (
SELECT
CASE
WHEN m.str LIKE '%APPLE%' THEN 'Apple'
WHEN m.str LIKE '%ORANGE%' THEN 'Orange'
END as fruit_name
FROM
MESA m
WHERE
m.str LIKE '%FRUIT%'
AND
(m.str LIKE '%APPLE%' OR m.str LIKE '%ORANGE%')
)
GROUP BY
fruit_name
Same thing, but only 1 CASE required, which simplifies support ...
同样的事情，但只需要 1 个 CASE，这简化了支持......
回答by Dave Costa
I would do it this way -- only requires a single change to add additional types of fruit.
我会这样做 - 只需要进行一次更改即可添加其他类型的水果。
WITH fruits AS (
SELECT 'APPLE' fruit FROM DUAL
UNION ALL
SELECT 'ORANGE' fruit FROM DUAL
)
SELECT fruit, count(*)
FROM MESA m, fruits
WHERE m.str LIKE '%FRUIT%'
AND m.str LIKE '%' || fruits.fruit || '%'
GROUP BY fruit
If your strings are reliably in the format you showed in your sample data, I would consider changing the predicate to one condition, WHERE m.str LIKE 'FRUIT%' || fruits.fruit ||'%'.
如果您的字符串可靠地采用您在示例数据中显示的格式，我会考虑将谓词更改为一种条件WHERE m.str LIKE 'FRUIT%' || fruits.fruit ||'%'.
回答by JNK
SELECT count(*) AS 'Apples'
FROM MESA m
WHERE m.str LIKE '%FRUIT%'
AND m.str LIKE '%APPLE%'
SELECT count(*) AS 'Oranges'
FROM MESA m
WHERE m.str LIKE '%FRUIT%'
AND m.str LIKE '%ORANGE%'
Would that work?
那行得通吗？
回答by Mark Baker
Something like this?
像这样的东西？
SELECT Fruit,
SUM(counter)
FROM ( SELECT CASE
WHEN m.str LIKE '%APPLE%'
THEN 'APPLE'
ELSE 'ORANGE'
END AS Fruit
COUNT(*) AS counter
FROM MESA m
WHERE m.str LIKE '%FRUIT%'
AND (m.str LIKE '%APPLE%' OR m.str LIKE '%ORANGE%')
GROUP BY m.str
)
GROUP BY Fruit
