如何在Oracle中显示具有最高价值的记录? [英] How to display the record with the highest value in Oracle?

问题描述

我有4个具有以下结构的表:

I have 4 tables with the following structure:

表artist:

artistID lastname firstname nationality dateofbirth datedcease

表work:

workId title copy medium description artist ID

表Trans:

TransactionID Date Acquired Acquistionprice datesold askingprice salesprice customerID workID

表Customer:

customerID lastname Firstname street city state zippostalcode country areacode phonenumber email

第一个问题是哪个艺术家的artsold作品最多，售出了多少艺术家.

First question is which artist has the most works of artsold and how many of the artist works have been sold.

我的SQL查询是这样:

My SQL query is this:

SELECT * From dtoohey.artist A1 
INNER JOIN 
(
    SELECT COUNT(W1.ArtistID) AS COUNTER, artistID  FROM dtoohey.trans T1
    INNER JOIN dtoohey.work W1
    ON W1.workid = T1.Workid
    GROUP BY W1.artistID
) TEMP1
ON TEMP1.artistID = A1.artistID
WHERE A1.artistID = TEMP1.artistId
ORDER BY COUNTER desc;

我要获取整个表格，但是我只想只显示最高计数的第一行?

I am to get the whole table but I only want show only the first row which is the highest count how do I do that??

我尝试插入WHERE ROWNUM <=1，但显示的艺术家ID为1

I have tried inserting WHERE ROWNUM <=1 but it shows artist ID with 1

qns 2是指艺术家作品的平均利润最高的销售(即，艺术家每笔艺术品销售的平均利润)，以及该金额是多少.

qns 2 is sales of which artist's work have resulted in the highest average profit (i.e) the average of the profits made on each sale of worksby an artist), and what is that amount.

我的SQL查询是:

SELECT A1.artistid, A1.firstname FROM
(
    SELECT 
        (salesPrice - AcquisitionPrice) as profit, 
        w1.artistid as ArtistID 
    FROM dtoohey.trans T1
    INNER JOIN dtoohey.WORK W1
    on W1.workid = T1.workid
) TEMP1
INNER JOIN dtoohey.artist A1
ON A1.artistID = TEMP1.artistID
GROUP BY A1.artistid
HAVING MAX(PROFIT) = AVG(PROFIT);

我无法执行

我在下面尝试过查询，但仍然无法获取，并不断出现错误，右括号缺失了

I have tried query below but still not able to get it keep getting the error missing right parenthesis

SELECT A1.artistid, A1.firstname, TEMP1.avgProfit
FROM 
(
    SELECT 
        AVG(salesPrice - AcquisitionPrice) as avgProfit, 
        W1.artistid as artistid
    FROM dtoohey.trans T1
    INNER JOIN dtoohey.WORK W1
    ON W1.workid = T1.workid
    GROUP BY artistid
    ORDER BY avgProfit DESC
    LIMIT 1
) TEMP1
INNER JOIN dtoohey.artist A1
ON A1.artisid = TEMP1.artistid

推荐答案

有时ORA-00907: missing right parenthesis的确切含义是:我们有一个左括号，没有匹配的右括号.但也可能由于语法错误而在括号内的语句的一部分中抛出该错误.

Sometimes ORA-00907: missing right parenthesis means exactly that: we have a left bracket without a matching right one. But it can also be thrown by a syntax error in a part of a statement bounded by parentheses.

这是这里的第二个原因:LIMIT是Oracle无法识别的Mysql命令.您可以在此处使用分析功能:

It's that second cause here: LIMIT is a Mysql command which Oracle does not recognise. You can use an analytic function here:

SELECT A1.artistid, A1.firstname, TEMP1.avgProfit
FROM 
(
    select  artistid
            , avgProfit
            , rank() over (order by avgProfit desc) as rnk
    from (
        SELECT 
            AVG(salesPrice - AcquisitionPrice) as avgProfit, 
            W1.artistid as artistid
        FROM dtoohey.trans T1
        INNER JOIN dtoohey.WORK W1
        ON W1.workid = T1.workid
        GROUP BY artistid
    ) 
) TEMP1
INNER JOIN dtoohey.artist A1
    ON A1.artisid = TEMP1.artistid
where TEMP1.rnk = 1

这使用RANK()函数，如果有几位艺术家获得相同的平均利润，则该函数将返回多个行.您可能要改用ROW_NUMBER().分析功能可能非常强大. 了解更多.

This uses the RANK() function which will return more than one row if several artists achieve the same average profit. You might want to use ROW_NUMBER() instead. Analytic functions can be very powerful. Find out more.

您可以将ROWN_NUMBER()，RANK()和DENSE_RANK()应用到任何top- n 问题.您也可以使用其中一个解决您的第一个问题.

You can apply ROWN_NUMBER(), RANK() and DENSE_RANK() to any top-n problem. You can use one of them to solve your first problem too.

但是平均利润为空."

"however the avg profit is null."

这可能是数据问题.如果(salesPrice - AcquisitionPrice)中的数字之一为null，则结果将为null，并且不会包括在平均值中.如果某个艺术家的所有行均为null，则AVG()将为null.

That's probably a data issue. If one of the numbers in (salesPrice - AcquisitionPrice) is null the result will be null, and won't be included in the average. If all the rows for an artist are null the AVG() will be null.

发生这种情况时，排序顺序将把NULL放在最后.但是当PARTITION BY子句按AvgProfit desc排序时，会将NULL结果放在第1位.解决方案是在windowing子句中使用NULLS LAST:

As it happens the sort order will put NULL last. But as the PARTITION BY clause sorts by AvgProfit desc that puts the NULL results at rank 1. The solution is to use the NULLS LAST in the windowing clause:

            , rank() over (order by avgProfit desc nulls last) as rnk

这将确保您在顶部获得非空结果(前提是至少有一位艺术家在两列中都有值).

This will guarantee you a non-null result at the top (providing at least one of your artists has values in both columns).

这篇关于如何在Oracle中显示具有最高价值的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！