SQL：根据'like'函数的结果是否可以'按'分组？ [英] SQL: Is it possible to 'group by' according to 'like' function's results?

问题描述

我正在使用Oracle SQL，并且想将一些喜欢函数结果的不同行分组。用一个例子来详细说明：

---

假设我有一张表MESA，其中一列是巨大的串。我计算匹配特定模式的行数：

  SELECT m.str，count（*）
从MESA m 
 WHERE m.str LIKE'％FRUIT％'
 AND（m.str LIKE'％APPLE％'或m.str LIKE'％ORANGE％'）
  

所以我们假设这个查询的结果是：

FRUIT..afsafafasfa ... RED_APPLE 20

FRUIT..afsafafasfa ... YELLOW_APPLE 12

FRUIT ..afsafafasfa ... GREEN_APPLE 3

FRUIT ..afsafafasfa ... PURPLE_ORANGE 4

FRUIT..afsafafasfa ... RED_ORANGE 45

但我希望我的结果是：

APPLE 35

ORANGE 49

---

<这是可能做到的吗？如果是这样，怎么会这样？ ：）

评论和代码片段非常感谢。

PS：当然查询和结果是比上面的例子更复杂。我只是为了简单起见而写它。

干杯..

解决方案确定：

  WITH Fruits AS（
 SELECT 
 CASE 
当m.str LIKE'％APPLE％'THEN'Apple'
当m.str LIKE'％ORANGE％'然后'橙色'
结束为FruitType 
从MESA m 
 WHERE m.str LIKE'％FRUIT％'）
 SELECT FruitType，COUNT（*）
 FROM Fruits 
 WHERE FruitType IN（'Apple'，'Orange'）
 GROUP BY FruitType; 
  

I am using Oracle SQL and I want to group some different rows that 'like' function results. To elaborate with an example:

---

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:

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

FRUIT..afsafafasfa...YELLOW_APPLE 12

FRUIT..afsafafasfa...GREEN_APPLE 3

FRUIT..afsafafasfa...PURPLE_ORANGE 4

FRUIT..afsafafasfa...RED_ORANGE 45

But I want my results to be:

APPLE 35

ORANGE 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.

Cheers..

解决方案

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;

这篇关于SQL：根据'like'函数的结果是否可以'按'分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！