mariadb 我需要一些查询细节

我有两个表"users"和"bookings"
我需要计算有多少用户已进入和注册的选定年和每个月
以及他们在匹配的年和月中花费了多少金额

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `user_nationality` varchar(255) DEFAULT NULL,
  `user_birthYear` int(11) DEFAULT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `user_passportNumber` varchar(255) DEFAULT NULL,
  `user_hotel` varchar(255) DEFAULT NULL,
  `gender` varchar(255) NOT NULL,
  `addedOn` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`user_id`, `user_name`, `user_nationality`, `user_birthYear`, `user_email`, `user_passportNumber`, `user_hotel`, `gender`, `addedOn`) VALUES
(104, 'john abraham', 'albania', 1994, 'john@john.com', '11100', 'google', 'male', '2023-01-29 09:06:41'),
(112, 'jah graz', 'morocco', 1990, 'jah@hah.com', '1843', 'df', 'male', '2023-02-06 17:29:58'),
(115, 'ronaldo abraham', 'angola', 1993, 'ronaldo@gmail.com', '87565', 'ng', 'male', '2023-02-06 17:30:42'),
(116, 'zhengjian yangben', 'china', 1983, 'gfjhfghfgh@ytfghj.com', 'e00000000', 'gt', 'female', '2023-02-06 17:30:56'),
(117, 'oksiao tiah', 'china', 1983, 'oksia@ytfghj.com', 'e000000001', 'google', 'female', '2023-02-06 17:31:26');

CREATE TABLE `bookings` (
  `booking_id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_birthYear` int(11) NOT NULL,
  `user_nationality` varchar(255) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_group` varchar(255) NOT NULL,
  `place_id` text DEFAULT NULL,
  `booked_by` int(11) DEFAULT NULL,
  `booked_date` date NOT NULL,
  `booked_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `booking_ref` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `isDomestic` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `bookings` (`booking_id`, `user_name`, `user_birthYear`, `user_nationality`, `user_id`, `user_group`, `place_id`, `booked_by`, `booked_date`, `booked_on`, `booking_ref`, `amount`, `status`, `isDomestic`) VALUES
(647, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false'),
(648, 'zhengjian yangben', 1983, 'china', 116, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:41:12', 'B-83167534874736719834', '300.00', 0, 'false'),
(649, 'zhengjian yangben', 1983, 'china', 104, 'adult', '37', 4, '2023-02-06', '2023-02-03 19:50:43', 'B-41167566360101919834', '100.00', 0, 'false'),
(650, 'john abraham', 1994, 'albania', 104, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:03', 'B-41167566360101919834', '0.00', 0, 'false'),
(651, 'john abraham', 1994, 'albania', 116, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:44', 'B-54167566365174419944', '0.00', 0, 'false'),
(652, 'zhengjian yangben', 1983, 'china', 116, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:44', 'B-54167566365174419944', '0.00', 0, 'false'),
(653, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false'),
(654, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-01-01 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false');

以下是我所尝试的
第一：

SELECT
`user_nationality` AS `Nationality`,
COUNT(`user_id`) AS `usersCount`,
MONTHNAME(`addedOn`) AS `monthName`,
MONTH(`addedOn`) AS `month`
FROM `users`
WHERE DATE_FORMAT(`addedOn`,'2023-%m') = DATE_FORMAT(`addedOn`,'2023-%m')
GROUP BY `monthName`,`Nationality`
ORDER BY `month`

从上面的代码我可以得到正确的结果，但我不能得到正确的金额，如果我加入预订表
我需要加入预订表以获取匹配的国家、年份和月份的总金额

我不打算回答具体的问题，而是笼统地帮助他人。请将此作为指南，并将其转化为您特定用例所需的内容。但是...在我进入细节之前。您提供的数据模型中缺少主键和外键。请正确掌握基本知识。
看起来您正在使用某种类型的酒店预订系统。想象一下这样的场景：您在真实的生活中出现在一家酒店，告诉接待员您有预订，他们问您的姓名，但您没有告诉他们......您不会经过接待处。您的数据模型也没有什么不同。掌握好基本知识，剩下的工作就水到渠成了。
不管怎样，你需要这样的东西;

SELECT
    main_table.id
    , SUM(second_table.field_you_want_to_sum)
    , MONTH(second_table.created_date)
    , YEAR(second_table.created_date)
    , CONCAT(YEAR(second_table.created_date), "-", MONTH(second_table.created_Date))
FROM
    main_table
    LEFT JOIN second_table ON main_table.id = second_table.main_table_id_fk
GROUP BY 
    main_table.id, CONCAT(YEAR(second_table.created_date), "-", MONTH(second_table.created_date))
;

SUM / COUNT等。在这个上下文中有相同的概念。您可能还需要一个ORDER BY，这就是为什么CONCAT方错误地使用yyyy-mm-dd格式，因为这是您需要的结构化格式。

mariadb 我需要一些查询细节

1条答案

相关问题

热门标签

最新问答