表数据
单号 日期 品号 数量
A001 2019-8-1 1 100
A002 2019-8-2 2 200
A003 2019-8-3 3 300
A004 2019-8-4 1 400
A005 2019-8-5 2 500
A006 2019-8-6 3 600
要查询出的结果
单号 日期 品号 数量
A001 2019-8-1 1 100
A002 2019-8-2 2 200
A003 2019-8-3 3 300
use Tempdb
go
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([单号] nvarchar(24),[日期] Date,[品号] int,[数量] int)
Insert #tab
select N'A001','2019-8-1',1,100 union all
select N'A002','2019-8-2',2,200 union all
select N'A003','2019-8-3',3,300 union all
select N'A004','2019-8-4',1,400 union all
select N'A005','2019-8-5',2,500 union all
select N'A006','2019-8-6',3,600
GO
--测试数据结束
SELECT 单号,日期,品号,数量
FROM
(
Select *,ROW_NUMBER() OVER(PARTITION BY [品号] order by [日期]) as rn
FROM #tab
) AS a
WHERE rn=1;
单号 日期 品号 数量
------------------------ ---------- ----------- -----------
A001 2019-08-01 1 100
A002 2019-08-02 2 200
A003 2019-08-03 3 300
https://blog.csdn.net/come_on_air/article/details/72902592
既然数据都重复，就时间不同，那就取max(时间)就行了吧
select 重复数据的列名1,重复数据的列名2,....,max(时间列) from table group by 重复数据的列名1,重复数据的列名2,....
这里有个MySQL里面可以用的关联子查询方法，这两天刚好在学这一节。顺便当作练习做了一下。
创建表的时候好像卡了一下，居然用了2秒多。。。。
mysql> CREATE TABLE orders
-> (order_num CHAR(4) NOT NULL,
-> order_date DATE,
-> product_id CHAR(1) NOT NULL,
-> amount INTEGER NOT NULL,
-> PRIMARY KEY (order_num));
Query OK, 0 rows affected (2.37 sec)
mysql> INSERT INTO orders VALUES
-> ('A001', '2019-8-1', '1' , 100 ),
-> ('A002', '2019-8-2', '2', 200 ),
-> ('A003', '2019-8-3', '3', 300 ),
-> ('A004', '2019-8-4', '1', 400 ),
-> ('A005', '2019-8-5', '2', 500 ),
-> ('A006', '2019-8-6', '3' ,600 );
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM orders;
+-----------+------------+------------+--------+
| order_num | order_date | product_id | amount |
+-----------+------------+------------+--------+
| A001 | 2019-08-01 | 1 | 100 |
| A002 | 2019-08-02 | 2 | 200 |
| A003 | 2019-08-03 | 3 | 300 |
| A004 | 2019-08-04 | 1 | 400 |
| A005 | 2019-08-05 | 2 | 500 |
| A006 | 2019-08-06 | 3 | 600 |
+-----------+------------+------------+--------+
6 rows in set (0.00 sec)
mysql> SELECT order_num, order_date, product_id, amount
-> FROM orders AS P1
-> WHERE order_date = (SELECT MIN(order_date)
-> FROM orders AS P2
-> WHERE P1.product_id = P2.product_id);
+-----------+------------+------------+--------+
| order_num | order_date | product_id | amount |
+-----------+------------+------------+--------+
| A001 | 2019-08-01 | 1 | 100 |
| A002 | 2019-08-02 | 2 | 200 |
| A003 | 2019-08-03 | 3 | 300 |
+-----------+------------+------------+--------+
3 rows in set (0.04 sec)