mysql - 检索客户收入

Question

我想创建一份包含前 20 位客户(基于收入)的报告。

我正在使用查询:

SELECT dbo.CustTable.AccountNum
,dbo.dirpartytable.NAME
,dbo.hcmworker.PERSONNELNUMBER
,dbo.CustInvoiceJour.SALESBALANCE
,dbo.custinvoicejour.QTY
FROM dbo.CustTable
inner JOIN dbo.HCMWORKER ON dbo.HCMWORKER.RECID = dbo.CustTable.KEV_Worker
inner join dbo.custInvoiceJour on CustInvoiceJour.OrderAccount = CustTable.AccountNum
inner join dbo.dirpartytable on dirpartytable.recid = custtable.PARTY
where CustTable.KEV_Worker = '5633561745'
ORDER BY SalesBalanceMst DESC

我找不到客户收入的关系，毕竟，这就是我想要对报告进行排序的方式。我在构建报告时正在对 SalesBalanceMST 进行排序。此外，我在执行此查询时获得了多条记录。

我做错了什么？

编辑:我现在意识到我正在显示每个发票日志，我如何才能显示客户的总收入？

Answer 1

AX 2012 中的类似搜索:

CustInvoiceJour CustInvoiceJour;
CustTable CustTable;
DirPartyTable DirPartyTable;
select forceLiterals generateonly sum(SalesBalanceMST), sum(Qty) from CustInvoiceJour
    where CustInvoiceJour.OrderAccount == '102372200'
       && CustInvoiceJour.InvoiceDate  >  today()-365
    join TableId from CustTable
    group AccountNum
    where CustTable.AccountNum == CustInvoiceJour.OrderAccount
    join TableId from DirPartyTable
    group Name
    where DirPartyTable.RecId == CustTable.Party;
info(CustInvoiceJour.getSQLStatement());

这显示了以下 SQL:

SELECT SUM(T1.SALESBALANCEMST),SUM(T1.QTY),T2.ACCOUNTNUM,T3.NAME 
FROM CUSTINVOICEJOUR T1 
CROSS JOIN CUSTTABLE T2 
CROSS JOIN DIRPARTYTABLE T3 
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'xxx')) 
AND ((T1.ORDERACCOUNT=N'102372200') 
AND (T1.INVOICEDATE>{ts '2015-11-06 00:00:00.000'}))) 
AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'xxx')) 
AND (T2.ACCOUNTNUM=T1.ORDERACCOUNT)) 
AND ((T3.PARTITION=5637144576) 
AND (T3.RECID=T2.PARTY))    
GROUP BY T2.ACCOUNTNUM,T3.NAME 
ORDER BY T2.ACCOUNTNUM,T3.NAME 

与您的查询有何不同:

• 没有加入 HcmWorker，因为我没有您的自定义字段。
• 使用 sum() 进行聚合
• 选择 InvoiceDate
• 选择 OrderAccount
• DataAreaId 的选择，对性能非常重要，隐含在 AX 中
• 选择 Partition，对性能非常重要，隐含在 AX 中

您不能直接对总和进行排序，但可以对 a nested SQL query 进行排序.