SQL查询以获取仅显示表中最新日期的数据(SQL query to get data that only show the latest date from tables)

我有3个表格如下：

表用户

+---+-----+--------+ | id| name| contact| +---+-----+--------+ |101| John| 123121 | |102| Jake| 123122 | |103| Mia| 123123 | |104| Mike| 123124 | |105|Drake| 123125 | |106|Jonas| 123126 | +---+-----+--------+

表交易

+------+--------+-------+ |billno|billdate|user_id| +------+--------+-------+ | A001|01/01/18| 101| | A002|01/01/18| 102| | A003|01/01/18| 103| | A004|01/02/18| 101| | A005|01/02/18| 105| | A006|01/02/18| 102| | A007|01/03/18| 105| | A008|01/03/18| 106| | A009|01/04/18| 101| | A010|01/04/18| 103| | A011|01/04/18| 106| +------+--------+-------+

表产品

+------+-----------+ |billno|productcode| +------+-----------+ | A001| P001| | A001| P002| | A001| P002| | A002| P001| | A002| P003| | A003| P001| | A003| P003| | A003| P004| | A004| P001| | A004| P005| | A005| P001| | A005| P003| | A005| P004| | A006| P001| | A007| P001| | A007| P005| | A008| P002| | A008| P003| | A008| P004| | A008| P005| | A009| P001| +------+-----------+

这个表就是一个例子，真实表上有更多的数据。

如果我的查询是这样的：

SELECT user.name, user.contact, transaction.billno, transaction.billdate FROM user inner join transaction on user.id = transaction.user_id inner join product on transaction.billno = product.billno WHERE product.productcode = 'P001'

它将显示所有账单号和用户，其产品代码为P001。

我的问题是，如何只显示产品代码为P001的用户的最新日期？

结果应该是这样的：

1 | John| 123121| A009| 01/04/18 2 |Drake| 123125| A007| 01/03/18 3 | Jake| 123125| A006| 01/02/18

它只是获取具有产品代码P001的用户标识的最新日期。

希望你们理解我的问题。谢谢。 :)

I have 3 table as below:

Table User

+---+-----+--------+ | id| name| contact| +---+-----+--------+ |101| John| 123121 | |102| Jake| 123122 | |103| Mia| 123123 | |104| Mike| 123124 | |105|Drake| 123125 | |106|Jonas| 123126 | +---+-----+--------+

Table Transaction

+------+--------+-------+ |billno|billdate|user_id| +------+--------+-------+ | A001|01/01/18| 101| | A002|01/01/18| 102| | A003|01/01/18| 103| | A004|01/02/18| 101| | A005|01/02/18| 105| | A006|01/02/18| 102| | A007|01/03/18| 105| | A008|01/03/18| 106| | A009|01/04/18| 101| | A010|01/04/18| 103| | A011|01/04/18| 106| +------+--------+-------+

Table Product

+------+-----------+ |billno|productcode| +------+-----------+ | A001| P001| | A001| P002| | A001| P002| | A002| P001| | A002| P003| | A003| P001| | A003| P003| | A003| P004| | A004| P001| | A004| P005| | A005| P001| | A005| P003| | A005| P004| | A006| P001| | A007| P001| | A007| P005| | A008| P002| | A008| P003| | A008| P004| | A008| P005| | A009| P001| +------+-----------+

This table is an example, there a lot more data on the real table.

If my query is like this:

SELECT user.name, user.contact, transaction.billno, transaction.billdate FROM user inner join transaction on user.id = transaction.user_id inner join product on transaction.billno = product.billno WHERE product.productcode = 'P001'

it will show all bill no and user that have product code P001.

My question is, how to show only the latest date for the user that have product code P001?

The result should be like this:

1 | John| 123121| A009| 01/04/18 2 |Drake| 123125| A007| 01/03/18 3 | Jake| 123125| A006| 01/02/18

It just take the latest date for that user id that have product code P001.

Hope you guys understand my question. Thanks. :)

最满意答案

您当前的尝试似乎有效，只需添加top（1）with ties子句即可显示每个用户的最新帐单

select top(1) with ties u.id, u.name, u.contact, t.billno, t.billdate from User u inner join Transaction t on t.user_id = u.id inner join Product p on p.billno = t.billno where p.productcode = 'P001' order by row_number() over (partition by u.id order by t.billdate desc)

Your current attempt seems work, just add top(1) with ties clause to display most recent bills for each user

select top(1) with ties u.id, u.name, u.contact, t.billno, t.billdate from User u inner join Transaction t on t.user_id = u.id inner join Product p on p.billno = t.billno where p.productcode = 'P001' order by row_number() over (partition by u.id order by t.billdate desc)

更多推荐