按城市分组

Question

正如一位好人和程序员建议我我应该简化我的桌子。到目前为止，我已经制作了一个新表（x-month，y-cities，value-Nettotal）它可以工作，但我仍然不明白为什么它不能按城市对值（nettotal）进行分组。月份没问题，但值只是从左到右开始，没有留下任何 0。无论如何，我希望您能从源头理解所有内容：

这里是查询：

<cfquery name="GET_SALES_TOTAL" datasource="#dsn#">
SELECT
    SUM(COALESCE(nettotal,0)) nettotal,
    SC.CITY_ID,
    DATEPART(MM,INVOICE_DATE) AY,
    C.FULLNAME,
    SC.CITY_NAME
FROM    
    #DSN2_ALIAS#.INVOICE I,
    SETUP_CITY SC,
    COMPANY C
WHERE
    C.COMPANY_ID=I.COMPANY_ID
    AND SC.CITY_ID=C.CITY
    AND PURCHASE_SALES=1
GROUP BY
    DATEPART(MM,INVOICE_DATE),
    SC.CITY_ID,
    C.FULLNAME,
    SC.CITY_NAME
ORDER BY 
      AY,CITY_ID
</cfquery>

<cfquery name="GET_SALES_GRAND_TOTAL" datasource="#dsn#">
SELECT SUM(NETTOTAL) NETTOTAL,SC.CITY_ID,SC.CITY_NAME
FROM #DSN2_ALIAS#.INVOICE I,SETUP_CITY SC,COMPANY C
WHERE C.COMPANY_ID=I.COMPANY_ID AND SC.CITY_ID=C.CITY AND PURCHASE_SALES=1
GROUP BY SC.CITY_ID, SC.CITY_NAME
ORDER BY CITY_ID,CITY_NAME
</cfquery>

这里是表格本身：

<table cellpadding="3" cellspacing="1" class="color-border">
<tr class="color-header">
    <td></td>
    <cfoutput query="GET_SALES_GRAND_TOTAL" group="city_id">
        <td>#city_name#</td>
    </cfoutput>
</tr>
<cfoutput query="GET_SALES_TOTAL" group="AY"><!--- months first --->
    <tr class="color-row"><!--- month-specific stuff goes here --->
        <td>#ay#</td>
        <cfoutput group="city_id"><!--- city-specific stuff --->
            <td>#tlformat(nettotal,2)#<!--- format NETTOTAL however you want here ---></td>
        </cfoutput>
    </tr>
</cfoutput>
<tr class="color-header">
    <td>City Overal</td>
    <cfoutput query="GET_SALES_GRAND_TOTAL" group="city_id">
        <td>#tlformat(nettotal,2)#<!--- format NETTOTAL here ---></td>
    </cfoutput>
</tr>
</table>

这里是屏幕截图，使其更加清晰：

除了对城市进行分组之外，其他内容都完美运行！

Answer 1

您遇到的问题似乎是没有针对月份和城市的每种组合的记录。如果每个城市每月都有销售，那么现在的设置就很好，但是我们可以改进此代码，以便它正确处理“丢失”的条目。

有些数据库具有特定的语法，可以帮助您创建如下表：例如，Access 有 TRANSFORM，Oracle 和 SQL Server 有 CUBE 和 ROLLUP >，MySQL有WITH ROLLUP。然而，即使在调整查询之后，您仍然可能需要调整 CF，所以让我们尝试使用更通用的形式。

我们不需要内部联接，而是需要外部联接：所有城市加上任何有销售额的城市的销售额。然而，我们也需要所有月份。在这个特定的例子中，我们可以使用笛卡尔积来获取月份和城市。 （对这些要非常小心：您应该只在您确实希望字段 A 和字段 B 的每个组合都有一条记录的情况下使用它们。很容易在错误的地方使用它们并获得 10,000 条记录而不是 100 条记录。

）采取约翰的建议并对其进行一些修改：

SELECT SUM(COALESCE(NETTOTAL,0)) NETTOTAL
  ,SC.CITY_ID
  ,SC.CITY_NAME
  ,M.INVOICE_MONTH                     
FROM SETUP_CITY SC
  LEFT OUTER JOIN COMPANY C ON SC.CITY_ID = C.CITY                   
  LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I ON C.COMPANY_ID = I.COMPANY_ID
  , (SELECT DISTINCT DATEPART(MM,INVOICE_DATE) INVOICE_MONTH FROM #DSN2_ALIAS#.INVOICE) M
WHERE PURCHASE_SALES = 1   
  AND DATEPART(MM,I.INVOICE_DATE) = M.INVOICE_MONTH                  
GROUP BY SC.CITY_ID, SC.CITY_NAME, M.INVOICE_MONTH
ORDER BY SC.CITY_NAME, M.INVOICE_MONTH 

我对存在其他内连接的笛卡尔积的格式有点模糊，所以尝试一下，我们可以根据需要修改它。

这将为您提供一个查询，其中包含每个城市每月的销售额值。外连接的确切形式可能需要更改，具体取决于您使用的数据库，但这至少应该使您朝着正确的方向前进。

请注意，您需要更改 CF 以匹配修改后的查询，但我认为只是这部分

<cfoutput query="GET_SALES_TOTAL" group="AY"><!--- months first --->
    <tr class="color-row"><!--- month-specific stuff goes here --->
        <td>#ay#</td>

：

<cfoutput query="GET_SALES_TOTAL" group="INVOICE_MONTH"><!--- months first --->
    <tr class="color-row"><!--- month-specific stuff goes here --->
        <td>#invoice_month#</td>

Answer 2

SUM 有一个恼人的功能，如果求和值之一为 null，则计算结果为 null。

尝试将 SELECT SUM(NETTOTAL) NETTOTAL 更改为 SELECT SUM(COALESCE(nettotal,0)) nettotal

查询语法备注

请不要使用隐式 where 连接令人困惑、容易出错，而且不利于您的心理健康。
使用显式联接：

SELECT SUM(NETTOTAL) NETTOTAL
  ,SC.CITY_ID
  ,SC.CITY_NAME                     
FROM #DSN2_ALIAS#.INVOICE I
  INNER JOIN SETUP_CITY SC ON SC.CITY_ID = C.CITY
  INNER JOIN COMPANY C ON C.COMPANY_ID = I.COMPANY_ID                   
WHERE PURCHASE_SALES = 1                     
GROUP BY SC.CITY_ID, SC.CITY_NAME                     
ORDER BY SC.CITY_NAME 

这样您可以将联接条件与过滤条件分开，并且如果您需要联接大量表，您的查询将更易于维护和扩展。
作为奖励，您将摆脱 SQL '89 的困扰，这样您就可以最终离开 Loveshack 。

Answer 3

在 ColdFusion 中，当使用分组 cfoutput 时，您分组的任何列也应该位于查询的 ORDER BY 部分中。因此，将第一个查询更改为 ORDER BY AY, city_id
并将第二个查询更改为 ORDER BY SC.CITY_NAME, city_id