mysql - SQL group by 从分组中排除 NULL 值

Question

我在表格中得到了这种情况

client   staff   code   time1   time2
------------------------
c1       null    code1
c2       null    code1
null     s1      code1
null     s2      code1

尝试按人员和代码对行进行分组:

SELECT GROUP_CONCAT(client),staff,code from table GROUP BY staff,code

我明白了:

client   staff   code   ...  time1   time2
------------------------
c1,c2    null    code1
null     s1      code1   <-
null     s2      code1   <-

为了所请求的功能，我需要“关注”员工的条目，这样我也可以获得相对时间 1 和时间 2。问题是上面箭头指示的行没有任何客户 ID，因此没有机会检索他们的数据。客户信息进入 staff = null 行。

我怎样才能实现这样的目标？

client   staff   code   ...  time1   time2
------------------------
c1,c2    s1      code1   <-
c1,c2    s2      code1   <-

谢谢

Answer 1

(迟到的回答:)，但它可能对其他人有帮助!)

首先，您必须意识到单个 FROM 不能生成包含 both c1 和 s1(例如)的行，而另一行包含both c1 和 s2。当然，除了包含 GROUP_CONCAT(client), GROUP_CONCAT(staff) 的一行，但它没有用，因为它失去了客户和员工之间的联系。

你所要做的就是将你的表与自身连接起来:

client   staff   code   
------------------------
c1       null    code1
c2       null    code1
null     s1      code1
null     s2      code1
c3       null    code1*
c4       null    code2*


SELECT c.client, s.staff, c.code
FROM `table` c
INNER JOIN `table` s ON s.code = c.code

client   staff   code
------------------------
c1       null    code1
c1       null    code1
c1       s1      code1
c1       s2      code1

c2       null    code1
c2       null    code1
c2       s1      code1
c2       s2      code1

null     null    code1
null     null    code1
null     s1      code1
null     s2      code1

null     null    code1
null     null    code1
null     s1      code1
null     s2      code1

显然，两个 4 行表之间的交叉连接给出了 16 个结果。排除具有 null 的行:

SELECT c.client, s.staff, c.code
FROM `table` c
INNER JOIN `table` s ON s.code = c.code AND s.staff IS NOT NULL
WHERE c.client IS NOT NULL

client   staff   code
------------------------
c1       s1      code1
c1       s2      code1

c2       s1      code1
c2       s2      code1

然后，您可以按人员和代码分组，并汇总客户:

SELECT GROUP_CONCAT(c.client), s.staff, c.code
FROM `table` c
INNER JOIN `table` s ON s.code = c.code AND s.staff IS NOT NULL
WHERE c.client IS NOT NULL
GROUP BY staff, c.code

client   staff   code
------------------------
c1,c2     s1      code1
c1,c2     s2      code1

注意:正如所写的那样，它不会显示只有客户而没有员工的代码，以及那些有员工而没有代码的代码。如果其中一种情况是可能的，那么您必须 LEFT JOIN 相关表(table c LEFT JOIN staff s，或 table s LEFT JOIN table c，对 NULL 进行适当的测试)而不是 INNER JOIN。

client   staff   code   time1   time2
------------------------
c1       null    code1
c2       null    code1
null     s1      code1
null     s2      code1
*c3      null    code2*
*null    s3      code3*

SELECT GROUP_CONCAT(c.client), s.staff, c.code
FROM `table` c
LEFT JOIN `table` s ON s.code = c.code AND s.staff IS NOT NULL
WHERE c.client IS NOT NULL
GROUP BY staff, c.code

client   staff   code
------------------------
c1,c2     s1      code1
c1,c2     s2      code1
c3        NULL    code2

SELECT GROUP_CONCAT(c.client), s.staff, c.code
FROM `table` s
LEFT JOIN `table` c ON c.code = s.code AND c.client IS NOT NULL
WHERE s.staff IS NOT NULL
GROUP BY staff, c.code

client   staff   code
------------------------
c1,c2     s1      code1
c1,c2     s2      code1
NULL      s3      code3

不清楚为什么您的行中填充了客户或员工(但不是两者)；也许您应该重新考虑您的模型。