自我加入问题，我认为呢? [英] self join issue, i think?

问题描述

我只有一张表(只能有一张)，如下图所示.

I have one table(could only have one) that looks like seen below.

它包含工厂代码，商品编号和与该商品相关的批次.

It contains a plant code, an article number and batches related to that article.

plant - code - batch - value - volume - added_date
A1      1000   A1      10      20       date
A1      2000   A7      20      15       date
A1      1000   A1      5       10       later than first A1 batch
A2      200    A8      10      9        date
A1      2000   A10     20      20       date

我该如何显示此信息:对于每个工厂和代码，仅显示最新批次的DISTINCT批次数量和数量(请注意，plant A1中的代码1000仅显示10是最新添加的批次的数量.

How could I present this information like this: for each plant and code, show the DISTINCT number of batches and the volume for only the latest added batch(note that code 1000 in plant A1 is showing only 10 as that is the volume of the latest added batch).

plant - code - batches - volume
  A1      1000   1         10     
  A1      2000   2         35
  A2      200    1         9

早些时候，我从一个家伙那里得到了一些帮助，并且该查询非常有效，直到在同一工厂中同一产品的两个批次具有相同编号的情况下，我尝试仅从最新批次中获取产量.

I got some help from a guy here earlier, and that query works great until I tried to get the volume from only the latest batches if there is two batches with same number for the same product on the same plant.

SELECT code, plant,
    COUNT(DISTINCT(batch)) as Batches,
    SUM(value) as TotalValue,
    SUM(volume) as TotalVolume,
    SUM(value * risk) as TotalRisk,
FROM lists 
GROUP BY code, plant

我想您可以通过一些concat和self join来做到这一点，但是我不够熟练，只能使用一个表:/

I guess you could do it with some concat and self joins but I am not skilled enough to work with only one table :/

提前谢谢！

好吧，实际上，阿德里安(Adrian)链接到的答案解决了所描述的问题...但是似乎很难做到让其他所有东西都可以使用该解决方案.我只描述了我的问题"的一部分，我认为足以解决其余的问题.但是我错了.我不仅需要最新添加的批次和数量，还需要对批次执行一些SUM操作.我认为最好将数据放入两个不同的表中并从那里开始工作.

Well, actually the answer that Adrian linked to solved the described issue...however it seems to be to hard to do make everything else work with that solution. I did only describe a part of my "problem", what I thought was enough to then solve the rest myself. But I was wrong. Not only do I need the latest added batch and volume, I also need to do some SUM operations on the batches. I am thinking that it may be best for me to place the data into two different tables and work from there.

推荐答案

SELECT  t.*,
        (SELECT  volume 
           FROM  lists
          WHERE  plant = t.plant
            AND  code  = t.code
          ORDER BY added_date DESC
          LIMIT 1   --Take the latest record for the plant & code
          ) AS volume                  
  FROM  (
            SELECT code, plant,
                COUNT(DISTINCT(batch)) as Batches,
                SUM(value) as TotalValue,
                SUM(value * risk) as TotalRisk
            FROM lists 
            GROUP BY code, plant
        ) AS t

这篇关于自我加入问题，我认为呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！