mysql - MYSQL - 通过查询从组中的最后一个 id 获取值

Question

SQL Fiddle中的完整示例 我有一个来自 3 个表的查询

SELECT MAX(s.id_stock), s.precio_in, s.codigo, 
   SUM(s.cantidad) - COALESCE(sv.suma_salidas,0) as cantidad, 
   x.status
    FROM db1_stock s
    LEFT JOIN 
      (SELECT COALESCE(sum(cant_ser),0) suma_salidas, codigo
       FROM db1_servicios
       GROUP BY codigo) sv USING (codigo)       
    LEFT JOIN db1_oc x
        ON (s.oc_id = x.oc_id)
    WHERE x.status = 'EN BODEGA'
    GROUP BY s.codigo
    ORDER BY x.fecha_oc DESC

表（db1_services 为空）

结果

我希望得到相同的结果，但从最后一个开始id_stock，不会丢失 SUM 和 group by codigo；不是 的最大值precio_in，只是最近的。谢谢 ！！ 小提琴

标签： mysqlgroup-bysumleft-join

Answer 1

要获得最后一个值precio_in（我认为是与 关联的那个MAX(id_stock)），您需要db1_stock在派生表中进行聚合，然后JOIN返回db1_stock：

SELECT s.id_stock, 
       s.precio_in, 
       s.codigo, 
       a.cantidad - COALESCE(sv.suma_salidas,0) as cantidad, 
       x.status
FROM db1_stock s
JOIN (
  SELECT codigo,
         MAX(id_stock) AS id_stock,
         SUM(cantidad) AS cantidad
  FROM db1_stock
  GROUP BY codigo
) a ON a.codigo = s.codigo AND a.id_stock = s.id_stock
LEFT JOIN (
  SELECT COALESCE(SUM(cant_ser),0) AS suma_salidas, codigo
  FROM db1_servicios
  GROUP BY codigo
) sv ON sv.codigo = s.codigo
JOIN db1_oc x ON s.oc_id = x.oc_id
WHERE x.status = 'EN BODEGA'
ORDER BY x.fecha_oc DESC

输出：

id_stock    precio_in   codigo      cantidad    status
19          4000        PRUEBA      6           EN BODEGA
20          4200        PRUEBA2     1           EN BODEGA

SQLFiddle 上的演示