mysql使用心得

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `staff`
-- ----------------------------
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` smallint(10) DEFAULT NULL,
`department` int(10) DEFAULT NULL,
`type` smallint(5) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of staff
-- ----------------------------
INSERT INTO `staff` VALUES ('1', '刘德华', '54', '1', '1', '2014-06-21 11:29:22');
INSERT INTO `staff` VALUES ('2', '张学友', '50', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('3', '郭富城', '52', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('4', '黎明', '53', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('5', '刘德华', '54', '2', '2', '2014-06-21 11:39:27');
INSERT INTO `staff` VALUES ('6', '梁朝伟', '55', '2', '2', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('7', '黄日华', '57', '2', '2', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('8', '梁朝伟', '55', '3', '3', '2014-06-21 11:30:36');
INSERT INTO `staff` VALUES ('9', '刘德华', '54', '3', '3', '2014-06-21 11:31:01');

SELECT * FROM `staff` as t1 group by `name`,age,department,type,create_time
having create_time = (select max(create_time) from staff as t2 where t2.name=t1.name ) #group by t2.name
;

解析：group by是先按`name`,age,department,type,create_time 进行分组，分完组后having对每个分组里面按照create_time进行过滤，最后得到每个name的最新一条记         录；也就行有多少个分组分组就要执行多少遍 having create_time = (select max(create_time) from staff as t2 where t2.name=t1.name ) 语句，select                     max(create_time) from staff as t2 where t2.name=t1.name 查询的数据源是从全表中通过name进行过滤后，再查找最大的一条。

思考：可以把having后面的所有create_time都换成id看看结果对比一下

另一种通过子查询的实现方式：

SELECT * FROM
(
select * from staff order by create_time desc
) as t1 group by `name`;

内连接方式实现：

select a.* from staff as a
INNER JOIN staff as b ON a.`name`=b.`name`
group by
a.`name`,
a.age,
a.id,
a.department,
a.create_time
having a.create_time = max(b.create_time)

 

还有一种实现方法：

SELECT a.*,max(a.gid) FROM linksus_gov_structure as a ,(select max(gid ) as gid from `linksus_gov_structure` group by account_id ) as b
where a.gid=b.gid
group by account_id

 

待信审初审、待数据完成过期：

select * from loan_robot_audit a where a.type=80 group by a.application_id,a.create_time
HAVING a.create_time = (select max(b.create_time) from loan_robot_audit b where b.application_id=a.application_id and b.type=80 )

另外两种方式，其中有一种和上面某种重复：

EXPLAIN
select * from na_customer_thirdcode_record where id in (select max(id) from na_customer_thirdcode_record group by customer_id );

EXPLAIN
select * from na_customer_thirdcode_record as a group by customer_id,created_at HAVING id = (select max(id) from na_customer_thirdcode_record as b where a.customer_id=b.customer_id group by customer_id)

问题： chatgpt回答：用户日志表user_log中有多条用户数据，根据用户user_id分组后，按照用户最后登录时间log_time倒叙排序，并且汇总出用户每次访问时长time 这条sql怎么写 mysql

sql

SELECT 
  user_id, 
  MAX(log_time) as last_log_time,
  SUM(time) as total_time
FROM 
  user_log
GROUP BY 
  user_id
ORDER BY 
  last_log_time DESC;

这条SQL语句的每一部分含义如下：

• SELECT user_id, MAX(log_time) as last_log_time, SUM(time) as total_time：选择 user_id 字段，选择最后登录时间（为 user_log 表的 log_time 字段中最大的值），并选择访问时长的总和（为 time 字段的总和）。
• FROM user_log：指定从 user_log 表中选择数据。
• GROUP BY user_id：根据 user_id 字段对数据进行分组。
• ORDER BY last_log_time DESC：按照最后登录时间倒序排序。

请注意，这只是基于你描述的需求的基本示例。你可能需要根据实际情况调整或添加更多内容。例如，如果每次访问的开始和结束时间都保存在日志中，你可能需要计算每次访问的持续时间，然后再进行求和。

		select *,max(created_at) max_created_at,count(*),count(*) cnt, 
                sum(if(ctr.series='响扣内固定基台（4.0）',1,0)) as 'xiangkoungdjt',sum(if(ctr.series='PMC',1,0)) as 'PMC',
                sum(if(ctr.series='PCC',1,0)) as 'PCC',sum(if(ctr.series='DFI',1,0)) as 'DFI',
                sum(if(ctr.series='CC',1,0)) as 'CC',sum(if(ctr.series='Active',1,0)) as 'Active',
                sum(if(ctr.series='复合基台（直式）',1,0)) as 'fuhetjzs' 
		from na_customer_thirdcode_record as ctr group by customer_id order by max_created_at desc