MYSQL - 正确连接我的 3 个表的语法

Question

Edit2:选择分离查询并在数据库输出之外将信息作为一个整体进行整理/处理。以 .CSV 格式取出这些数据，并将它们添加到 Excel 中，我将在其中运行实际数字。

查询 1 以提取订单和所需信息:

SELECT
shipstation_orders_v2.id AS SSO_id,
shipstation_orders_v2.order_number AS SSO_orderNumber,
shipstation_orders_v2.order_id AS SSO_orderID,
shipstation_orders_v2.storename AS SSO_storeName,
shipstation_orders_v2.order_date AS SSO_orderDate,
shipstation_orders_v2.order_total AS SSO_orderTotal,
shipstation_orders_v2.name AS SSO_name,
shipstation_orders_v2.company AS SSO_company

FROM shipstation_orders_v2

GROUP BY shipstation_orders_v2.id,
     shipstation_orders_v2.order_number,
     shipstation_orders_v2.order_id,
     shipstation_orders_v2.storename,
     shipstation_orders_v2.order_date,
     shipstation_orders_v2.order_total,
     shipstation_orders_v2.name,
     shipstation_orders_v2.company

ORDER BY SSO_orderDate

查询 2 以提取履行情况和等效信息:

SELECT DISTINCT
shipstation_orders_v2.id AS SSO_id,
shipstation_fulfillments.id AS SSF_id,
shipstation_fulfillments.order_number AS SSF_orderNumber,
shipstation_orders_v2.order_number AS SSO_orderNumber,
shipstation_orders_v2.order_id AS SSO_orderID,
shipstation_orders_v2.storename AS SSO_storeName,
shipstation_orders_v2.order_date AS SSO_orderDate,
shipstation_fulfillments.order_date AS SSF_orderDate,
shipstation_orders_v2.order_total AS SSO_orderTotal,
shipstation_fulfillments.amount_paid AS SSF_amountPaid,
shipstation_orders_v2.name AS SSO_name,
shipstation_orders_v2.company AS SSO_company,
shipstation_fulfillments.name AS SSF_name,
shipstation_fulfillments.company AS SSF_company

FROM shipstation_fulfillments

  INNER JOIN shipstation_orders_v2
  ON shipstation_fulfillments.order_number = 
  shipstation_orders_v2.order_number

WHERE shipstation_fulfillments.order_number = 
shipstation_orders_v2.order_number
  GROUP BY shipstation_orders_v2.id,
     shipstation_fulfillments.id,
     shipstation_fulfillments.order_number,
     shipstation_orders_v2.order_number,
     shipstation_orders_v2.order_id,
     shipstation_orders_v2.storename,
     shipstation_orders_v2.order_date,
     shipstation_fulfillments.order_date,
     shipstation_orders_v2.order_total,
     shipstation_fulfillments.amount_paid,
     shipstation_orders_v2.name,
     shipstation_orders_v2.company,
     shipstation_fulfillments.name,
     shipstation_fulfillments.company

编辑:问题标记为已回答。我想出了另一种不那么轻率的方法。支持 DRapp 让我的大脑动起来。

原始代码位于文字墙下方

我是一名自学成才的 MySQL 数据库用户。我不会说管理员，因为只有我。我已经整理了一个小型数据库用于工作 - 大约 60,000 行和最多 51 列分布在三个表中。我在工作中使用它来组织相当不同的销售数据设置，并利用它来识别趋势、季节性和所有这些好东西。我主要处理 Shipstation 数据。

我的问题是当我需要引入第三个表时。显然，对于两个表，这只是一个简单的JOIN。我的工作得很好。我在为第三个表正确设置 JOIN 时遇到了一些麻烦。

我正在尝试将两个最里面查询的数据JOIN到shipstation_orders_v2和order_keys到shipstation_fulfillments 我在第三个表中得到的结果。

对于那些不使用 Shipstation 或不熟悉其中这一元素的人来说，履行与订单属于不同的类别，并且不使用完全相同的数据。这是我将它们粘合在一起的肮脏方式，这样我们就可以得到一些关于销售和运输趋势等的体面的、可操作的信息。

我正在从 shipstation_orders_v2 到 order_keys 进行内部查询，作为 SELECT DISTINCT 拆分订单总和的方法。在构建该子查询之前，我遇到了数据重复问题。使用(现在的)子查询和子子查询，重复问题已被消除，并且仅使用这两个表就可以正常工作。

问题是，当我使用 JOIN 从 shipstation_fulfillments 进行 SELECT 到子查询和子子查询时，我'我遇到了障碍。

我在处理此查询时遇到了几个错误。按照发生和解决的顺序:

错误 2013，在查询期间失去与服务器的连接(这告诉我我正在对三个连接表进行全表读取，因为它事先没有出错，但我的 rinkadink 设置可以处理不了)。我摆脱了那个。

然后，由于无法识别的表名 shipstation_fulfillments，出现错误 1051。对我来说，我认为这可能是查询别名的问题。我不确定。

最后，好 ole 错误 1064，之后的第一个子查询的语法不正确 选择shipstation_fulfillments参数。

由于是自学成才，我几乎可以保证我只是在某个地方缺少了一个语法元素，而这对于熟练的 MySQL 用户来说似乎是相当明显的。以下是我当前的查询设置。

如果需要任何澄清，请告诉我。

SELECT

`shipstation_fulfillments`.`order_date` AS `orderDate`,
`shipstation_fulfillments`.`order_number` AS `orderNumber`,
(`shipstation_fulfillments`.`amount_paid` + `shipstation_fulfillments`.`tax_paid`) AS "Total Paid",
`shipstation_fulfillments`.`name` AS `name`,
`shipstation_fulfillments`.`company` AS `company`,

FROM
(
    (SELECT
        COUNT(`shipstation_orders_v2`.`order_key`) AS `orderCount`,
        `shipstation_orders_v2`.`key_id` AS `key_id`,
        `shipstation_orders_v2`.`order_number` AS `order_number`,
        MAX(`shipstation_orders_v2`.`order_date`) AS `order_date`,
        `shipstation_orders_v2`.`storename` AS `store`,
        (`shipstation_orders_v2`.`order_total` -     `shipstation_orders_v2`.`shippingPaid`) AS `orderPrice`,
        `shipstation_orders_v2`.`shippingpaid` AS `shippingPaid`,
        SUM(`shipstation_orders_v2`.`shippingpaid`) AS `SUM shippingPaid`,
        `shipstation_orders_v2`.`order_total` AS `orderTotal`,
        SUM(`shipstation_orders_v2`.`order_total`) AS `SUM Total Amount Paid`,
        `shipstation_orders_v2`.`qtyshipped` AS `qtyShipped`,
        SUM(`shipstation_orders_v2`.`qtyshipped`) AS `SUM qtyShipped`,
        `shipstation_orders_v2`.`name` AS `name`,
        `shipstation_orders_v2`.`company` AS `company`

        FROM

            (SELECT DISTINCT
                `order_keys`.`key_id` AS `key_id`,
                `order_keys`.`order_key` AS `order_key`,
                `shipstation_orders_v2`.`order_number` AS `order_number`,
                `shipstation_orders_v2`.`order_id` AS `order_id`,
                `shipstation_orders_v2`.`order_date` AS `order_date`,
                `shipstation_orders_v2`.`storename` AS `storename`,
                `shipstation_orders_v2`.`order_total` AS `order_total`,
                `shipstation_orders_v2`.`qtyshipped` AS `qtyshipped`,
                `shipstation_orders_v2`.`shippingpaid` AS `shippingpaid`,
                `shipstation_orders_v2`.`name` AS `name`,
                `shipstation_orders_v2`.`company` AS `company`
    FROM
        (`shipstation_orders_v2`
    JOIN `order_keys` ON ((`order_keys`.`order_key` = `shipstation_orders_v2`.`order_id`)))) `t`)


JOIN `shipstation_fulfillments`
ON (`shipstation_orders_v2`.`order_number` = `shipstation_fulfillments`.`order_number`)) `w`

Answer 1

作为几个注释...至于长表名称，没问题，但您可以使用对它们的别名引用，就像我通过示例所做的那样...ShipStation_Fulfillments SSF...“SSF”现在是一个别名打字时间较短，但仍然具有起源意义。

当通过“AS”更改查询中的列名称时，您只需要将列名称结果从其原始值更改为一开始的值，例如 SSF.order_date AS orderDate，其中您删除了“_”最终的列名称，但也在“总付费”中(但我讨厌带有嵌入空格的列名称，让用户界面处理标记事物，但那只是我)。

当输入 table.column (或 alias.column)时，通过驼峰命名法有助于提高可读性，而驼峰命名法稍微难以阅读，因为大脑会自然地分解为我们可读的单词。

基于查询的其他问题。外部查询部分无法识别内部封闭查询的别名，只能识别子选择的别名，就像使用“t”和“w”别名一样。

接下来，在进行 JOIN 时，我的偏好是按照查询中的表的方式读取它们，列出左侧第一个表，以及右侧连接到的所有内容。
如果从表 A 连接到表 B，ON 子句将是 ON A.KeyID = B.KeyID 与 B.KeyID = A.KeyID 特别是如果您要使用多个表... A->B、B->C , C->D

任何带有聚合(sum、avg、count、min、max 等)的查询都必须有一个“GROUP BY”子句来确定每条记录何时应该中断。在您的示例中，我假设原始销售订单中断。

虽然此查询不起作用，但这里是查询的清理版本，显示了上面的实现。

SELECT
      SSF.order_date AS OrderDate,
      SSF.order_number AS OrderNumber,
      (SSF.amount_paid + SSF.tax_paid) AS `Total Paid`,
      SSF.name,
      SSF.company
   FROM
      ( SELECT
                SSOv2.key_id,
                SSOv2.order_number,
                SSOv2.storename AS store,
                SSOv2.order_total - SSOv2.shippingPaid AS OrderPrice,
                SSOv2.ShippingPaid,
                SSOv2.order_total AS OrderTotal,
                SSOv2.QtyShipped,
                SSOv2.name,
                SSOv2.company,
                COUNT(SSOv2.order_key) AS orderCount,
                MAX(SSOv2.order_date) AS order_date,
                SUM(SSOv2.shippingpaid) AS `SUM shippingPaid`,
                SUM(SSOv2.order_total) AS `SUM Total Amount Paid`,
                SUM(SSOv2.qtyshipped) AS `SUM qtyShipped`
        FROM
            ( SELECT DISTINCT
                    OK.key_id AS key_id,
                    OK.order_key AS order_key,
                    SSOv2.order_number AS order_number,
                    SSOv2.order_id AS order_id,
                    SSOv2.order_date AS order_date,
                    SSOv2.storename AS storename,
                    SSOv2.order_total AS order_total,
                    SSOv2.qtyshipped AS qtyshipped,
                    SSOv2.shippingpaid AS shippingpaid,
                    SSOv2.name AS name,
                    SSOv2.company AS company
                 FROM
                    shipstation_orders_v2 SSOv2
                       JOIN order_keys 
                          ON SSOv2.order_id = OK.order_key
                       JOIN shipstation_fulfillments SSF
                          ON SSOv2.order_number = SSF.order_number ) t 
      ) w

接下来，在没有看到对解决查询至关重要的实际数据或列出的结构的情况下，我会要求您编辑现有的帖子。创建一个示例表，列出表、列和示例数据，以便我们可以了解您正在聚合并尝试从查询中获取的内容的基础。特别是显示每个订单和履行分别可能有多行以及您期望结果显示的示例答案。