mysql - 如何用 JOIN 替换 IN 子句？

Question

select [CLIENT_SUIT_ID]
  ,[CLIENT_CODE]
  ,[CARD_ID]
  ,[EXPIRE_DATE]
  ,ROW_NUMBER()over(partition by CARD_ID order by CARD_ID ASC)
from [KSS_TMP].[dbo].[CLIENT_SUIT]
where CARD_ID in(
SELECT CARD_ID
    from [KSS_TMP].[dbo].[CLIENT_SUIT]
    where CLIENT_CODE NOT LIKE '080%' AND card_id != '-'
    GROUP BY CARD_ID
    HAVING COUNT(CARD_ID) > 1
    )

你能告诉我如何用 JOIN 替换 IN 子句吗？我试图保持我的条件，所以我必须使用 IN。

标签： mysqlsql-servercursor

Answer 1

如果你想in用 a 替换你的子句join：

select [CLIENT_SUIT_ID]
  ,[CLIENT_CODE]
  ,[CARD_ID]
  ,[EXPIRE_DATE]
  ,ROW_NUMBER()over(partition by CARD_ID order by CARD_ID ASC)
from [KSS_TMP].[dbo].[CLIENT_SUIT] as Tab1 JOIN 
    (SELECT CARD_ID FROM [KSS_TMP].[dbo].[CLIENT_SUIT] 
     where CLIENT_CODE NOT LIKE '080%' AND card_id != '-' 
     GROUP BY CARD_ID 
     HAVING COUNT(CARD_ID) > 1 ) as Tab2 ON Tab1.Card_Id = Tab2.Card_Id

---

在您的情况下，您实际上并不需要额外的子查询。您可以使用另一个窗口函数在 1 个查询中完成所有操作conditional aggregation：

select * from (
  select *,
    row_number() over (partition by card_id order by card_id) rn,
    count(case when client_code not like '080%' and card_id != '-' 
               then 1 end) over (partition by card_id) cnt
  from client_suit
  ) t
where t.cnt > 1;