SQL: Можно ли "группировать по" в соответствии с результатами функции "как"?

Я использую Oracle SQL, и я хочу сгруппировать несколько разных строк, которые похожи на результаты функции. Чтобы разработать пример:

Предположим, что у меня есть таблица MESA с одним из столбцов - огромная строка. И я подсчитываю количество строк, соответствующих определенным шаблонам:

SELECT m.str, count(*)
FROM MESA m
WHERE m.str LIKE '%FRUIT%'
AND (m.str LIKE '%APPLE%' OR 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: Конечно, запрос и результаты сложнее, чем приведенный выше пример. Я просто написал это, как для простоты, чтобы объяснить.

Приветствия..

Ответ 1

Конечно:

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;

Ответ 2

Другой вариант ответа Дэвида Маркла:

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

То же самое, но требуется только 1 CASE, что упрощает поддержку...

Ответ 3

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%'

Будет ли это работать?

Ответ 4

Что-то вроде этого?

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

Ответ 5

Я бы сделал это таким образом - требуется только одно изменение, чтобы добавить дополнительные типы фруктов.

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

Если ваши строки надежно указаны в формате, который вы указали в ваших образцовых данных, я бы рассмотрел изменение предиката на одно условие, WHERE m.str LIKE 'FRUIT%' || fruits.fruit ||'%'.