MYSQL - 如何使用 group by 和having进行选择

发布于 01-14 22:27 字数 713 浏览 4 评论 0原文

我有这个模式:

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

睫毛溺水了2025-01-21 22:27:25

最好以 mysql 方式保存日期 2022-12-31,因此您可以使用带有转换的日期函数。

您需要按名称、名称和前 2 位数字进行 GROUP

并且 WHERE 子句始终位于 group by 之前

创建表用户(
  `user_id` 整数,
  `用户名` VARCHAR(5)
);

插入用户
  (`用户 ID`、`用户名`)
价值观
  ('2', '约翰'),
  ('3.', '巴里');
创建表事务(
  `transaction_id` 整数,
  `user_id` 整数
);

插入事务
  (`交易ID`,`用户ID`)
价值观
  ('19123', '2'),
  ('20123','2'),
  ('20124','2'),
  ('21123', '2');
select u.user_name, Count(*)
从事务 t 加入用户 u u.user_id = t.user_id
其中 u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN (19,20)
按 u.user_name 分组,(substr(t.transaction_id, 1, 2))
用户名 |数数(*)
:-------- | --------:
约翰 | 1
约翰 | 2

选择 u.user_name
, SUM((substr(t.transaction_id, 1, 2)) = 19) 作为 `Count_19`
, SUM((substr(t.transaction_id, 1, 2)) = 20) 作为 `Count_20`
从事务 t 加入用户 u u.user_id = t.user_id
其中 u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN (19,20)
按 u.user_name 分组
用户名 |计数_19 |计数_20
:-------- | --------: | --------:
约翰 | 1 | 2

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

CREATE TABLE users (
  `user_id` INTEGER,
  `user_name` VARCHAR(5)
);

INSERT INTO users
  (`user_id`, `user_name`)
VALUES
  ('2', 'Jhon'),
  ('3.', 'barry');
CREATE TABLE transaction (
  `transaction_id` INTEGER,
  `user_id` INTEGER
);

INSERT INTO transaction
  (`transaction_id`, `user_id`)
VALUES
  ('19123', '2'),
  ('20123', '2'),
  ('20124', '2'),
  ('21123', '2');
select u.user_name, Count(*)
from transaction t join users u on u.user_id = t.user_id
where u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN  (19,20)
group by u.user_name,(substr(t.transaction_id, 1, 2))
user_name | Count(*)
:-------- | -------:
Jhon      |        1
Jhon      |        2
select u.user_name
, SUM((substr(t.transaction_id, 1, 2)) = 19) As `Count_19`
, SUM((substr(t.transaction_id, 1, 2)) = 20) As `Count_20`
from transaction t join users u on u.user_id = t.user_id
where u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN  (19,20)
group by u.user_name
user_name | Count_19 | Count_20
:-------- | -------: | -------:
Jhon      |        1 |        2

db<>fiddle here

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文