在查询中组合两个表并从中创建新列

发布于 2025-01-07 10:34:14 字数 1137 浏览 2 评论 0原文

我遇到了查询问题，我不完全确定可以通过数据库的设置方式来完成该查询。基本上，我将在查询中使用两个不同的表，例如“交易”和“门票价格”。它们看起来像这样（带有一些示例数据）：

    TRANSACTIONS
  Transation ID | Ticket Quantity | Total Price | Salesperson | Ticket Price ID
       5489              250            250            Jim               8765
       5465              50             150            Jim               1258  
       7898              36             45             Ann               4774


     Ticket Prices
   Ticket Price ID | Quantity | Price | Bundle Name
        8765           1          1         1 ticket, $1   
        4774           12         15        5 tickets, $10
        1258           1          3         1 ticket, $3

我的目标是一份报告，该报告按捆绑类型细分每个销售人员的销售额。结果表应该是这样的：

 Sales Volume/Salesperson
 Name | Bundle A | Bundle B | Bundle C | Total
 Jim     250         0           50      300
 Ann     0           36          0        36

我一直在网上搜索，似乎获得它的最佳方法是使用各种子查询，就正确显示列标题而言，它的效果很好，但它并没有就实际的数字总数而言，它是有效的。它基本上结合了数据，为每个销售人员提供了总读数（在本例中，吉姆和安在捆绑包 A 中都有 250 笔销售，在捆绑包 B 中都有 36 笔销售，依此类推）。有什么方法可以编写一个查询来给出正确的结果吗？或者至少是接近它的东西？感谢您的任何意见。

原文

I'm having issues with a query that I'm not ENTIRELY sure can be done with the way the database is set up. Basically, I'll be using two different tables in my query, let's say Transactions and Ticket Prices. They look like this (With some sample data):

    TRANSACTIONS
  Transation ID | Ticket Quantity | Total Price | Salesperson | Ticket Price ID
       5489              250            250            Jim               8765
       5465              50             150            Jim               1258  
       7898              36             45             Ann               4774


     Ticket Prices
   Ticket Price ID | Quantity | Price | Bundle Name
        8765           1          1         1 ticket, $1   
        4774           12         15        5 tickets, $10
        1258           1          3         1 ticket, $3

What I'm aiming for is a report, that breaks down each salesperson's sales by bundle type. The resulting table should be something like this:

 Sales Volume/Salesperson
 Name | Bundle A | Bundle B | Bundle C | Total
 Jim     250         0           50      300
 Ann     0           36          0        36

I've been searching the web, and it seems the best way of getting it like this is using various subqueries, which works well as far as getting the column titles properly displayed, but it doesn't work as far as the actual numerical totals. It basically combines the data, giving each salesperson a total readout (In this example, both Jim and Ann would have 250 sales in Bundle A, 36 in Bundle B, etc). Is there any way I can write a query that will give me the proper results? Or even something at least close to it? Thanks for any input.

分享到QQ

分享到微博

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

发布评论

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

随波逐流 2025-01-14 10:34:14

您可以使用 Oracle 中的 PIVOT 语句来执行此操作。查询可能如下所示：

WITH pivot_data AS (
          SELECT t.salesperson,p.bundle_name,t.ticket_quantity 
          FROM   ticket_prices p, transactions t
          where t.ticket_price_id = p.ticket_price_id
          )
  SELECT *
  FROM   pivot_data
  PIVOT (
             sum(ticket_quantity)        --<-- pivot_clause
         FOR bundle_name          --<-- pivot_for_clause
      IN  ('1 ticket, $1','5 tickets, $10', '1 ticket, $3' )   --<-- pivot_in_clause
        );

这将为您提供如下结果：

在此处输入图像描述

You can use the PIVOT statement in Oracle to do this. A query might look something like this:

WITH pivot_data AS (
          SELECT t.salesperson,p.bundle_name,t.ticket_quantity 
          FROM   ticket_prices p, transactions t
          where t.ticket_price_id = p.ticket_price_id
          )
  SELECT *
  FROM   pivot_data
  PIVOT (
             sum(ticket_quantity)        --<-- pivot_clause
         FOR bundle_name          --<-- pivot_for_clause
      IN  ('1 ticket, $1','5 tickets, $10', '1 ticket, $3' )   --<-- pivot_in_clause
        );

which would give you results like this:

enter image description here

回复收藏 0 原文

~没有更多了~