该查询将循环运行数年 [英] Query that will loop through a range of years

问题描述

我正在寻找一种方法来使ACCESS查询返回几年的结果.由于查询中存在汇总计算，因此将BETWEEN语句更改为年份范围会导致错误.我可以使用UNION语句并每年重复一次代码，但这并不是很有效.可以仅使用SQL代码来完成，还是必须使用VB.我不了解VB，所以如果答案是需要VB，我将不胜感激.

I'm looking for a way to have an ACCESS query return results for a range of years. Since there are aggregate calculations in the query, changing the BETWEEN statement to a range of years results in an error. I could use UNION statements and repeat the code for each year but that isn't very efficient. Can it be done with just SQL code or does VB have to be used. I don't know VB so if the answer is that VB is needed I would appreciate detailed information.

ACCESS查询的代码如下:

The code for the ACCESS query is below:

SELECT DISTINCT
    DatePart("yyyy",sale_date) AS [YEAR],

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#) AS [Gross Sales],

    (SELECT Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#) AS COGS,

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2) 
     FROM INVENTORY 
     INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#) AS [Sales Margin],

    Round((((SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2) 
             FROM INVENTORY 
             INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
             WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009# )
             /
            (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0),2) 
             FROM SALES_RECEIPT
             INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
             WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#))*100),0) & "%" AS [Profit Margin],

    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * deductions_inventory.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_INVENTORY ON INVENTORY.INVENTORY_ID = DEDUCTIONS_INVENTORY.INVENTORY_ID
     WHERE DEDUCTIONS_INVENTORY.ENTRY_DATE Between #1/1/2009# And #12/31/2009# ) AS [Inventory Deductions],

    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * DEDUCTIONS_EXPENSE.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_EXPENSE ON INVENTORY.INVENTORY_ID = DEDUCTIONS_EXPENSE.INVENTORY_ID
     WHERE DEDUCTIONS_EXPENSE.ENTRY_DATE Between #1/1/2009# And #12/31/2009# ) AS [Inventory Expenses], 

    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009# and REASON="Breakage-Theft") AS [Fixture & Chain Deductions], 

    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009# and REASON="Promotion") AS [Fixture & Chain Expenses], 

    (SELECT Round(Nz(Sum(returns.selling_price * returns.quantity), 0),2)
     FROM returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID
     WHERE returns.return_date Between #1/1/2009# And #12/31/2009# ) AS [Sales Returns], 

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) - Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * sales_receipt.quantity),0),2)
     FROM INVENTORY 
     INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * deductions_inventory.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_INVENTORY ON INVENTORY.INVENTORY_ID = DEDUCTIONS_INVENTORY.INVENTORY_ID
     WHERE DEDUCTIONS_INVENTORY.ENTRY_DATE Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(inventory.VENDOR_ACTUAL_PRICE * DEDUCTIONS_EXPENSE.quantity), 0),2)
     FROM INVENTORY INNER JOIN DEDUCTIONS_EXPENSE ON INVENTORY.INVENTORY_ID = DEDUCTIONS_EXPENSE.INVENTORY_ID
     WHERE DEDUCTIONS_EXPENSE.ENTRY_DATE Between #1/1/2009# And #12/31/2009#)
     -
    (SELECT Round(Nz(Sum(DEDUCTIONS_FIXTURES_CHAINS.VENDOR_ACTUAL_PRICE*DEDUCTIONS_FIXTURES_CHAINS.quantity),0),2) 
     FROM DEDUCTIONS_FIXTURES_CHAINS
     WHERE DEDUCTIONS_FIXTURES_CHAINS.ENTRY_DATE Between #1/1/2009# And #12/31/2009#) 
     -
    (SELECT Nz(Sum(returns.selling_price * returns.quantity), 0)
     FROM returns inner JOIN inventory ON INVENTORY.INVENTORY_ID = returns.INVENTORY_ID
     WHERE returns.return_date Between #1/1/2009# And #12/31/2009#) AS [Gross Profit]

FROM
    INVENTORY
    INNER JOIN SALES_RECEIPT ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
WHERE
    (((SALES_RECEIPT.SAle_date) Between #1/1/2009# And #12/31/2009#))
GROUP BY
    DatePart("yyyy",sale_date);

推荐答案

原则上，您需要将子查询中的所有Between #1/1/2009# And #12/31/2009#条件替换为与外部查询相关的条件.
如果照原样进行，则会有一个效率极低的查询，它将为外部查询中的每个记录计算总计.

In principle you need to replace all Between #1/1/2009# And #12/31/2009# conditions in the subqueries with a condition correlated with the outer query.
If you do it as is, you will have an utterly inefficient query that will calculate totals for each individual record in the outer query.

在这种情况下，除了给您提供单一年份数字(2009)之外，您似乎并没有使用外部查询.

You do not seem to be using the outer query for anything other than giving you the single year number (2009) in this case.

因此将外部查询更改为仅返回感兴趣的年份，并使子查询相关，例如

Therefore change the outer query to simply return the year numbers of interest, and make the subqueries correlated, e.g.

SELECT
    Year(years.year_start) AS [YEAR],

    (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] between years.year_start and years.year_end) AS [Gross Sales],

    (SELECT Round(Nz(Sum((Nz(inventory.VENDOR_ACTUAL_PRICE,0))*sales_receipt.quantity),0),2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID
     WHERE SALES_RECEIPT.[SALE_DATE] between years.year_start and years.year_end) AS COGS,

    etc
FROM
    (select
         DateSerial(Year(sale_date), 1, 1) as year_start,
         DateSerial(Year(sale_date), 12, 31) as year_end
     from SALES_RECEIPT
     where sale_date between #1/1/2009# And #12/31/2015#
     group by Year(sale_date)
    ) as years

这篇关于该查询将循环运行数年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！