mySQL 5.5 - 在与 UNION 链接的两个表之后将值转为列

debugcn 发表于 Dev

卡伦吉

我有一个最后的问题，我无法用我的 sql 代码进行排序。基本上我想透视表并以与此不同的格式显示：http : //sqlfiddle.com/#!9/98436/1

我尝试做的是旋转 LOC_ID 和 LOC_ID_b 以获得平面文件。问题是让代码工作我按 LOC_ID 和 LOC_ID_b 分组，但不知道如何解决它。

我尝试实现的结果如下：

PN    AAA  q    AAA c   BBB q   BBB c   CCC q   CCC c
A1                      2       1       
RRR             1                               1
T1     1        1               
HHH    3                                3

鲑鱼

您可以使用条件聚合。如果您的已知 loc_id 数量有限，您可以这样编码

Select pn,
    max(case when src = 'h'  and loc_id = 'AAA' then val else 0 end) as AAA_qty,
    max(case when src = 'r'  and loc_id = 'AAA' then val else 0 end) as AAA_count,
    max(case when src = 'h'  and loc_id = 'BBB' then val else 0 end) as BBB_qty,
    max(case when src = 'r'  and loc_id = 'BBB' then val else 0 end) as BBB_count,
    max(case when src = 'h'  and loc_id = 'CCC' then val else 0 end) as CCC_qty,
    max(case when src = 'r'  and loc_id = 'CCC' then val else 0 end) as CCC_count 
    from (select 'h' as src, pn, loc_id, sum(qty) val from history group by src,pn,loc_id 
    union
    select 'r' as src, pn, loc_id, count(*) val from rota group by src,pn,loc_id
) s 
group by pn
order by pn;

如果您不知道或不想在添加或删除 loc_ids 时更改您的代码，您需要动态 sql

set @sql = (
select concat( 
        'Select pn,',
        group_concat(
        concat('max(case when src = ' ,char(39),'h',char(39),'  and loc_id = ', char(39),loc_id,char(39), ' then val else 0 end) as ' , 
        concat(loc_id,'_qty,')
        ,
        'max(case when src = ' ,char(39),'r',char(39),'  and loc_id = ', char(39),loc_id,char(39), ' then val else 0 end) as ' , 
        concat(loc_id,'_count')
        )
        ) 

      ,' from ('
      ,
      'select ', char(39),'h',char(39),' as src, pn, loc_id, sum(qty) val from history group by src,pn,loc_id'
       ,
        ' union '
        , 
        'select ',char(39),'r',char(39),' as src, pn, loc_id, count(*) val from rota group by src,pn,loc_id
) s 
group by pn
order by pn;'
        )


from
(
select  loc_id from history
union
select loc_id from rota
) a
);

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

+-----+---------+-----------+---------+-----------+---------+-----------+
| pn  | AAA_qty | AAA_count | BBB_qty | BBB_count | CCC_qty | CCC_count |
+-----+---------+-----------+---------+-----------+---------+-----------+
| A1  |       0 |         0 |       2 |         1 |       0 |         0 |
| HHH |       0 |         0 |       0 |         0 |       3 |         0 |
| RRR |       0 |         0 |       0 |         0 |       0 |         1 |
| T1  |       1 |         1 |       0 |         0 |       0 |         0 |
+-----+---------+-----------+---------+-----------+---------+-----------+
4 rows in set (0.00 sec)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。