SQL：在MIN查询后询问MAX结果

I'm trying to write a query where I select the highest Speed out of the PCs with the Lowest Ram.

我正在尝试编写一个查询,我选择具有最低Ram的PC中最高的速度。

Using this SQL Statement

使用此SQL语句

Select DISTINCT RAM FROM PC WHERE RAM = (Select MIN(RAM) FROM PC);

Gives this Result:

给出这个结果:

RAM
512

Using this SQL Statement

使用此SQL语句

Select Speed, Ram From PC WHERE Speed = (Select Max(Speed) from PC WHERE Ram = (Select DISTINCT RAM FROM PC WHERE RAM = (Select MIN(RAM) FROM PC)));

Gives this result

给出了这个结果

Speed RAM
3.2   512
3.2   1024

What am I doing wrong in my query? The second record should not be showing as we have already selected the Min(RAM)

我在查询中做错了什么?第二条记录不应该显示,因为我们已经选择了Min(RAM)

Sample Data:

CREATE TABLE PC (   model INT PRIMARY KEY,
                  speed NUMBER (4,2),
                  ram INT,
                        hd INT,
                  price NUMBER (7,2)
                );

INSERT INTO PC VALUES (1001, 3.66, 1024, 250, 2114);
INSERT INTO PC VALUES (1002, 2.10, 512, 250, 995);
INSERT INTO PC VALUES (1003, 1.42, 512, 80, 478);
INSERT INTO PC VALUES (1004, 2.80, 1024, 250, 649);
INSERT INTO PC VALUES (1005, 3.20, 512, 250, 630);
INSERT INTO PC VALUES (1006, 3.20, 1024, 320, 1049);
INSERT INTO PC VALUES (1007, 2.20, 1024, 200, 510);
INSERT INTO PC VALUES (1008, 2.20, 2048, 250, 770);
INSERT INTO PC VALUES (1009, 2.00, 1024, 250, 650);
INSERT INTO PC VALUES (1010, 2.80, 2048, 300, 770);
INSERT INTO PC VALUES (1011, 1.86, 2048, 160, 959);
INSERT INTO PC VALUES (1012, 2.80, 1024, 160, 649);
INSERT INTO PC VALUES (1013, 3.06, 512, 80, 529);

2 个解决方案

#1

You overcomplicate things.

你太复杂了。

For selecting the minimum ram, you can shorten you statement to this:

要选择最小ram,您可以将语句缩短为:

SELECT MIN(RAM) FROM PC

For selecting the maximum speed for the minimum ram, use this:

要选择最小ram的最大速度,请使用:

SELECT MAX(speed) FROM PC where RAM = (SELECT MIN(RAM) FROM PC)

#2

This isn't too difficult using analytic functions:

使用分析函数这并不困难:

SELECT model, speed, ram, price
FROM
(
    SELECT model, speed, ram, price, RANK() OVER (ORDER BY speed DESC) rn2
    FROM
    (
        SELECT model, speed, ram, price, RANK() OVER (ORDER BY ram) rn1
        FROM PC
    ) t
    WHERE rn1 = 1
) t
WHERE rn2 = 1;

The advantage of this approach is that it returns the entire matching records.

这种方法的优点是它返回整个匹配记录。

#1