SQL无法弄清楚如何正确连接

Having a joining issue

有加入问题

I have one table that has an ID and description column the seasons are new, but the descriptions repeat. so we can have an Adult price for season 34 and an adult price for season 35 etc.

我有一个表具有ID和描述列,季节是新的,但描述重复。所以我们可以得到第34季的成人价和第35季的成人价等。

select * from tableA 
-- returns id, description, price, season etc ... 
-- 1 "GT Adult" 10 34
-- 2 "GT Child" 5 34
-- 3 "GT Senior" 8 34 
-- 1 "GT Adult" 11 35
-- 2 "GT Child" 6 35
-- etc.

TableB has multiple columns these columns have names/headers that correspond to the description column.

TableB有多列,这些列具有与描述列对应的名称/标题。

select * from tableB 
-- returns customer_no adult, child, senior, order_dt, order_data, season, perf_no etc. 
-- returns 112 0, 12, 2, order_dt, order_data, order_season. 
-- returns 415 23, 0, 0, order_dt, order_data, order_season.

Basically each customer places an order for a given amount of tickets of each type. The information we can use to join is season and performance that they match on ...

基本上每个客户都会订购每种类型的给定数量的门票。我们可以用来加入的信息是他们匹配的季节和表现......

but i can't figure out how to say for customer 112 since he got 12 children's tickets he should be charged 5 a ticket, and 2 senior tickets he should be charged 8 dollar for each of those tickets. Where as customer 415 should be charged $10 for each of the 23 tickets. by season.

但我无法弄清楚如何为客户112说,因为他有12张儿童票,他应该被收取5张票,而2张高票,他应该收取每张票8美元。如果客户415应该为23张门票中的每一张收取10美元。按季节。

The only thing I can do for sure is join on season but how do i join on the correct column. Please advise.

我唯一能做的就是加入赛季,但我如何加入正确的专栏。请指教。

2 个解决方案

#1

You can use PIVOT to get all ticket prices in a single row per season:

您可以使用PIVOT每季获得一行的所有门票价格:

SELECT season, [GT Adult], [GT Child], [GT Senior]
FROM (
   SELECT season, price, [description]
   FROM tableA
   ) source
   PIVOT (
      MAX(price)
      FOR [description] IN ([GT Adult], [GT Child], [GT Senior])
   ) pvt

Given the sample data quoted in the OP, the above produces sth like:

鉴于OP中引用的样本数据,上面产生的结果如下:

season  GT Adult    GT Child    GT Senior
-----------------------------------------
34      10          5           8
35      11          6           NULL

Then you can perform a simple INNER JOIN operation in order to get the total amount per customer order:

然后,您可以执行简单的INNER JOIN操作,以获得每个客户订单的总金额:

SELECT customer_no, adult * [GT Adult] + child *  [GT Child] + senior * [GT Senior] AS total
FROM tableB AS B
INNER JOIN ( 
   SELECT season, [GT Adult], [GT Child], [GT Senior]
   FROM (
      SELECT season, price, [description]
      FROM tableA) source
      PIVOT (
         MAX(price)
         FOR [description] IN ([GT Adult], [GT Child], [GT Senior])
      ) pvt
) t ON b.season = t.season

SQL Fiddle Demo

SQL小提琴演示

P.S. The above query works in SQL Server.

附:以上查询适用于SQL Server。

EDIT:

To simulate PIVOT in MySQL we have to use conditional aggregates:

要在MySQL中模拟PIVOT,我们必须使用条件聚合:

select season,
       sum(if(description='GT Adult', price ,null)) as adultPrice,
       sum(if(description='GT Child', price ,null)) as childPrice,
       sum(if(description='GT Senior', price ,null)) as seniorPrice
from tableA 
group by season;

The above query gives us the result set with which a JOIN operation can be performed:

上面的查询为我们提供了可以执行JOIN操作的结果集:

SELECT customer_no, adult * adultPrice + child *  childPrice + senior * seniorPrice AS total
FROM tableB AS b
INNER JOIN ( 
  SELECT season,
       SUM(IF(description='GT Adult', price ,null)) AS adultPrice,
       SUM(IF(description='GT Child', price ,null)) AS childPrice,
       SUM(IF(description='GT Senior', price ,null)) AS seniorPrice
  FROM tableA 
  GROUP BY season) AS a ON b.season = a.season

MySQL Demo here

MySQL演示在这里

#2

I don't think you can do what you want with the tables you have. There is no clear way to associate the "adult" column in TableB with the row that contains "GT Adult" in TableA.

我不认为你能用自己的桌子做你想做的事。没有明确的方法将TableB中的“成人”列与TableA中包含“GT Adult”的行相关联。

You could redesign TableB to solve this:

你可以重新设计TableB来解决这个问题:

TableB (customer_no, ticket_type, quantity, order_dt, ...)

So for customer 112 we would have in TableB:

因此,对于客户112,我们将在表B中:

112, "GT_Child", 12 ...
112, "GT_Senior", 2 ...

So you can answer your queries by joining on ticket_type (and possibly other columns if you need them).

因此,您可以通过加入ticket_type(如果需要,可能还有其他列)来回答您的查询。

If possible, you should move the details of the order itself into a third table (let's call it TableC) and allocate an order number. So we would now have TableA as you have it and then:

如果可能,您应该将订单本身的详细信息移动到第三个表(我们称之为TableC)并分配订单号。所以我们现在拥有TableA,然后:

TableB (order_no, customer_no, ticket_type, quantity)
TableC (order_no, order_dt, season ...)

#1