MYSQL - 如何使用 group by 和having进行选择
我有这个模式:
table = users
user_id - integer
user_name - string
table = transaction
transaction_id - string
user_id - integer
这个示例数据:
user_id - user_name
2 - Jhon
3. - barry
transaction_id - user_id
19123 - 2
20123 - 2
20124 - 2
21123 - 2
我需要获取用户 jhon 仅在 19 和 20 中做了多少事务 transaction_id 的前 2 位数字是年份,但我似乎无法将它们分组,我所拥有的是:
select u.user_name
from transaction t join users u on u.user_id = t.user_id
group by (substr(t.transaction_id, 1, 2))
where <I have no idea in how to fill this>
我想要的结果是:
jhon 1 2
19 中的 1 笔交易 20 笔交易 2 笔
I have this schema:
table = users
user_id - integer
user_name - string
table = transaction
transaction_id - string
user_id - integer
this sample data:
user_id - user_name
2 - Jhon
3. - barry
transaction_id - user_id
19123 - 2
20123 - 2
20124 - 2
21123 - 2
I need to get how many transactions the user jhon did in 19 and 20 only
the first 2 digits from the transaction_id is the year, but I can't seem to group them, what I have is:
select u.user_name
from transaction t join users u on u.user_id = t.user_id
group by (substr(t.transaction_id, 1, 2))
where <I have no idea in how to fill this>
what I want as a result is:
jhon 1 2
1 transction in 19
2 transactions in 20
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

最好以 mysql 方式保存日期 2022-12-31,因此您可以使用带有转换的日期函数。
您需要按名称、名称和前 2 位数字进行 GROUP
并且 WHERE 子句始终位于 group by 之前
db<>fiddle 此处
It would be better to save dates in mysql way 2022-12-31, so you cqan use date function withput converting.
You need to GROUP By Nam,ame and the first 2 digits
And the WHERE clause belongs always before the group by
db<>fiddle here