MySQL添加COUNT( )值

Question

编辑： SQL Fiddle在这里

我在做一个产品饲料。我收到了一份报价清单；

• 每个报价都有一个产品。
• 每种产品都属于一个类别。
• 一个类别可以是另一个类别的子类别。

我有三个表(其中只有相关的行)

报盘：

OFFERS
___________________
| id | product_id |
-------------------
|  1 |         16 |
-------------------
|  2 |         54 |
-------------------
|  3 |         52 |
-------------------
|  4 |         20 |
-------------------
|  5 |          7 |
-------------------
|  6 |          5 |
-------------------

产品：

PRODUCTS
_______________
| id | cat_id |
---------------
| 16 |      1 |
---------------
| 54 |      3 |
---------------
| 52 |      4 |
---------------
| 20 |      1 |
---------------
|  7 |     15 |
---------------
|  5 |      3 |
---------------

类别：

CATEGORIES
_____________________________________________________________
| id | display_name | original_name | subcat_of | is_active |
-------------------------------------------------------------
|  1 | Cars         | automobiles   |         0 |         1 |
-------------------------------------------------------------
|  2 |              | motorcycles   |         0 |         0 |
-------------------------------------------------------------
|  3 | Muscle cars  | muscle-cars   |         1 |         1 |
-------------------------------------------------------------
|  4 | Hybrid cars  | treehugwagons |         1 |         1 |
-------------------------------------------------------------

我得写两个问题。第一个需要

• 返回名称并计算给定主类别及其子类别的报价金额。
• 但只有当主类别处于活动状态时
• 如果类别没有display_name，则使用original_name

我想我把这张写下来了：

SELECT
    offers.id AS offer_id,
    product_id,
    products.cat_id,
    CASE
        WHEN categories.display_name <> ''
        THEN categories.display_name
        ELSE categories.original_name
    END AS cat_name,
    COUNT(offers.id) as num_offers
FROM
    offers
INNER JOIN
    products
    ON
    product_id = products.id
INNER JOIN
    categories
    ON
    cat_id = categories.id
WHERE
    categories.is_active = 1
    AND
    (categories.id = :cat_id OR categories.subcat_of = :cat_id)
GROUP BY
    cat_name
ORDER BY
    cat_name ASC

我很确定这个查询远非理想，但就目前而言，它是有效的。

这是我需要的第二个问题。这一条需要：

• 返回名称并计算给定主类别及其子类别的报价金额，并返回每个主类别的总数。
• 但只有当主类别处于活动状态时
• 如果类别没有display_name，则使用original_name

我可以使用一些PHP来完成自己的总结，但是如果在SQL中做不到这么简单的事情，我会感到惊讶的。

Answer 1

我相信这是可能的，也是相当简单的，假设你没有子分类：

  SELECT CASE 
           WHEN c_main.display_name <> ''
           THEN c_main.display_name
           ELSE c_main.original_name
         END cat_name,
         COUNT(o.id) as num_offers
    FROM offers o
    JOIN products p
      ON o.product_id = p.id
    JOIN categories c
      ON p.cat_id = c.id
     AND (c.id = :cat_id OR c.subcat_of = :cat_id)
  /* AND c.is_active = 1 /* Include if necessary */
    JOIN categories c_main
      ON c_main.id = :cat_id
     AND c_main.is_active = 1 
GROUP BY cat_name
ORDER BY cat_name ASC

我会将您的第一个查询写为：

  SELECT CASE 
           WHEN c.display_name <> ''
           THEN c.display_name
           ELSE c.original_name
         END cat_name,
         COUNT(o.id) as num_offers
    FROM offers o
    JOIN products p
      ON o.product_id = p.id
    JOIN categories c
      ON p.cat_id = c.id
     AND (c.id = :cat_id OR c.subcat_of = :cat_id)
     AND c.is_active = 1
GROUP BY cat_name
ORDER BY cat_name ASC

作为旁白：

我还会考虑将空的NULL输出为display_name，然后您可以替换

CASE 
  WHEN c_main.display_name <> ''
  THEN c_main.display_name
  ELSE c_main.original_name
END cat_name

通过以下方式：

COALESCE(c_main.display_name, c_main.original_name) cat_name

Answer 2

它不能在MySQL中完成，因为MySQL不支持递归查询。你有三个选择：

1. 使用某些复变函数模拟递归/分层查询。(相关)
2. 切换到Server哪个使用CTE支持递归查询。。
3. 写一点PHP。

这取决于你:)

作为附带说明，我在这里留下了一个有趣的幻灯片，它是由SO用户使用SQL和PHP的分层数据模型编写的，它可能适合您的情况。