我正在尝试在两个表之间进行交叉连接并消除重复

Question

这是我的查询：

select ios_Subscribers.ID, ios_Eligibility.SUBSCRIBERID, ios_Subscribers.LNAME, ios_Subscribers.FNAME, ios_Eligibility.DESCR, ios_Eligibility.VERIFICATIONDATE`
FROM ios_Subscribers CROSS JOIN
ios_eligibility
WHERE (ios_Eligibility.VERIFICATIONDATE = CONVERT(DATETIME, '2019-11-20 00:00:00', 102)) AND (ios_Eligibility.DESCR = 'WAIVER Advantage') AND (ios_Subscribers.Active = '1')
ORDER BY LNAME asc;

Answer 1

尝试使用 Distinct：

select distinct ios_Subscribers.ID, ios_Eligibility.SUBSCRIBERID, ios_Subscribers.LNAME, ios_Subscribers.FNAME, ios_Eligibility.DESCR, ios_Eligibility.VERIFICATIONDATE`
FROM ios_Subscribers CROSS JOIN
ios_eligibility
WHERE (ios_Eligibility.VERIFICATIONDATE = CONVERT(DATETIME, '2019-11-20 00:00:00', 102)) AND (ios_Eligibility.DESCR = 'WAIVER Advantage') AND (ios_Subscribers.Active = '1')
ORDER BY LNAME asc;