WHERE句と条件カウントが1より大きいMySQLGROUPBY？

where句を使用したgroupbyを理解するために、テーブルを作成しましょう。テーブルを作成するためのクエリは次のとおりです-

mysql> create table GroupByWithWhereClause
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> IsDeleted tinyint(1),
   -> MoneyStatus varchar(20),
   -> UserId int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.57 sec)

これで、insertコマンドを使用してテーブルにいくつかのレコードを挿入できます。クエリは次のとおりです-

mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',101);
Query OK, 1 row affected (0.17 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101);
Query OK, 1 row affected (0.19 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',102);
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',102);
Query OK, 1 row affected (0.20 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',102);
Query OK, 1 row affected (0.59 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',103);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.20 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.10 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',104);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',104);
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',105);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105);
Query OK, 1 row affected (0.26 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105);
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',105);
Query OK, 1 row affected (0.24 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',106);
Query OK, 1 row affected (0.23 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106);
Query OK, 1 row affected (0.16 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106);
Query OK, 1 row affected (0.14 sec)

selectステートメントを使用してテーブルのすべてのレコードを表示します。

クエリは次のとおりです-

mysql> select *from GroupByWithWhereClause;

以下は出力です-

+----+-----------+-------------+--------+
| Id | IsDeleted | MoneyStatus | UserId |
+----+-----------+-------------+--------+
|  1 |         0 | Undone      |    101 |
|  2 |         0 | done        |    101 |
|  3 |         0 | done        |    101 |
|  4 |         0 | done        |    102 |
|  5 |         1 | Undone      |    102 |
|  6 |         1 | done        |    102 |
|  7 |         0 | Undone      |    103 |
|  8 |         0 | done        |    103 |
|  9 |         0 | done        |    103 |
| 10 |         0 | done        |    103 |
| 11 |         0 | done        |    104 |
| 12 |         0 | Undone      |    104 |
| 13 |         1 | Undone      |    105 |
| 14 |         1 | done        |    105 |
| 15 |         1 | done        |    105 |
| 16 |         0 | done        |    105 |
| 17 |         0 | Undone      |    106 |
| 18 |         0 | done        |    106 |
| 19 |         0 | done        |    106 |
+----+-----------+-------------+--------+
19 rows in set (0.00 sec)

これは、WHERE句を使用したGROUPBYへのクエリです-

mysql> SELECT * FROM GroupByWithWhereClause
   -> WHERE IsDeleted= 0 AND MoneyStatus= 'done'
   -> GROUP BY SUBSTR(UserId,1,3)
   -> HAVING COUNT(*) > 1
   -> ORDER BY Id DESC;

以下は出力です-

+----+-----------+-------------+--------+
| Id | IsDeleted | MoneyStatus | UserId |
+----+-----------+-------------+--------+
| 18 |         0 | done        |    106 |
|  8 |         0 | done        |    103 |
|  2 |         0 | done        |    101 |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)