我有两个表,一个是客户表,一个是付款表
需要有特定标准的客户最近的付款。为那个specific_criteria创建了一个查询
select top 1 *
from payments
where id in (select id from specific_criteria);
显然,它只返回一行。
我实际上不能在这个数据库中编写VB代码来实现它。
寻找一些SQL代码,将获得每个客户最近的付款。
SELECT c.*,
p.*
FROM Customers c
INNER JOIN
Payments p ON p.Customer_ID=c.ID
WHERE p.ID IN (SELECT p2.ID FROM
(SELECT p1.ID, MAX(p1.payment_date)
FROM Payments p1
GROUP BY p1.ID) AS p2)
这样的东西会起作用吗?
select c.*, (
select top 1 p.*
from payments p
where p.customer_id = c.id
order by p.payment_date desc )
from customers c
where customer_critera