多个语句的最大值

Question

+----+-------+-----------+---------------+
| ID | Name  | Orderdate | Catalog Price |
+----+-------+-----------+---------------+
| 7b | 34-10 | NULL      |          3000 |
| 7b | 34-10 | NULL      |          3000 |
| 7b | 34-10 | NULL      |          2000 |
| 7b | 35-12 | PL-17     |          3000 |
| 8b | 35-11 | PL-18     |          2000 |
| 8b | 34-13 | PL-18     |          2000 |
| 8b | 34-14 | PL-18     |         2000  |
| 8b | 34-15 | PL-18     |         2000  |
| 9b | 35-12 | PL-19     |          2000  
| 9b | 35-11 | PL-19     |          2000 |
| 9b | 34-18 | PL-19     |          2000 |
| 9b | 34-19 | PL-19     |          2000 |
| 9b | 34-20 | PL-19     |          2000 |
+----+-------+-----------+---------------+

我需要Id以7开头的产品列表，其中名称以34开头，其中Orderdate = null，只有目录价格最高的记录

输出应该是

 ID Name    Orderdate   Catalog Price
7b  34-10   NULL    3000
7b  34-10   NULL    3000

我想我的解释不清楚，所以在此

步骤1 id从7开始

 ID Name    Orderdate   Catalog Price
    7b  34-10   NULL    3000
    7b  34-10   NULL    3000
    7b  34-10   NULL    2000
    7b  35-12   PL-17   3000

*第2步：*名字以34开头

 ID Name    Orderdate   Catalog Price
    7b  34-10   NULL    3000
    7b  34-10   NULL    3000
    7b  34-10   NULL    2000

步骤3，其中orderdate =null

ID  Name    Orderdate   Catalog Price
    7b  34-10   NULL    3000
    7b  34-10   NULL    3000
    7b  34-10   NULL    2000

第四步:以最高的目录价格

 ID Name    Orderdate   Catalog Price
    7b  34-10   NULL    3000
    7b  34-10   NULL    3000

Answer 1

请给你的数据库贴上标签。以下是Oracle可以尝试的一些内容：

SELECT id,name,orderdate,catalog_price
FROM TABLE a
WHERE id LIKE '7%' AND name LIKE '34%' AND orderdate IS NULL
  AND catalog_price = (SELECT max(catalog_price) FROM TABLE b 
  WHERE a.id=b.id AND a.name=b.name)

Answer 2

SELECT * FROM Table
WHERE 
Id LIKE '7%' 
AND Name LIKE '34%' 
AND OrdeDate IS NULL
AND Catalog Price = ( SELECT MAX(Catalog Price) FROM Table )

不太确定什么是代表

only the records with highest catalog price

如果表内的最高价格比我的答案正确，如果按id分组的最高价格是正确的，那么@django-unchained答案是正确的。

Answer 3

如果您想要每个组的最大catalog_price (id、name、orderdate)，则需要以下内容

select id, name, orderdate, max(catalog_price) as catalog_price
from my_table
where like'7%' and name like'34%' and orderdate is null
group by id, name, orderdate

如果你不关心这群人，你想要这个

select id, name, orderdate,  catalog_price
from my_table
where id like'7%' and name like'34%' and orderdate is null
and catalog_price = (select max(catalog_price) from my_table)

演示