mysql - 基于映射计算

Question

我有两个表 - Table1。有体积的元素。和表2。映射表。
表格1。有体积的元素。

Item   |  Small  | Medium  |  Large  |  XLarge|  Volume  
-------|---------|---------|---------|--------|-----------   
Shirt  |---10----|----0----|---20----|---0----|-----------  
-------|---------|---------|---------|--------|-----------  
Pants  |----0----|----30---|---10----|---20---|-----------  
-------|---------|---------|---------|--------|-----------  
Skirts |----0----|----30---|---10----|---20---|-----------  
-------|---------|---------|---------|--------|-----------

表2.映射表

Item  |Size  
------|-------  
Shirt |small  
------|-------  
Shirt |medium  
------|-------   
Shirt |large  
------|-------  
Pants |large  
------|-------  
Skirts|medium

我需要使用映射来计算表 1 中的体积。例如，对于衬衫，我查看映射表，发现我们有小号、中号和大号。因此，我需要总结表 1 中所有项目(不仅仅是衬衫)的小、中和大列，即 110。

输出表:

Item   |  Small  | Medium  |  Large  |  XLarge|  Volume  
-------|---------|---------|---------|--------|-----------   
Shirt  |---10----|----0----|---20----|---0----|----110----  
-------|---------|---------|---------|--------|-----------  
Pants  |----0----|----30---|---10----|---20---|-----40----  
-------|---------|---------|---------|--------|-----------  
Skirts |----0----|----30---|---10----|---20---|-----60----  
-------|---------|---------|---------|--------|-----------

select step1.Item
from 
(select t1.Item
,t1.Small
,t1.Medium
,t1.Large
from t1) step1
full outer join
t2
on t1.Item=t2.Item
group by t1.Item
,t1.Small
,t1.Medium
,t1.Large

无法弄清楚案例陈述。请帮忙!

Answer 1

因为您的要求是“从表 1 中汇总所有项目的(具体尺寸)”，所以立即获得每个尺寸的总数是有好处的。一个简单的求和查询(select sum(Small) as Small, sum(Medium)...)将作为子查询包含在最终解决方案中，以便可以轻松计算每个大小的总计“抬头看”。

为了计算每个项目的体积，我们需要旋转映射表，以便每个项目有一行，并且我们可以对每个映射尺寸的尺寸总计进行求和。使用交叉联接到上面的总计，并使用case语句将Mapping.Size值转换为相应的总计值。

最后一步从Table1中选择以获取具有特定大小卷的源项目；并作为子查询连接到上面以获得相应的总体积。

下面的查询使用 SQL Server 表变量来演示如何使用示例数据。您可以替换为适当的 mysql 等效项来确认。

declare @Table1 table (
   Item varchar(10),
   Small int, Medium int, Large int, XLarge int)
insert into @Table1 values
('Shirt', 10, 0, 20, 0),
('Pants', 0, 30, 10, 20),
('Skirts', 0, 30, 10, 20)

declare @Mapping table(
   Item varchar(10),
   Size varchar(10))
insert into @Mapping values
('Shirt', 'small'),
('Shirt', 'med'),
('Shirt', 'large'),
('Pants', 'large'),
('Skirts', 'med')

select  a.*, v.Volume
from    @Table1 a
        /* Join main table back to mapping based summary for final result */
        left join (
        /* The following is a "manual pivot" technique used to 
           transform rows into aggregated columns. */
        select  m.Item,
                /* Aggregate totals accoring to the mapping */
                sum(
                case m.Size
                    when 'large' then t.Large
                    when 'med' then t.Medium
                    when 'small' then t.Small
                    when 'xlarge' then t.XLarge
                    else 0
                end
                ) Volume
        from    @Mapping m
                cross join (
                     /* Get totals of each column */
                     select  sum(Small) Small,
                             sum(Medium) Medium, 
                             sum(Large) Large, 
                             sum(XLarge) XLarge
                     from    @table1
                ) t
        group by m.Item
        ) v on v.Item = a.Item