常用sql记录

柔光的暖阳◎ 2022-03-02 02:29 245阅读 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)