MySQL 分组条件

Question

有没有办法在设置特定值时仅按特定行分组？

即我有下表:

USERNO  |   FIRSTNAME   |   NAME    |   CUSTOMER    |   YEAR
1       |   Jon         |   Snow    |   0           |   2014
2       |   Williams    |   Spear   |   1           |   2015
1       |   John        |   Snow    |   1           |   2015
3       |   Sam         |   Wells   |   0           |   2014
3       |   Sam         |   Wells   |   0           |   2013

当CUSTOMER为1时，应按USERNO分组。所以我想要以下结果:

USERNO  |   FIRSTNAME   |   NAME    |   CUSTOMER    |   YEAR
1       |   Jon         |   Snow    |   1           |   2014
2       |   Williams    |   Spear   |   1           |   2015
3       |   Sam         |   Wells   |   0           |   2014
3       |   Sam         |   Wells   |   0           |   2013

这可能吗？

提前致谢!

Answer 1

您可以使用 UNION 来获得这样的结果:

SELECT *
FROM tablename
WHERE CUSTOMER != 1
UNION 
SELECT *
FROM tablename
WHERE CUSTOMER = 1
GROUP BY USERNO;

或者您也可以使用CASE，但您必须指定ELSE部分，以便在不满足条件时对记录进行分组:

SELECT *
FROM tablename
GROUP BY CASE WHEN CUSTOMER  = 1 THEN USERNO ELSE columnname END;

或者如果您希望将结果分组为 COMMA 分隔列表，则可以像这样使用 GROUP_CONCAT:

SELECT 
  GROUP_CONCAT(DISTINCT CAST(USERNO) AS CHAR),
  GROUP_CONCAT(DISTINCT CAST(FIRSTNAME) AS CHAR),
  GROUP_CONCAT(DISTINCT CAST(NAME) AS CHAR),
  CUSTOMER,
  GROUP_CONCAT(DISTINCT CAST(YEAR) AS CHAR)
FROM tablename
GROUP BY CUSTOMER;