如何在不复制表单内部联接表的情况下选择记录

Question

从这个查询中，我如何获取没有重复值的记录。我需要获取值，如果表 a 中的名称和表 c 中的帐户应该在日期字段为最大值时查看。

SELECT
      a.name
    , a.dates
    , b.da
    , a.id
    , b.client
    , b.[Address]
    , b.[City]
    , b.[State]
    , b.dot
    , b.Score
    , b.Pay
    , b.code
    , b.[Country]
FROM a
INNER JOIN c ON a.Account = c.Account
INNER JOIN b ON [a].name = b.name
WHERE c.users = 00
    AND b.Act = 1
ORDER BY a.dates

Answer 1

使用 DISTINCT ？

SELECT DISTINCT
      a.name
    , a.dates
    , b.da
    , a.id
    , b.client
    , b.[Address]
    , b.[City]
    , b.[State]
    , b.dot
    , b.Score
    , b.Pay
    , b.code
    , b.[Country]
FROM a
INNER JOIN c ON a.Account = c.Account
INNER JOIN b ON [a].name = b.name
WHERE c.users = 00
    AND b.Act = 1
ORDER BY a.dates

Answer 2

使用 CTE 怎么样？

with CTE_duplicate 
as
(SELECT 
  a.name
, a.dates
, b.da
, a.id
, b.client
, b.[Address]
, b.[City]
, b.[State]
, b.dot
, b.Score
, b.Pay
, b.code
, b.[Country]
, row_number() over ( partition by a.name
, a.dates
, b.da
, a.id
, b.client
, b.[Address]
, b.[City]
, b.[State]
, b.dot
, b.Score
, b.Pay
, b.code
, b.[Country]   order by a.name
, a.dates
, b.da
, a.id
, b.client
, b.[Address]
, b.[City]
, b.[State]
, b.dot
, b.Score
, b.Pay
, b.code
, b.[Country] ) as rownumber
FROM a
INNER JOIN c ON a.Account = c.Account
INNER JOIN b ON [a].name = b.name
WHERE c.users = 00
AND b.Act = 1
) select * from  CTE_duplicate where rownumber=1 order by dates;