mysql 如何将 group_concat() 结果放入列表中

Question

我有一个名为 abc 的表，其值为:

date        |   Code |  Class  |    subitem |   Item_No |
2017-04-02  |   FII  | INDIR   |    YAA     |   28      |
2017-04-09  |   FII  | INDIR   |    YAA     |   28      |
2017-04-16  |   FII  | INDIR   |    YAA     |   14      |
2017-04-23  |   FII  | INDIR   |    YAA     |   24      |
2017-04-30  |   FII  | INDIR   |    YAA     |   41      |
2017-04-02  |   RRT  | INDIR   |    BKK     |   8       |
2017-04-09  |   RRT  | INDIR   |    BKK     |   3       |
2017-04-16  |   RRT  | INDIR   |    BKK     |   16      |
2017-04-23  |   RRT  | INDIR   |    BKK     |   15      |
2017-04-30  |   RRT  | INDIR   |    BKK     |   32      |
2017-04-09  |   RRT  | INDIR   |    WESEL   |   14      |   
2017-04-16  |   RRT  | INDIR   |    WESEL   |   7       |   
2017-04-23  |   RRT  | INDIR   |    WESEL   |   2       |   
2017-04-02  |   RRT  | INDIR   |    YAA     |   67      |
2017-04-09  |   RRT  | INDIR   |    YAA     |   98      |
2017-04-16  |   RRT  | INDIR   |    YAA     |   135     |
2017-04-23  |   RRT  | INDIR   |    YAA     |   103     |
2017-04-30  |   RRT  | INDIR   |    YAA     |   119     |
2017-04-02  |   IDS  | INDIR   |    YAA     |   8       | 

我想要的输出是这样的:

CODE    |   ITEM_NO |   GROUP   |   DATE
FII     |   103     |   A1      |   2017-04-02
RRT     |   103     |   A1      |   2017-04-02
IDS     |   103     |   A1      |   2017-04-02
FII     |   126     |   A2      |   2017-04-09
RRT     |   126     |   A2      |   2017-04-09
FII     |   149     |   A3      |   2017-04-16
RRT     |   149     |   A3      |   2017-04-16
FII     |   127     |   A4      |   2017-04-23
RRT     |   127     |   A4      |   2017-04-23
FII     |   160     |   A5      |   2017-04-30
RRT     |   160     |   A5      |   2017-04-30

这是我的脚本:

select distinct
Group_concat(Code SEPARATOR ', ') Code,
sum(Item_No) totally,
CONCAT("A",CAST(@jim:=@jim +1 AS CHAR)) Group,
Date Date
from tableT
where Date between '2017-04-01' and '2017-04-30'
and SubItem = 'YAA'
and dept IN ('FII','RRT','IDS')
group by  Date

但我只能得到这样的结果:

CODE            | ITEM_NO   |   GROUP   |   DATE
FII, RRT, IDS   |   103     |   A1      |   2017-04-02
FII, RRT        |   126     |   A2      |   2017-04-09
FII, RRT        |   149     |   A3      |   2017-04-16
FII, RRT        |   127     |   A4      |   2017-04-23
FII, RRT        |   160     |   A5      |   2017-04-30

还有其他方法可以得到结果吗？谢谢您的帮助

Answer 1

为此，您可以使用组选择和代码选择之间的联接

  select 
  t1.deptPivot  Code,
  sum(t1.Item_No) totally,
  t2.Group,
  t1.DATE
  from tableT t1
  INNER JOIN (
        select 
        CONCAT("A",CAST(@jim:=@jim +1 AS CHAR)) Group,
        Date Date
        from tableT
        where Date between '2017-04-01' and '2017-04-30'
        and SubItem = 'YAA'
        and dept IN ('FII','RRT','IDS')
        group by  Date, 

  ) t2 on t1.Date = t2.Date
  where Date between '2017-04-01' and '2017-04-30'
  and SubItem = 'YAA'
  and dept IN ('FII','RRT','IDS')
  group by  code, group, Date