mysql获取表中最后一条记录

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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值