SQL для присоединения одной таблицы к другой таблице несколько раз? (Сопоставление продуктов с категориями)

Допустим, у меня есть таблица Product, Category и Product_To_Category. Продукт может быть в нескольких категориях.

    Product                     Category        Product_to_category
    ID   |   NAME               ID  | Name      Prod_id | Cat_id
    =====================       ============    ===================
        1| Rose                    1| Flowers          1| 1
        2| Chocolate Bar           2| Food             2| 2
        3| Chocolate Flower                            3| 1
                                                       3| 2

Я хотел бы запрос SQL, который дает мне результат, например

    ProductName      | Category_1 | Category_2 | Category_3
    =======================================================
    Rose             | Flowers    |            |
    Chocolate Flower | Flowers    | Food       |

и Т. Д.

Лучший способ, которым я смог это сделать, — объединить несколько запросов вместе; один запрос для каждого ожидаемого количества категорий для данного продукта.

select p.name, cat1.name, cat2.name
from
  product p, 
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat1,
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat2
where p.id = cat1.id 
  and p.id = cat2.id
  and cat1.id != cat2.id
union all
select p.name, cat1.name, null
from
  product p, 
  (select * from category c, producttocategory pc where pc.category_id = c.id) cat1
where p.id = cat1.id 
  and not exists (select 1 from producttocategory pc where pc.product_id = p.id and pc.category_id != cat1.id)

Есть несколько проблем с этим.

  • Во-первых, я должен повторить это объединение для каждой ожидаемой категории; если продукт может быть в 8 категориях, мне нужно 8 запросов.
  • Во-вторых, категории неравномерно размещены в одних и тех же столбцах. Например, иногда у продукта может быть «Еда, цветы», а иногда «Цветы, еда».

Кто-нибудь знает лучший способ сделать это? Кроме того, есть ли у этой техники техническое название?


sql join
person Mr. Shiny and New 安宇    schedule 20.04.2009    source источник

Ответы (4)


arrow_upward
10
arrow_downward

Я не знаю, какую СУБД вы используете, но в MySQL вы можете использовать GROUP_CONCAT:

SELECT
  p.name,
  GROUP_CONCAT(c.name SEPARATOR ', ') AS categories
FROM
  product p
  JOIN product_to_category pc ON p.id = pc.product_id
  JOIN category c ON c.id = pc.category_id
GROUP BY
  p.name
ORDER BY
  p.name,
  c.name
person Seb    schedule 20.04.2009
comment
Без GROUP BY этот запрос вернет только одну строку, так же как запрос COUNT(*) вернет только одну строку. - person chim; 29.04.2013

arrow_upward
1
arrow_downward

Вы не можете создать эти результаты с помощью строгого SQL-запроса. То, что вы пытаетесь создать, называется сводной таблицей. Многие инструменты отчетности поддерживают такое поведение, когда вы выбираете свой продукт и категорию, а затем превращаете категорию в сводную колонку.

Я полагаю, что SQL Server Analysis Services также поддерживает подобные функции, но у меня нет опыта работы со службами SSAS.

person Adam Robinson    schedule 20.04.2009

arrow_upward
1
arrow_downward 

SELECT p.name, cat_food.name, cat_flowers.name
FROM
  product p
  left outer join  Product_to_category pc_food 
    on p.id = pc_food.Prod_id
  left outer join Category cat_food
    on pc_food.Cat_id = cat_food.id
    AND cat_food.name = 'Food'
  left outer join  Product_to_category pc_flowers
    on p.id = pc_flowers.Prod_id
  left outer join Category cat_flowers
    on pc_flowers.Cat_id = cat_flowers.id
    AND cat_flowers.Name = 'Flowers'

Это работает, только если вы знаете количество возможных категорий, чтобы поместить их в столбцы. Так работает (стандартный) SQL, количество столбцов не является динамическим.

person Stefan Steinegger    schedule 20.04.2009

arrow_upward
1
arrow_downward

Ответ Себа поставил меня на правильный путь для обходного пути. Я использую Oracle, и у него есть функции, которые эмулируют MYSQL group_concat. Вот пример. Это не создает столбцы и, следовательно, не так хорошо, как чистое решение SQL, но подходит для моих текущих целей.

with data as
( 
  select 
    pc.id cat,
    p.id prod, 
    row_number() over( partition by p.id order by pc.id) rn,
    count(*) over (partition by p.id) cnt
  from product_to_category pc, product p
  where pc.product_id = p.id
)
select prod, ltrim(sys_connect_by_path(cat, ','), ',') cats
  from data
 where rn = cnt
 start with rn = 1 connect by prior prod = prod and prior rn = rn - 1
 order by prod

Это генерирует такие данные, как

PROD | CATS
===========
284  |   12
285  |   12
286  | 9,12

Я могу редактировать столбец ltrim(sys_connect_by_path()) по мере необходимости, чтобы генерировать любые данные, которые мне нужны.

person Mr. Shiny and New 安宇    schedule 20.04.2009