mysql group去空值,如何在MYSQL中存在空值的GROUP_CONCAT？

I have a long query that returns multiple values for a primary key (from a LEFT join).

For example : (only showing two fields, but there about 10 fields)

LotID Size

1 A

1 B

1 C

2 null

3 B

4 A

4 B

When I use GROUP_CONACT, it returns as follows :

LotID Size

1 A,B,C

3 B

4 A,B

But what I actually want is :

LotID Size

1 A,B,C

2 null

3 B

4 A,B

I tried using

GROUP_CONCAT(CONCAT_WS(',', IFNULL(Size,''))) AS Sizes,

It returns :

LotID Sizes

1 A,B,C,,,

3 B,,

4 A,B,,

It does not return LotID=2, also aditional commas.

How could I do it to get clean records ?

解决方案

You must be doing something wrong with group_concat, because this:

select

lotid,

group_concat(size) size

from tablename

group by lotid

returns:

| lotid | size |

| ----- | ------------------ |

| 1 | A,B,C |

| 2 | null |

| 3 | B |

| 4 | A,B |

See the demo.