SQL练习一
练习一:
需求:
- 2023年3月投保的人数(客户可能买多个保单)
- 2023年3月客户投保的总金额
表
userinsure 表
CREATE TABLE userinsure(
`userid` int NOT NULL COMMENT '用户id',
`username` varchar(30) NOT NULL COMMENT '用户名',
`insureid` int NOT NULL COMMENT '保单id',
`date` datetime NOT NULL COMMENT '创建时间'
);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (1001110003, 500000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (1001110004, 400000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2001110001, 100000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2011110001, 10000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2011110002, 30000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2011110003, 100000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2011120008, 50000);
INSERT INTO `insuresum` (`insureid`, `insuremoney`) VALUES (2011120009, 99999);
insuresum 表
CREATE TABLE insuresum(
`insureid` int NOT NULL COMMENT '保单id',
`insuremoney` double NOT NULL COMMENT '投保金额'
);
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (1, '哇哈哈', 2011110001, '2023-03-04 17:05:12');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (2, '喜羊羊', 2011110002, '2023-03-04 17:11:21');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (3, '艾达丝', 2011110003, '2023-03-05 17:12:45');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (4, '爱丽丝', 2011120009, '2023-04-02 17:13:37');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (5, '手打', 2011120008, '2023-04-01 17:14:03');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (6, '喜羊羊', 2001110001, '2023-04-02 17:14:44');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (7, '哇哈哈', 1001110003, '2023-03-13 17:15:17');
INSERT INTO `userinsure` (`userid`, `username`, `insureid`, `date`) VALUES (8, '哇哈哈', 1001110004, '2023-04-14 17:31:17');
3月份投保的人数(一个客户可能在3月份 参加多个保险)
查询出3月人数,去重
SELECT
COUNT(
DISTINCT ( username ))
FROM
userinsure
WHERE
PERIOD_DIFF(
date_format( now( ), '%Y%m' ),
date_format( DATE, '%Y%m' )) = 1;
2023年3月客户投保的总金额
查询出2023年3月的金额 求和,
2023年3月这个日期条件有两种实现方式:
1.现在时间(现在时间 23年4月)的上一个月
2.指定具体的年月
SELECT
SUM(isum.insuremoney) '3月份总金额(元)'
FROM
userinsure ui
INNER JOIN
insuresum isum on ui.insureid = isum.insureid
WHERE
PERIOD_DIFF(
date_format( now( ), '%Y%m' ),
date_format( DATE, '%Y%m' )) = 1;
-------------------------------------
SELECT
SUM(isum.insuremoney) '3月份总金额(元)'
FROM
userinsure ui
INNER JOIN
insuresum isum on ui.insureid = isum.insureid
WHERE
YEAR(DATE)='2023' AND MONTH(DATE)='3';
---------------------------------------
SELECT
SUM(isum.insuremoney) '3月份总金额(元)'
FROM
userinsure ui
INNER JOIN
insuresum isum on ui.insureid = isum.insureid
WHERE
DATE_FORMAT(date,'%Y-%m') = '2023-03';
题主学习记录,大家多多包涵。