用于显示两个不同月份销售额的 SQL 查询

发布于 2024-12-01 06:21:44 字数 629 浏览 1 评论 0原文

我想知道在创建显示两个不同月份的销售额（在参数中选择）的查询时最常用的方法是什么。

我的数据库看起来像这样：

Posting Date    Company      Size    Sales
01/01/2011      Microsoft    1000    900

我已经有一个选择“年月”的参数。我想要的是有两个参数，以便我可以在 Microsoft Visual Studio 中并排比较“年月”的销售额。

所以查询应该有两个参数，@PostingDate1和@PostingDate2

感谢您的帮助！

--更新--

试图让这更容易理解。
可供选择的两个参数是“年月”
这样，当参数1选择“年月”：2011年1月，参数2：2011年2月时，结果表将如下所示（选择什么月份并不重要，只是结果会显示不同的结果）月）

Company     Size     Sales1    Sales2
Microsoft   1000     100       200

如果 2011 年 1 月的销售额为 100
2011 年 2 月销量为 200

原文

I was wondering what method is most used when creating a query that displays sales for two different months (selected in the parameter).

My database looks something like this:

Posting Date    Company      Size    Sales
01/01/2011      Microsoft    1000    900

I already have a parameter where "year month" is selected.
What I want is to have two parameters so that I can compare the sales in "year month" side by side in Microsoft Visual Studio.

So the query should have two parameters, @PostingDate1 and @PostingDate2

Thanks for any help!

--UPDATE--

Trying to make this more understandable.

The two parameters to select from will be "year month"

So that the result table will look like this when "year month" is selected for parameter 1: january 2011, and parameter 2: february 2011 (doesn´t matter what months are selected, just that the results will show the different months)

Company     Size     Sales1    Sales2
Microsoft   1000     100       200

That is if sales for january 2011 was 100

and sales for february 2011 was 200

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瀟灑尐姊 2024-12-08 06:21:44

我认为您想做一个CROSS JOIN，但我不完全确定我理解您的问题。如果您的查询结果只有一行，那么我建议使用 CROSS JOIN，如果不是，那么最好不要使用它。它应该是这样的：

SELECT A.[Posting Date] [Posting Date 1], A.Company Company1, A.Size Size1, A.Sales Sales1,
       B.[Posting Date] [Posting Date 2], B.Company Company2, B.Size Size2, B.Sales Sales2
FROM (SELECT [Posting Date], Company, Size, Sales
      FROM YourTable
      WHERE [Posting Date] = @PostingDate1) A
CROSS JOIN (SELECT [Posting Date], Company, Size, Sales
            FROM YourTable
            WHERE [Posting Date] = @PostingDate2) B

I think that you want to do a CROSS JOIN, but I'm not completely sure that I understood your question. If the results of your query are onle ONE row, then I recommend a CROSS JOIN, of not, then its probably better not to use it. It should be something like this:

SELECT A.[Posting Date] [Posting Date 1], A.Company Company1, A.Size Size1, A.Sales Sales1,
       B.[Posting Date] [Posting Date 2], B.Company Company2, B.Size Size2, B.Sales Sales2
FROM (SELECT [Posting Date], Company, Size, Sales
      FROM YourTable
      WHERE [Posting Date] = @PostingDate1) A
CROSS JOIN (SELECT [Posting Date], Company, Size, Sales
            FROM YourTable
            WHERE [Posting Date] = @PostingDate2) B

回复收藏 0 原文

请止步禁区 2024-12-08 06:21:44

我的答案：

我最终使用了“UNION”，不知道这是否更合适，但它摆脱了使用“CROSS JOIN”的冗余数据。

SELECT 
A.Company, A.Size, SUM(A.Sales) as Sales1, SUM(B.Sales2)
FROM
(
(SELECT Company, Size, Sales as Sales, 0 as Sales2
FROM Sales
WHERE Posting date = @PostingDate1) AS A
UNION
(SELECT Company, Size, 0 as Sales, Sales as Sales2
FROM Sales
WHERE Posting date = @PostingDate2)
) AS B
)
GROUP BY 
A.Company, A.Size

My answer:

I ended up using "UNION", don´t know whether this is more appropriate, but it got rid of the redundant data from using "CROSS JOIN".

SELECT 
A.Company, A.Size, SUM(A.Sales) as Sales1, SUM(B.Sales2)
FROM
(
(SELECT Company, Size, Sales as Sales, 0 as Sales2
FROM Sales
WHERE Posting date = @PostingDate1) AS A
UNION
(SELECT Company, Size, 0 as Sales, Sales as Sales2
FROM Sales
WHERE Posting date = @PostingDate2)
) AS B
)
GROUP BY 
A.Company, A.Size

回复收藏 0 原文

~没有更多了~