包含主键的列的SQL MAX [英] SQL MAX of column including its primary key

问题描述

从sql下面，选择我得到cart_id和该购物车中最大价值物品的价值.

From below sql select I get the cart_id and the value of the maximum valued item in that cart.

SELECT CartItems.cart_id, MAX(ItemValues.value)
FROM CartItems 
INNER JOIN ItemValues 
ON CartItems.item_id=ItemValues.item_id
GROUP BY CartItems.cart_id

但是我还需要该项目的item_id(ItemValues.item-id).

but I also need item_id for that item (ItemValues.item-id).

两个表CartItems，ItemValues(以及它们各自的Carts，Item，与此处无关).
每个购物车可以有多个商品，而每个商品都有一个在ItemValues中定义的值.
每个项目都属于一个购物车.
购物车的价值是其购物车中具有最大值的商品的价值.
如何选择cart-id，max(item-value)及其对应的item-id?

例如，购物车ID A包含值为10的商品ID X和值为90的商品IDY.
用上面的sql选择我得到的，

Two tables, CartItems, ItemValues (and their respective Carts, Items, irrelevant here).
Each cart can have several items wheras each item has one value defined in ItemValues.
Each item belongs to one cart.
The value of a cart is the value of the item with maximum value within its cart.
How do I select cart-id, max(item-value) and it's corresponding item-id?

For instance cart-id A contains item-id X with value 10 and item-id Y with value 90.
With above sql select I get,

A, 90

我需要的是

A, Y, 90

平台:MS SQL

platform: MS SQL

推荐答案

在MS SQL和Oracle中:

SELECT *
FROM
  (
  SELECT ci.*, iv.*, 
        ROW_NUMBER() OVER (PARTITION BY CartItems.cart_id ORDER BY ItemValues.value DESC)
  FROM   CartItems ci
  INNER JOIN ItemValues iv
     ON CartItems.item_id=ItemValues.item_id
  ) s
WHERE rn = 1

在MySQL中:

SELECT
FROM
  (
  SELECT ci.*,
         (
         SELECT id
         FROM ItemValues iv
         WHERE iv.item_id = ci.item_id
         ORDER BY
               value DESC
         LIMIT 1
         ) AS maxitem
  FROM   CartItems ci
  ) iv, ItemValues ivo
WHERE ivo.id = iv.maxitem

这篇关于包含主键的列的SQL MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！