时间戳

首页 > 解决方案 > SQL 特定的查询更正

sql - SQL 特定的查询更正

问题描述

谁能告诉我，这个查询有什么问题，我是使用 Oracle APEX 的学生。我收到了这个错误：ORA-00918: column ambiguously defined

SELECT t.BandDesc, TypeDesc, CustomerID, PaymentAmount, TotalPayment FROM

(SELECT Room_Dim.BandDesc, Room_Dim.TypeDesc, CustomerID, PaymentAmount
FROM Room_Dim, Fact_Bookings_Payments
WHERE Room_Dim.RoomID = Fact_Bookings_Payments.RoomID)t 

LEFT JOIN 

(SELECT ConcertID, CustomerID, TotalPayment
FROM Fact_Buy)t2

ON t.CustomerID = t2.CustomerID;

我需要找到：对于每个房间乐队和音乐会，产生 4 星级 TypeDesc 房间的累积收入

标签： sqloracle-apex

解决方案

t并且t2两者都有一个CustomerId并且您在连接条件中使用它；但是，在您选择的第一行CustomerId没有指定它来自哪个表。这可能是错误的来源。

SELECT t.BandDesc,
       TypeDesc,
       t.CustomerID,        -- Either this
       t2.CustomerID,       -- Or this
       PaymentAmount,
       TotalPayment
FROM   (
  SELECT Room_Dim.BandDesc,
         Room_Dim.TypeDesc,
         CustomerID,
         PaymentAmount
  FROM   Room_Dim
         INNER JOIN Fact_Bookings_Payments
         ON Room_Dim.RoomID = Fact_Bookings_Payments.RoomID
) t 
LEFT JOIN (
  SELECT ConcertID, CustomerID, TotalPayment
  FROM Fact_Buy
) t2
ON t.CustomerID = t2.CustomerID;

推荐阅读