有没有更好的方法来编写这个SQL语句?

GROUP BY 可能会允许SQL引擎更好地优化您的查询,但由于分组参数太多,因此读取起来比较困难。

根据 SQL Team 是考虑使用子查询。这通常会使 小组通过 语句更简单,使整个查询更容易阅读。

使用子查询:

SELECT orders.billerID, 
    orders.invoiceDate, 
    orders.txnID, 
    orders.bName, 
    orders.bStreet1, 
    orders.bStreet2, 
    orders.bCity, 
    orders.bState, 
    orders.bZip, 
    orders.bCountry, 
    orders.sName, 
    orders.sStreet1, 
    orders.sStreet2, 
    orders.sCity, 
    orders.sState, 
    orders.sZip, 
    orders.sCountry, 
    orders.paymentType, 
    orders.invoiceNotes, 
    orders.pFee, 
    orders.shipping, 
    orders.tax, 
    orders.reasonCode, 
    orders.txnType, 
    orders.customerID, 
    customers.firstName AS firstName, 
    customers.lastName AS lastName, 
    customers.businessName AS businessName, 
    orderStatus.statusName AS orderStatus, 
    orderItem.fees + orders.shipping + orders.tax AS orderTotal, 
    orderItem.fees + orders.shipping + orders.tax - payments.amount AS orderBalance 
FROM orders 
LEFT JOIN customers ON orders.customerID = customers.id 
LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id
LEFT JOIN 
    ( 
      SELECT orderID, SUM(itemPrice * itemQuantity) as fees
      FROM orderItems
      GROUP BY orderID
    ) orderItems ON orderItems.orderID = orders.id 
LEFT JOIN 
    ( 
      SELECT orderID, SUM(amount) as amount
      FROM payments
      GROUP BY orderID
    ) payments ON payments.orderID = orders.id

使用A 小组通过 以下内容:

SELECT orders.billerID, 
    orders.invoiceDate, 
    orders.txnID, 
    orders.bName, 
    orders.bStreet1, 
    orders.bStreet2, 
    orders.bCity, 
    orders.bState, 
    orders.bZip, 
    orders.bCountry, 
    orders.sName, 
    orders.sStreet1, 
    orders.sStreet2, 
    orders.sCity, 
    orders.sState, 
    orders.sZip, 
    orders.sCountry, 
    orders.paymentType, 
    orders.invoiceNotes, 
    orders.pFee, 
    orders.shipping, 
    orders.tax, 
    orders.reasonCode, 
    orders.txnType, 
    orders.customerID, 
    customers.firstName AS firstName, 
    customers.lastName AS lastName, 
    customers.businessName AS businessName, 
    orderStatus.statusName AS orderStatus, 
    SUM(orderItems.itemPrice * orderItems.itemQuantity) + orders.shipping + orders.tax AS orderTotal, 
    SUM(orderItems.itemPrice * orderItems.itemQuantity) + orders.shipping + orders.tax - SUM(payments.amount) AS orderBalance 
FROM orders 
LEFT JOIN customers ON orders.customerID = customers.id 
LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id
LEFT JOIN orderItems ON orderItems.orderID = orders.id 
LEFT JOIN payments ON payments.orderID = orders.id
GROUP BY 
    orders.billerID, 
    orders.invoiceDate, 
    orders.txnID, 
    orders.bName, 
    orders.bStreet1, 
    orders.bStreet2, 
    orders.bCity, 
    orders.bState, 
    orders.bZip, 
    orders.bCountry, 
    orders.sName, 
    orders.sStreet1, 
    orders.sStreet2, 
    orders.sCity, 
    orders.sState, 
    orders.sZip, 
    orders.sCountry, 
    orders.paymentType, 
    orders.invoiceNotes, 
    orders.pFee, 
    orders.shipping, 
    orders.tax, 
    orders.reasonCode, 
    orders.txnType, 
    orders.customerID, 
    customers.firstName, 
    customers.lastName, 
    customers.businessName, 
    orderStatus.statusName  

小组通过 解释:

你可以 分组依据 收集具有相似数据的记录。对于我的示例,我将使用一个简单的product表 Category , Name 和 Price 柱。如果我将数据分组 类别 我可以聚合(即 SUM , COUNT , MIN , MAX 等)基于任何其他列。因为我是按 类别 结果记录将具有唯一值的列 类别 . 其他任何列可能返回不同的值,因此不能包含在select语句中。

名称、类别、价格
青椒,青椒,1.50
橘子辣椒,辣椒,2.50
黄辣椒,辣椒,2.50
柠檬,柑橘,1.00
橙子,柑橘,1.00
酸橙,柑橘,1.00

SELECT 
    Category, /* This is unique because it is in the GROUP BY clause */
    AVG(Price) AS AveragePrice,
    MAX(Price) AS MaxPrice,
    MIN(Price) AS MinPrice
    /* , Name */  /* This is invalid because it is not in the GROUP BY clause */
                  /* The values are not unique so SQL does not know what to return */
FROM Produce
GROUP BY Category