获取数据的Sql获取数据>询问价格 [英] Sql getting data where acquisitionprice > askingprice

问题描述

Trans



（TransID，AcquisitionPrice，AskingPrice，WorkID）



工作



（工作ID，标题，艺术家ID）



艺术家



（ArtistID，FirstName，姓氏）



问题是列出所售出的艺术品的工作ID，标题和艺术家名称。平均销售价格，以及他们售出的价格。



因此输出应该是：



WorkID Title FirstName LastName AcquisitionPrice







我在获取此输出并计算平均价格时遇到问题。 MySQL总是说有些东西丢失。



我尝试过：



SELECT T.WorkID，w.Title，A.LastName，A.FirstName

FROM trans T

JOIN工作w ON T.WorkID = w.WorkID

JOIN艺术家A

ON T.acquisitionprice> T.ASKINGPRICE;

（我确定最后一部分是错的）

解决方案

我不是MySQL专家，但我认为它应该是这样的：

  SELECT  T.WorkID，w.Title，A.LastName，A .FirstName 
  FROM  trans T 
  JOIN 工作w  ON  T.WorkID = w.WorkID 
  JOIN 艺术家A  ON  A.ArtistID = w.ArtistID 
  WHERE  T.acquisitionprice> T.ASKINGPRICE 

Trans

(TransID, AcquisitionPrice, AskingPrice, WorkID)

Work

(WorkID, Title, Artist ID)

Artist

(ArtistID, FirstName, LastName)

Question is to list the work ID, title and artist name of all the works of art that sold for more than the average sales price, and the price they sold for.

Hence output should be:

WorkID Title FirstName LastName AcquisitionPrice



I have problem getting this output and calculating the average price. MySQL always says there is something missing.

What I have tried:

SELECT T.WorkID, w.Title, A.LastName, A.FirstName
FROM trans T
JOIN Work w ON T.WorkID = w.WorkID
JOIN Artist A
ON T.acquisitionprice > T.ASKINGPRICE;
(I'm sure the last part is wrong)

解决方案

I'm not an MySQL specialist, but I think it should look like:

SELECT T.WorkID, w.Title, A.LastName, A.FirstName
FROM trans T
JOIN Work w ON T.WorkID = w.WorkID
JOIN Artist A ON A.ArtistID = w.ArtistID
WHERE T.acquisitionprice > T.ASKINGPRICE

这篇关于获取数据的Sql获取数据>询问价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！