mysql获取表中最后一条记录

十年如一日@

已于 2022-11-17 18:14:19 修改

阅读量5.6k

点赞数

分类专栏： mySql 文章标签： java

于 2019-09-05 17:19:21 首次发布

本文链接：https://blog.csdn.net/qq_37924509/article/details/100562338

版权

mySql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1： mysql获取表中最后一条记录

SELECT
	device_id,
	GROUP_CONCAT( merchant_id ) merchant_id,
	SUBSTRING_INDEX( GROUP_CONCAT( remark ORDER BY create_time DESC ), ",", 1 ) AS remark,
	SUBSTRING_INDEX( GROUP_CONCAT( create_time ORDER BY create_time DESC ), ",", 1 ) AS atime 
FROM
	bs_device_binding 
GROUP BY
	device_id

1.1 自定义group_concat 分割符

**select product_id,group_concat(attribute separator '#') from test_group_concat group by product_id;**

转载连接

2 ：分组数据后取每组的前N条的几种方法

转载直通车

DROP DATABASE IF EXISTS company;
CREATE DATABASE company;
USE company;
CREATE TABLE `employee`(
`empno` INT(32) NOT NULL AUTO_INCREMENT,
`department` VARCHAR(64),
`salary` DOUBLE(10,2),
PRIMARY KEY(`empno`)
);

INSERT INTO EMPLOYEE(`department`,`salary`) VALUES
('B',16000.00),('B',19000.00),('A',10000.00),('A',11000.00),('A',12000.00),('B',13000.00),('B',14000.00),('D',24000.00),('D',25000.00),('B',18000.00),
('C',7000.00),('A',13000.00),('A',14000.00),('A',16000.00),('C',5000.00),('A',16000.00),('A',17000.00),('A',18000.00),('A',19000.00),('B',17000.00),
('B',10000.00),('B',11000.00),('B',12000.00),('B',15000.00),('D',26000.00),('D',26000.00),('D',27000.00),('B',15500.00),('B',16000.00),
('D',20000.00),('D',21000.00),('D',25500.00),('C',2000.00),('C',3000.00),('C',4000.00),('D',28000.00),('D',29000.00),('D',30000.00),('C',6000.00),
('C',1000.00),('C',1100.00),('D',22000.00),('D',23000.00),('C',5500.00),('C',6000.00),('C',8000.00),('C',9000.00),('A',15000.00),('A',15500.00);

SELECT e.`department`,e.`empno`,e.`salary`
FROM employee e
LEFT JOIN employee et
ON e.`department` = et.`department` AND e.`salary`<et.`salary`
GROUP BY e.`empno`,e.`department`,e.`salary`
HAVING COUNT(1)<10
ORDER BY e.`department`,e.`salary` DESC;

SELECT e.`department`,e.`empno`,e.`salary`
FROM employee e
INNER JOIN employee et
ON e.`department` = et.`department` AND e.`salary`<=et.`salary`
GROUP BY e.`empno`,e.`department`,e.`salary`
HAVING COUNT(1)<=10
ORDER BY e.`department`,e.`salary` DESC;