时间戳

首页 > 解决方案 > 可视化订单中所有价值最高的产品，从中提取最大的金额和订单

sql - 可视化订单中所有价值最高的产品，从中提取最大的金额和订单

问题描述

 Desired result
    NAME                  PRICE
Lenovo X1 Carbon           4009.00
Lenovo ThinkVision X1    1549.00
Lenovo YOGA 520          1349.00
Motorola Moto Z2           999.00
Motorola Moto Z          549.00
Motorola Moto E5          179.00

ID  CATEGORY    NAME                            PRICE   ORDER_ID
1   PC          Lenovo ThinkaPad L380            1579     5
2   Mobile      Motorola Moto E5                 179      1
3   Mobile      Motorola Moto Z                549    2
4   Monitor     Lenovo ThinkVision X1            1549   4
5   PC          Lenovo X1 Carbon                 4009     3
6   Mobile      Motorola Moto Z2                 999      4
7   PC          Lenovo Legion Y530             2099   4
8   PC          Lenovo YOGA 520                 1349    3
9   Monitor     Lenovo ThinkVision X1            1549   6
10 PC           Lenovo YOGA 520                1349     6
11  Monitor   Lenovo ThinkVision X1         1549      3
12  Mobile    Motorola Moto Z2               999      3
13  Mobile    Motorola Moto E5               179      3
14  Mobile    Motorola Moto Z                   549   3

在此处输入图像描述

我使用这 2 个查询从照片中的表格中提取信息，但我需要找到一种方法来仅通过一个查询来检索信息，请帮助

SELECT TOP 1 NAME , PRICE, ORDER_ID 
FROM PRODUCTS
ORDER BY PRICE DESC

SELECT NAME ,PRICE
FROM PRODUCTS 
WHERE  ORDER_ID like '%3%'  
ORDER BY PRICE DESC

期望的结果

标签： sql

解决方案

-- Get everything from the table 
SELECT 
  *
FROM
   Products

-- Where the total order value for the order is the highest
Where  
   order_id = 
(
    -- The largest order total
    SELECT TOP 1
        order_id
    FROM 
        Products 
    GROUP BY 
        order_id 
    ORDER BY 
        SUM(price) DESC
)
ORDER BY 
   Price DESC

推荐阅读