mysql语句求助，有1千万条数据，里面有几个字段，name和id同时重复的有很多，我希望能把这部分重复的删除
发布网友
发布时间：2022-10-14 00:02
共1个回答
热心网友
时间：2023-11-18 09:08
SALE_DATE DATETIME NOT NULL ,
SALE_ITEM VARCHAR(2) NOT NULL ,
SALE_MONEY DECIMAL(10,2) NOT NULL
);
DELIMITER //
CREATE PROCEDURE CreateReportData()
BEGIN
DECLARE v_begin_day DATE;
DECLARE v_end_day DATE;
SET v_begin_day = STR_TO_DATE('2009-01-01', '%Y-%m-%d');
SET v_end_day = STR_TO_DATE('2013-01-01', '%Y-%m-%d');
WHILE v_begin_day < v_end_day DO
INSERT INTO SALE_REPORT VALUES
(v_begin_day, 'A',
Year(v_begin_day) );
INSERT INTO SALE_REPORT VALUES
(v_begin_day, 'B',
Month(v_begin_day) );
INSERT INTO SALE_REPORT VALUES
(v_begin_day, 'C',
DAY(v_begin_day) );
SET v_begin_day = DATE_ADD(v_begin_day, INTERVAL 1 DAY);
END WHILE;
END;
//
DELIMITER ;
call CreateReportData();
使用模拟的 ROW_NUMBER + PARTITION 的方式
SELECT
sale_item,
sale_date,
sale_money
FROM
(
SELECT
CASE
WHEN @cn != sale_item THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS No,
@cn := sale_item AS sale_item,
sale_date,
sale_money
FROM
(SELECT @rownum:=0) r,
(SELECT @cn:='') p,
SALE_REPORT
ORDER BY
SALE_ITEM,
SALE_DATE DESC
) subQuery
WHERE
no <=3;
+-----------+---------------------+------------+
| sale_item | sale_date | sale_money |
+-----------+---------------------+------------+
| A | 2012-12-31 00:00:00 | 2012.00 |
| A | 2012-12-30 00:00:00 | 2012.00 |
| A | 2012-12-29 00:00:00 | 2012.00 |
| B | 2012-12-31 00:00:00 | 12.00 |
| B | 2012-12-30 00:00:00 | 12.00 |
| B | 2012-12-29 00:00:00 | 12.00 |
| C | 2012-12-31 00:00:00 | 31.00 |
| C | 2012-12-30 00:00:00 | 30.00 |
| C | 2012-12-29 00:00:00 | 29.00 |
+-----------+---------------------+------------+
9 rows in set (0.02 sec)追问你这不是我想要的，你这回答我在其它地方见过
