按城市分组 [英] group by cities

问题描述

由于我被一个好人和程序员告知我应简化我的表。到目前为止，我已经做了一个新的表（x月，y城市，价值Nettotal）它的工作，但仍然我不明白为什么它不能分组的值（nettotal）按城市。它与月，但值只是从左到右开始，没有任何0留下。无论如何，我希望你会理解从源代码：

这里有查询：

 < cfquery name =GET_SALES_TOTALdatasource =＃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_TOTALdatasource =＃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 =3cellspacing =1class =color-border> 
< tr class =color-header> 
< td>< / td> 
< cfoutput query =GET_SALES_GRAND_TOTALgroup =city_id> 
< td>＃city_name＃< / td> 
< / cfoutput> 
< / tr> 
< cfoutput query =GET_SALES_TOTALgroup =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但是你想要这里--->< / td> 
< / cfoutput> 
< / tr> 
< / cfoutput> 
< tr class =color -head> 
< td>城市Overal< / td> 
< cfoutput query =GET_SALES_GRAND_TOTALgroup =city_id> 
< td> #tlformat（nettotal，2）＃<！--- format NETTOTAL这里--->< / td> 
< / cfoutput> 
< / tr> 
< / table> 
  

这是屏幕截图，使其更清晰： http://i56.tinypic.com/kcey69.jpg

除了分组的城市工作完美！

解决方案

看起来你的问题是，月和城市的组合。如果每个城市每个月都有销售，我们可以改进此代码，以便正确处理缺失条目。

一些数据库具体的语法，将帮助你使这样的表：例如，Access有 TRANSFORM ，Oracle和SQL服务器有 CUBE 和 ROLLUP ，MySQL具有 WITH ROLLUP 。但是，即使调整查询后，您仍然可能需要调整CF，所以让我们尝试使用更一般的形式处理。

而不是内连接，我们想要一个外部联接：所有城市加上销售的任何城市的销售。但是，我们也需要所有月份。在这个特殊情况下，我们可以使用笛卡尔乘积来获得月份和城市。 （要非常小心这些：你应该只使用它们的情况下，你真的需要一个记录的字段A和字段B的每个组合。它很容易使用错误的地方，并获得10,000记录，而不是100。）

我们来 Johan的建议并修改一些：

  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 
 WHEREASE_ALES = 1 
和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 
  

我有一个模糊的笛卡尔产品的格式与其他内部连接存在，所以试试，我们可以根据需要修改它。 p>

这应该给你一个查询，每个城市的销售额每个城市的值。外连接的确切形式可能需要更改，具体取决于您使用的数据库，但这至少应该使您朝正确的方向移动。

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

 < cfoutput query =GET_SALES_TOTALgroup =AY><！--- months first ---> 
< tr class =color-row><！--- month-specific stuff goes here ---> 
< td>＃ay＃< / td> 
  

成为

 < cfoutput query =GET_SALES_TOTALgroup =INVOICE_MONTH><！--- months first ---> 
< tr class =color-row><！--- month-specific stuff goes here ---> 
< td>＃invoice_month＃< / td> 
  

As I was advised by a good man and programmer I should simplify my table. So far I have made a new table (x-month,y-cities,value-Nettotal) it works, but still I didn't understand why it can't group the values (nettotal) by cities. It's OK with month, but the values just come starting from left to right without any 0 left behind. Anyway I hope you will understand everything from the source:

here are the queries:

<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>

and here is the table itself:

<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>

Here is the screenshot to make it more clear: http://i56.tinypic.com/kcey69.jpg

The other stuff except grouping the cities works perfectly!

解决方案

It looks like the problem that you're having is that there is not a record for every combination of month and city. The setup as it is now is fine if every city has sales every month, but we can improve this code so that it handles "missing" entries properly.

Some databases have specific syntax that will help you make a table like this: for example, Access has TRANSFORM, Oracle and SQL server have CUBE and ROLLUP, MySQL has WITH ROLLUP. However, you still might have to tweak your CF even after adjusting the query, so let's try working with what you have in a more general form.

Instead of an inner join, we want an outer join: all cities plus sales for any city that has sales. However, we also need all months. In this particular instance, we can use a Cartesian product to get months and cities. (Be very careful about these: you should only use them in situations where you really do want one record for every combination of field A and field B. It's easy to use one in the wrong place and get 10,000 records instead of 100.)

Let's take Johan's suggestion and modify it some:

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 

I'm a little fuzzy on the format for a Cartesian product with other inner joins present, so give this a try and we can modify it as needed.

That should give you a query with a value for every city for each month with sales. The exact form of the outer join may need to change, depending on the database you're using, but that should at least move you in the right direction.

Note that you will need to change your CF to match the revised query, but just this part, I think:

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

becomes

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

这篇关于按城市分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！