按计数顺序排序不正确-SQL(MS Access 2007) [英] Order by count not sorting correctly - SQL (MS Access 2007)

问题描述

概述: 比较各种账户作为代理人的农产品的日常交易，以发现代理人和公司持股之间的差异，仅显示最新信息；当前交易中的代码，而不是先前交易中的代码.

目标: 通过代码(例如苹果)以最高的频率对交易进行排序.

当前表:

--------------------------------------------------------------------------------------------------------------
|        DATE | ACCT_CODE | TYPE | TYPE_DESCRIPTION |    CODE | DIFFERENCE | AGENT_HOLDING | COMPANY_HOLDING |
--------------------------------------------------------------------------------------------------------------
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |      -1500 |             0 |            1500 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |       1500 |          2000 |             500 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |          0 |             0 |               0 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |          0 |         49797 |           49797 |
| 02 Aug 2012 | acc170000 | prod |          produce |  grapes |       2500 |          2500 |               0 |
| 02 Aug 2012 | acc170000 | prod |          produce |  grapes |      -1500 |             0 |            1500 |
| 02 Aug 2012 | acc008221 | caus |             cash |    cash |          0 |             0 |               0 |
| 02 Aug 2012 | acc008221 | caus |             cash | carrots |          0 |         43273 |           43274 |
| 01 Aug 2012 | acc008221 | caus |             cash |    cash |          0 |             0 |               0 |
| 01 Aug 2012 | acc008221 | caus |             cash | carrots |          0 |         43273 |           43274 |
| 02 Aug 2012 | acc179185 | prod |          produce |   mango |        125 |          2775 |            2650 |
| 02 Aug 2012 | acc179185 | prod |          produce |   mango |     -57646 |        453639 |          511286 |
| 02 Aug 2012 | acc636903 | prod |          produce |   mango |          0 |        481081 |          481081 |
| 02 Aug 2012 | acc001049 | prod |          produce |   mango |       2500 |          2500 |               0 |
| 02 Aug 2012 | acc001189 | prod |          produce |   apple |      -1091 |             0 |            1091 |
--------------------------------------------------------------------------------------------------------------

代码:

SELECT 
    Count(CurrentTrade.Code) AS CountOfCode, 
    CurrentTrade.date, 
    CurrentTrade.Acct_Code, 
    CurrentTrade.Type, 
    CurrentTrade.Code, 
    CurrentTrade.Agent_Holding, 
    CurrentTrade.Company_Holding, 
    CurrentTrade.Difference
FROM CurrentTrade 
LEFT JOIN PrevTrade 
    ON CurrentTrade.Company_Holding=PrevTrade.Company_Holding
WHERE (((PrevTrade.Company_Holding) Is Null) AND ((CurrentTrade.Code)<>'cash'))
GROUP BY 
    CurrentTrade.Code,CurrentTrade.date, 
    CurrentTrade.Acct_Code, 
    CurrentTrade.Type, 
    CurrentTrade.Agent_Holding, 
    CurrentTrade.Company_Holding, 
    CurrentTrade.Difference
ORDER BY count(CurrentTrade.Code) ASC;

当前结果:

---------------------------------------------
| COUNTOFCODE |        DATE | TYPE |   CODE |
---------------------------------------------
|           1 | 02 Aug 2012 | prod | grapes |
|           1 | 02 Aug 2012 | prod | grapes |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
---------------------------------------------

问题: 如何解决使用正确的ORDER BY频率(苹果，芒果，葡萄)的问题?

解决方案

我认为问题在于您所做的分组过于精细.

在这种情况下，从GROUP BY子句中排除三个金额列(AgentHolding，CompanyHolding和Difference)并在输出中将它们汇总(sum)可能很有意义. /p>

以下是此更改将产生的示例: http://www.sqlfiddle. com/#！3/fc4d4/7

注意:由于我不知道left join的结构和数据，因此省略了left join与PrevTrade表，但是希望这可以说明目的.

Overview: Compare day to day trades for produce as agent for various accounts to find difference between agent and company holdings showing only what is new; codes in current trades, but not previous trades.

Objective: Sort the trades by code (i.e. apples) with highest frequency.

Current table:

--------------------------------------------------------------------------------------------------------------
|        DATE | ACCT_CODE | TYPE | TYPE_DESCRIPTION |    CODE | DIFFERENCE | AGENT_HOLDING | COMPANY_HOLDING |
--------------------------------------------------------------------------------------------------------------
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |      -1500 |             0 |            1500 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |       1500 |          2000 |             500 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |          0 |             0 |               0 |
| 02 Aug 2012 | acc107101 | prod |          produce |   apple |          0 |         49797 |           49797 |
| 02 Aug 2012 | acc170000 | prod |          produce |  grapes |       2500 |          2500 |               0 |
| 02 Aug 2012 | acc170000 | prod |          produce |  grapes |      -1500 |             0 |            1500 |
| 02 Aug 2012 | acc008221 | caus |             cash |    cash |          0 |             0 |               0 |
| 02 Aug 2012 | acc008221 | caus |             cash | carrots |          0 |         43273 |           43274 |
| 01 Aug 2012 | acc008221 | caus |             cash |    cash |          0 |             0 |               0 |
| 01 Aug 2012 | acc008221 | caus |             cash | carrots |          0 |         43273 |           43274 |
| 02 Aug 2012 | acc179185 | prod |          produce |   mango |        125 |          2775 |            2650 |
| 02 Aug 2012 | acc179185 | prod |          produce |   mango |     -57646 |        453639 |          511286 |
| 02 Aug 2012 | acc636903 | prod |          produce |   mango |          0 |        481081 |          481081 |
| 02 Aug 2012 | acc001049 | prod |          produce |   mango |       2500 |          2500 |               0 |
| 02 Aug 2012 | acc001189 | prod |          produce |   apple |      -1091 |             0 |            1091 |
--------------------------------------------------------------------------------------------------------------

Code:

SELECT 
    Count(CurrentTrade.Code) AS CountOfCode, 
    CurrentTrade.date, 
    CurrentTrade.Acct_Code, 
    CurrentTrade.Type, 
    CurrentTrade.Code, 
    CurrentTrade.Agent_Holding, 
    CurrentTrade.Company_Holding, 
    CurrentTrade.Difference
FROM CurrentTrade 
LEFT JOIN PrevTrade 
    ON CurrentTrade.Company_Holding=PrevTrade.Company_Holding
WHERE (((PrevTrade.Company_Holding) Is Null) AND ((CurrentTrade.Code)<>'cash'))
GROUP BY 
    CurrentTrade.Code,CurrentTrade.date, 
    CurrentTrade.Acct_Code, 
    CurrentTrade.Type, 
    CurrentTrade.Agent_Holding, 
    CurrentTrade.Company_Holding, 
    CurrentTrade.Difference
ORDER BY count(CurrentTrade.Code) ASC;

Current results:

---------------------------------------------
| COUNTOFCODE |        DATE | TYPE |   CODE |
---------------------------------------------
|           1 | 02 Aug 2012 | prod | grapes |
|           1 | 02 Aug 2012 | prod | grapes |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  apple |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
|           1 | 02 Aug 2012 | prod |  mango |
---------------------------------------------

Issue: How to fix to use correct ORDER BY frequency (apples, mango, grapes)?

解决方案

I think the problem is that you are doing the grouping too granular.

In this case it would probably make sense to exclude the three Amount columns (AgentHolding, CompanyHolding and Difference) from the GROUP BY clause and just aggregate them (sum) in the output.

Here's an example of what this change would produce: http://www.sqlfiddle.com/#!3/fc4d4/7

Note: I left out the left join with the PrevTrade table as I did not know its structure and data, but hopefully this illustrates the intent.

这篇关于按计数顺序排序不正确-SQL(MS Access 2007)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！