时间戳

首页 > 解决方案 > 根据每组的记录筛选分组依据

mysql - 根据每组的记录筛选分组依据

问题描述

我正在使用mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)).

我创建了以下数据库：

CREATE TABLE `transaction` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `filling_date` timestamp NULL DEFAULT NULL,
  `trx_type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount_range` int DEFAULT NULL,
  `insider_price` decimal(30,4) DEFAULT NULL,
  `qty` int DEFAULT NULL,
  `transaction_value` decimal(30,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
); 

INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(6, 'Apple', '2021-06-06 16:39:54.000', 'P - Purchase', 1000, 10, 100, 8000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(7, 'Apple', '2021-06-05 15:29:34.000', 'S - Sale', 2000, 11, 200, 9000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(8, 'Microsoft', '2021-06-05 11:22:15.000', 'P - Purchase', 2000, 10, 500, 1000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(9, 'Apple', '2021-05-16 11:29:44.000', 'P - Purchase', 1000, 11, 1000, 10000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(10, 'Microsoft', '2021-01-10 11:22:15.000', 'P - Purchase', 100, 30, 700, 3000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(11, 'Microsoft', '2021-06-01 22:22:15.000', 'S - Sale', 6000, 60, 600, 4000);

我想过滤我的 group-by 语句，以便按 > 1 ( count(Company) > 1) 获得每组的所有记录。

我尝试了以下方法：

select 
  count(Company) AS RecordsPerGroup,
  Company, 
  max(filling_date) Last_filling_date, 
  trx_type,
  sum(amount_range) amount_range,
  sum(insider_price) insider_price,
  sum(qty) qty,
  sum(transaction_value) transaction_value
from transaction
where filling_date >= DATE(NOW()) - INTERVAL 31 DAY 
AND trx_type ='p - purchase'
AND count(Company) > 1
group by company

当我运行上述查询时，我得到：

Query Error: Error: ER_INVALID_GROUP_FUNC_USE: Invalid use of group function

该错误源于以下语句AND count(Company) > 1，这在我的查询中是不允许的。

在下面找到我的 db-fiddle 示例：

DB-Fiddle 示例

我想得到以下输出：

| RecordsPerGroup | Last_filling_date   | amount_range | insider_price | qty  | transaction_value | Company   | trx_type     |
| --------------- | ------------------- | ------------ | ------------- | ---- | ----------------- | --------- | ------------ |
| 2               | 2021-06-06 16:39:54 | 2000         | 21.0000       | 1100 | 18000.0000        | Apple     | P - Purchase |

有什么建议如何过滤我的 group-by 语句以仅显示所有具有 >2 group bys 的记录？

感谢您的回复！

标签： mysqlsql

解决方案

对计数的断言属于HAVING子句，而不是WHERE子句，并且如果您打算 select trx_type，那么该列也应该出现在GROUP BY子句中：

SELECT
    COUNT(Company) RecordsPerGroup,
    Company, 
    MAX(filling_date) Last_filling_date,
    trx_type,
    SUM(amount_range) amount_range,
    SUM(insider_price) insider_price,
    SUM(qty) qty,
    SUM(transaction_value) transaction_value
FROM `transaction`
WHERE
    filling_date >= DATE(NOW()) - INTERVAL 31 DAY AND
    trx_type = 'p - purchase' 
GROUP BY
    Company,
    trx_type
HAVING
    COUNT(Company) > 1;

推荐阅读