如何确定某个值是否出现在 MySQL 中的 GROUP BY 组中?
mysqlmysqli database
您可以使用聚合函数 SUM() 和 IF 来确定某个值是否出现在 GROUP BY 组中。
让我们首先创建一个演示表
mysql> create table GroupbygroupDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (1.48 sec)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
mysql> insert into GroupbygroupDemo values(10,'John'); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupbygroupDemo values(10,'Carol'); Query OK, 1 row affected (0.08 sec) mysql> insert into GroupbygroupDemo values(10,'Carol'); Query OK, 1 row affected (0.12 sec) mysql> insert into GroupbygroupDemo values(20,'David'); Query OK, 1 row affected (0.17 sec) mysql> insert into GroupbygroupDemo values(30,'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into GroupbygroupDemo values(30,'David'); Query OK, 1 row affected (0.20 sec) mysql> insert into GroupbygroupDemo values(30,'Mike'); Query OK, 1 row affected (0.16 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from GroupbygroupDemo;
输出如下
+--------+----------+ | UserId | UserName | +--------+----------+ | 10 | John | | 10 | Carol | | 10 | Carol | | 20 | David | | 30 | John | | 30 | David | | 30 | Mike | +--------+----------+ 7 rows in set (0.00 sec)
以下查询用于确定某个值是否出现在 GROUP BY 组中
mysql> select UserId, -> if(sum(UserName='David'),'YES','NO') as Correct_Name_David -> from GroupbygroupDemo -> group by UserId;
以下是输出 −
+--------+--------------------+ | UserId | Correct_Name_David | +--------+--------------------+ | 10 | NO | | 20 | YES | | 30 | YES | +--------+--------------------+ 3 rows in set (0.08 sec)
