SQL一对多关系查询以JSON格式检索数据

Question

我是SQL的初学者，学习设计数据库表和查询特殊情况下的数据

我提出了以下用于学习一对多关系的用例，我为其设计了三个SQL表，如下所示，其中包含公司生产的产品信息及其销售信息：

表1：公司表-包含公司名称

ID  NAME
1   MICROSOFT 
2   APPLE
3   SAMSUNG

表2:PRODUCTSERIES（与公司的多对一关系）-包含公司生产的产品的系列名称

ID NAME         COMPANY_FK
1  WINDOWS        1
2  OFFICE         1
3  IPHONE         2
4  MACBOOK        2
5  GALAXY         3
6  GALAXYNOTE     3

表3：产品（与PRODUCTSERIES的多对一关系）-包含实际生产的产品及其销售信息

ID Name        total_manufactured    sold   PRODUCTSERIES_FK
1  GALAXY7         11                  1       5
2  GALAXY8         11                  1       5
3  GALAXYNOTE7     11                  1       6
4  GALAXYNOTE8     11                  1       6
5  OFFICE10        11                  1       2
6  OFFICE13        11                  1       2
7  IPHONE10        11                  1       3
8  IPHONE12        11                  1       3
9  MACBOOK MINI    11                  1       4
10 MACBOOK PRO     11                  1       4

现在，我试图查询这些表，以获得每个产品系列公司的销售百分比，如下例所示：

[{"company": MICROSOFT, "TOTAL_SALES%": 0.09, "SERIES": [{"WINDOWS_SALES": 0.09}, {"OFFICE_SALES": 0.09}]},
 {"company": APPLE, "TOTAL_SALES%": 0.09, "SERIES": [{"IPHONE_SALES": 0.09}, {"MACBOOK_SALES": 0.09}]},
 {"company": SAMSUNG, "TOTAL_SALES%": 0.09, "SERIES": [{"GALAXY_SALES": 0.09}, {"GALAXYNOTE_SALES": 0.09}]}]

注：一家公司的“总销售额%”是该公司拥有的所有产品系列的平均销售额（销售/总制造）

我是SQL的初学者，不知道如何使用SQL获得输出。你能分享一下我如何构建我的查询或一些相关文档来学习吗？如果我的桌子设计不合适，你能纠正一下吗？我想使用PYTHON执行查询，如果我知道查询，我已经知道如何使用PYTHON执行和检索结果

DB-FIDLE URL：

https://www.db-fiddle.com/f/tJVQe3NqosqX2mkkUZrZMx/1

Answer 1

为了使用MySql，我不得不稍微更改模式（见下文）：

SQL获取每个productseries的百分比。我将把它作为一个练习留给您，让您在公司层面上获得这些信息：

SELECT c.company_name, ps.series_id, ps.series_name, SUM(p.sold) / sq.total_manufactured AS percentage
FROM products p
JOIN productseries ps ON p.product_series = ps.series_id
JOIN company c ON ps.series_company = c.company_id
JOIN (
  SELECT ps1.series_id, SUM(p1.manufactured) as total_manufactured
  FROM products p1 JOIN productseries ps1 ON p1.product_series = ps1.series_id
  GROUP BY ps1.series_id
) sq ON ps.series_id = sq.series_id
GROUP BY company_name, series_id, series_name
ORDER BY company_name, series_name

架构（MySQL v5.7）

CREATE TABLE company (
    company_id INTEGER PRIMARY KEY,
    company_name VARCHAR(32) NOT NULL UNIQUE
);

^{tb1}$

---

View on DB Fiddle

Answer 2

SQL选择

在本例中，您希望从表中获取信息。因此您必须使用SELECT语句

语句的格式如下：SELECT return_values FROM table_name WHERE conditions_statement

return_values可以是列名，也可以是使用列名的SQL函数生成的值

在您的情况下，您需要将三种不同的select语句组合在一起：

• 从每个公司名称等于您要计算其值的公司的行中获取每个公司id（因为这是productseries表中的外键）的列表
• 从productseries表中获取序列号列表（因为这是productseries表中的外键），该行的序列号公司出现在我们之前获取的公司号列表中的每一行
• 获取已售出列的总和，除以products表中每一行的制造列总和，其中product_系列出现在我们之前获取的series_id列表中

在SQL中，需要使用IN关键字，后跟一组括号，包括下一条SQL语句。该关键字基本上将括号中的内容视为列表，并且可以用作条件（即：仅从列表中的表中选择内容）。我们可以从另一个SQL语句的结果生成此列表，这是您需要执行的操作：

• SELECT company_id FROM company WHERE company_name='APPLE'
• SELECT series_id FROM productseries WHERE series_company IN (above_sql_here)
• SELECT SUM(sold)/SUM(manufactured) FROM products WHERE product_series IN (above_sql_here)

您问题的答案

SELECT SUM(sold)/SUM(manufactured) FROM products WHERE product_series IN (SELECT series_id FROM productseries WHERE series_company IN (SELECT company_id FROM company WHERE company_name='APPLE'))

sql中另一个有用的函数是CAST()，它可以影响返回的数字呈现给我们的方式。如果您要求返回的数字为特定的小数位数，请按以下方式开始上面的语句：

SELECT CAST(SUM(sold)/SUM(manufactured) as decimal(places_before_point, places_after_point)) FROM ....

确保用小数点前后所需的小数位数替换places_before_point和places_after_point。（即：decimal(1,2)意味着像0.98一样返回一个浮点）。对：juergen d的职位的信任

还有一件事需要记住的是，你必须为每一家你想找到价值的公司写一份这样的陈述

可能有用的额外点

如果您只想返回一个值，请使用上面只返回百分比值的语句。如果演示是一个问题，您还可以使用JOIN关键字（它将不同SQL语句的结果粘在一起），例如，您希望显示公司名称后跟数字

.... join_type JOIN ....

用表示要执行的联接类型的关键字替换联接类型。更多信息可以在W3Schools上找到，链接如下

最有用的链接

在W3Schools SQL页面上，有一个每个关键字的列表，其中有一个页面，向您解释当您单击它时需要了解的所有内容

如果您正在寻找一个可以在不进行任何实时操作的情况下使用SQL的地方，那么还有两个有用的链接：

• DBFiddle（如上所示）
• SQLFiddle（同类的东西，不同的UI和一些不同的模式）