SQL query to ask-CodePudding

Table record how the SQL query, how to select only the customer's last record?

For example:

Customer value date
Zhang SAN 500 2020-11-11
Li si 850 2020-11-08
Zhang SAN 1180 2020-11-01
Li si 580 2020-10-31

.

Query results show only the last updated date of each customer record? Such as zhang the 2020-11-11 amount shown only 500 this record? Bill only shows the amount 2020-11-08 record of 850?

CodePudding user response:

According to essentially a grammar as follows, similar to other database:
Select a. * From table as a
Inner join (Select clients, Max (date) as the biggest date From table GroupBy customers) as b
On a. customer=b. and a. date=b. biggest date

Note: if the customer, the date have duplicate records, the results may have multiple (m * n), so it is best to only one primary key field connections,

CodePudding user response:

 

The create table # t 
(
Customer varchar (10), 
The amount of money, 
Date a datetime 
) 

Insert into # t 
Select 'zhang, 500,' 2020-11-11 'union all 
Select the 'bill', 850, '2020-11-08' union all 
Select 'zhang, 1180,' 2020-11-01 'union all 
Select the 'bill', 580, '2020-10-31' 

Select * from # t 

SELECT clients, amount, date FROM 
(SELECT clients, amount, date, ROW_NUMBER () OVER (PARTITION BY customer ORDER BY date DESC) AS XH from # t) tt WHERE XH=1

CodePudding user response:

Forgot to say, is the sqlite

reference 4 floor Mr Huang... Response:

 

The create table # t 
(
Customer varchar (10), 
The amount of money, 
Date a datetime 
) 

Insert into # t 
Select 'zhang, 500,' 2020-11-11 'union all 
Select the 'bill', 850, '2020-11-08' union all 
Select 'zhang, 1180,' 2020-11-01 'union all 
Select the 'bill', 580, '2020-10-31' 

Select * from # t 

SELECT clients, amount, date FROM 
(SELECT clients, amount, date, ROW_NUMBER () OVER (PARTITION BY customer ORDER BY date DESC) AS XH from # t) tt WHERE XH=1

CodePudding user response:

reference 1st floor samsone response:

SELECT clients, amount, Max (date) FROM table GROUP BY the customer, the amount

This one or all records query out, how to keep only every customer updated the latest the record?

CodePudding user response:

 
SELECT * FROM table a 
INNER JOIN (
SELECT customers, Max date (date) FROM table GROUP BY customer) b 
ON a. date=b. date

CodePudding user response:

Ok, have been solved, the method here:

https://blog.csdn.net/bushizhuanjia/article/details/6854208

CodePudding user response:

The general called windowing function

Sqllite after 3.25 version has support

The official explanation:
https://www.sqlite.org/windowfunctions.html