gpt4 book ai didi

mysql - 如何仅计算用户第一次出现的组

转载 作者:行者123 更新时间:2023-11-29 18:09:07 25 4
gpt4 key购买 nike

我有以下表结构:

CREATE TABLE `client_notes` (
`id` bigint(20) NOT NULL,
`client_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`note` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`datepost` datetime NOT NULL
) ENGINE=MyISAM

这里我存储用户为客户插入的注释。每个 client_id 都有一些注释,由 user_id 添加。目标是计算经纪商添加第一个注释的次数(额外选项可以是本周的一段时间,例如在 where 子句中)

+----+----------+-----------------------------+
| id | broker_id| client | datepost |
+----+---------+------------------------------+
| 1 | 1 | 10 | 29-11-2017 10:00:00| <- this is first note for this client
| 2 | 2 | 10 | 29-11-2017 10:20:00|
| 3 | 2 | 15 | 28-11-2017 10:10:00| <- this is first note for this client
| 4 | 1 | 15 | 28-11-2017 10:20:00|
| 5 | 1 | 15 | 28-11-2017 10:30:00|
+----+----------+--------+--------------------+

输出应该是:

+-----------+-------+
| broker_id | count |
+-----------+-------+
| 1 | 1 |
| 2 | 1 |
+-----------+-------+

最佳答案

首先,不要在简单的 SQL 标识符上使用不必要的和非标准的引号:

CREATE TABLE client_notes (
id bigint(20) NOT NULL,
client_id int(11) NOT NULL,
user_id int(11) NOT NULL,
note text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
datepost datetime NOT NULL
) ENGINE=MyISAM

要获得“第一”任何东西都意味着一个顺序和最小值。您希望每个客户的最小日期:

-- Note: example data column names don't match above table definition
select min(datepost) as datepost, client_id from ENGINE group by client_id

任何 SELECT 的结果本身都是一个表,并且可以进行操作,

select broker_id, count(*) as 'count'
from ENGINE as E
join (
select min(datepost) as datepost, client_id from ENGINE group by client_id
) as A
on E.client_id = A.client_id
and E.datepost = A.datepost
group by broker_id

关于mysql - 如何仅计算用户第一次出现的组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47542768/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com