在具有内部联接的存储过程中定义两个或多个条件

Question

首先，我需要通过变量@company_id来过滤表TbTaxCompanies和tbCompany中的数据。。。并且有效！

CREATE PROCEDURE taxcompany_importTaxes
@company_id int
AS
SELECT 
tc.idtaxcompany, tc.company_id, c.nameCompany, 
ct.tax1, ct.tax2, ct.tax3, ct.dateUpgrade
FROM 
tbTaxCompanies tc
INNER JOIN 
tbCompany c ON tc.company_id = c.idcompany
WHERE 
tc.company_id = @company_id
ORDER BY 
tc.idtaxcompany ASC

第二，我需要再次过滤之前用@company_id选择的公司的日志集，但现在只获得最新更新的税款(只有一行(。。。但这不起作用！

CREATE PROCEDURE taxcompany_importTaxes
@company_id int
AS
SELECT 
tc.idtaxcompany, tc.company_id, c.nameCompany, 
ct.tax1, ct.tax2, ct.tax3, ct.dateUpdate
FROM 
tbTaxCompanies tc
INNER JOIN 
tbCompany c ON tc.company_id = c.idcompany
WHERE 
tc.company_id = @company_id 
AND (tc.dateUpdate = (SELECT MAX (dateUpdate) FROM tbTaxCompanies))
ORDER BY 
tc.idtaxcompany ASC

我真的很感激任何帮助。

Answer 1

如果您想要第一个结果集中的最新一行，只需使用order by和top:

SELECT TOP (1) WITH TIES
tc.idtaxcompany, tc.company_id, c.nameCompany, 
tc.tax1, tc.tax2, tc.tax3, tc.dateUpgrade
FROM tbTaxCompanies tc
INNER JOIN tbCompany c on tc.company_id = c.idcompany
WHERE tc.company_id = @company_id
ORDER BY tc.dateUpdate DESC

如果您想要每个组的最新一行，例如每个tc.idtaxcompany，那么您可以将ORDER BY子句更改为：

ORDER BY RANK() OVER(PARTITION BY tc.idtaxcompany ORDER BY tc.dateUpdate DESC)