常用sql记录

最新推荐文章于 2024-08-15 16:05:29 发布

水中加点糖

最新推荐文章于 2024-08-15 16:05:29 发布

阅读量364

点赞数

分类专栏： DB 文章标签：常用sql

本文链接：https://blog.csdn.net/puhaiyang/article/details/88663372

版权

DB 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.mysql内实现rownum

SELECT 
  @rownum := @rownum +1 AS rownum,
  e.*
FROM
  (SELECT 
    @rownum := 0) r,
  employee e

2.获取出重复的记录,保留最新一条

SELECT
	a.crt_time,
	a.stu_no,
	a.age
FROM
	stu a
RIGHT JOIN (
	SELECT
		max(b.crt_time) AS max_crt_time,
		b.stu_no
	FROM
		stu b
	GROUP BY
		b.stu_no
) AS c ON a.crt_time = c.max_crt_time
AND a.stu_no = c.stu_no

其测试数据表为:

DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `crt_time` datetime DEFAULT NULL,
  `stu_no` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `stu`
-- ----------------------------
BEGIN;
INSERT INTO `stu` VALUES ('1', 'zhangsan', '11', '2019-03-01 21:31:25', '20180303'), ('2', 'lishi', '12', '2019-03-25 21:31:31', '20180202'), ('3', 'wangwu', '15', '2019-03-19 21:35:31', '20180101'), ('4', 'lisi', '13', '2019-03-28 21:33:50', '20180202'), ('5', 'zhangsan', '16', '2019-03-03 21:36:04', '20180303'), ('6', 'zhangsan', '17', '2019-03-27 21:37:15', '20180303'), ('7', 'liuer', '13', '2019-03-26 21:38:02', '20180404'), ('8', 'tianqi', '13', '2019-03-27 21:38:26', '20180505');
COMMIT;

即：

id	name	age	crt_time	stu_no
1	zhangsan	11	2019-03-01 21:31:25	20180303
2	lishi	12	2019-03-25 21:31:31	20180202
3	wangwu	15	2019-03-19 21:35:31	20180101
4	lisi	13	2019-03-28 21:33:50	20180202
5	zhangsan	16	2019-03-03 21:36:04	20180303
6	zhangsan	17	2019-03-27 21:37:15	20180303
7	liuer	13	2019-03-26 21:38:02	20180404
8	tianqi	13	2019-03-27 21:38:26	20180505

2019-03-19 21:35:31	20180101	15
2019-03-28 21:33:50	20180202	13
2019-03-27 21:37:15	20180303	17
2019-03-26 21:38:02	20180404	13
2019-03-27 21:38:26	20180505	13

3.求两列数据的交集

select tt ,bb.wec_account from (
select	123456789	 as tt union
select	987654321	 as tt union
select	147258369	 as tt ) aa left join table bb on (aa.tt=bb.id and (bb.created_time between '2019-03-11 00:00:00' and '2019-03-11 23:59:59') and bb.status=6)

4.获取最新记录

SELECT 
    *
FROM
    wallet a
WHERE
    a.id = (SELECT 
            MAX(b.id)
        FROM
            wallet b
        WHERE
            a.account = b.account)