sql小记

最新推荐文章于 2024-11-12 17:21:53 发布

dreams512

最新推荐文章于 2024-11-12 17:21:53 发布

阅读量318

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/chuan_yu_chuan/article/details/49534417

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1）看到一篇博客，wwwcomy 的博客 http://wwwcomy.iteye.com/blog/2013530，提到查询股票中的前三个。

数据表结构

DROP TABLE IF EXISTS `stock`;

CREATE TABLE `stock` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(255) default NULL,
`value` int(11) default NULL,
`Date` date default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `stock` VALUES ('1', '股票1', '10', '2013-12-25');
INSERT INTO `stock` VALUES ('2', '股票1', '9', '2013-12-26');
INSERT INTO `stock` VALUES ('3', '股票1', '8', '2013-12-25');
INSERT INTO `stock` VALUES ('4', '股票1', '7', '2013-12-24');
INSERT INTO `stock` VALUES ('5', '股票1', '5', '2013-12-23');
INSERT INTO `stock` VALUES ('6', '股票1', '4', '2013-12-22');
INSERT INTO `stock` VALUES ('7', '股票2', '19', '2013-12-26');
INSERT INTO `stock` VALUES ('8', '股票2', '18', '2013-12-25');
INSERT INTO `stock` VALUES ('9', '股票2', '17', '2013-12-24');
INSERT INTO `stock` VALUES ('10', '股票2', '15', '2013-12-23');
INSERT INTO `stock` VALUES ('11', '股票2', '14', '2013-12-22');
INSERT INTO `stock` VALUES ('12', '股票3', '109', '2013-12-26');
INSERT INTO `stock` VALUES ('13', '股票3', '108', '2013-12-25');
INSERT INTO `stock` VALUES ('14', '股票3', '107', '2013-12-24');
INSERT INTO `stock` VALUES ('15', '股票3', '105', '2013-12-23');
INSERT INTO `stock` VALUES ('16', '股票3', '104', '2013-12-22');

使用自连接查询查询每只股票中前三个
SELECT * ,
(SELECT COUNT(*) FROM stock s WHERE s.name=s1.name AND s.value > s1.value) AS test
FROM stock s1 HAVING test<=2
等价于
SELECT *
FROM stock s1

HAVING (SELECT COUNT(*) FROM stock s WHERE s.name=s1.name AND s.value > s1.value)<=2

这两个sql（其实是一种）在mysql调试通过，本人还写出来一个，适合在sybase/MSSQL上运行，还没有测试。欢迎指正。

使用row_number()和开窗函数

SELECT *, row_number() over(PARTITION BY NAME ORDER BY VALUE DESC ) AS rn
HAVING rn<=3

2）mysql中替代top,partition by的方案

使用substring_index()函数和group_concat() 函数

例如：

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
`user_id` int(11) DEFAULT NULL,
`money` int(11) DEFAULT NULL,
`create_time` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `orders` */

insert into `orders`(`user_id`,`money`,`create_time`) values

(1,100,'123'),(1,50,'456'),(2,200,'456'),(2,100,'789');

/*其中 123,456等表示时间，类似于相对于2000-01-01 00:00:00的时间*/

group_concat函数根据user_id聚合money，形成类似 100,50这样的字符串，然后使用字符串函数切片操作。

substring_index函数需要三个参数

SELECT user_id,SUBSTRING_INDEX(GROUP_CONCAT(money),',',1)
FROM
(
SELECT o.user_id AS user_id,o.money AS money,o.create_time FROM orders o
ORDER BY user_id,create_time DESC
)t
GROUP BY user_id