如果你想要一个表，然后使用以下查询

SELECT S.* 
FROM Student_info S
  INNER JOIN Student_info UT
    ON S.id = UT.id
    AND S.studentName = UT.studentName
where S.id in (1,2) and S.studentName in ('a','b')

并表中数据如下

id|name|adde|city
1   a   ad  ca
2   b   bd  bd
3   a   ad  ad
4   b   bd  bd
5   c   cd  cd

然后输出如下

id|name|adde|city
1   a   ad  ca
2   b   bd  bd

取而代之的是使用WHERE EXISTS语法。

SELECT *
FROM table1
WHERE EXISTS (SELECT *
              FROM table2
              WHERE Lead_Key = @Lead_Key
                        AND table1.CM_PLAN_ID = table2.CM_PLAN_ID
                        AND table1.Individual_ID = table2.Individual_ID)

你可以从子查询中创建一个派生表，并将table1连接到这个派生表:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL

为什么使用WHERE EXISTS或DERIVED表，当你可以只做一个普通的内部连接:

SELECT t.*
FROM table1 t
INNER JOIN CRM_VCM_CURRENT_LEAD_STATUS s
    ON t.CM_PLAN_ID = s.CM_PLAN_ID
    AND t.Individual_ID = s.Individual_ID
WHERE s.Lead_Key = :_Lead_Key

如果(CM_PLAN_ID, Individual_ID)在状态表中不是唯一的，则可能需要使用SELECT DISTINCT t.*。

简单而错误的方法是使用+或连接两列并生成一列。

Select *
from XX
where col1+col2 in (Select col1+col2 from YY)

这会偏离轨道，很慢。不能在编程中使用，但如果你只是查询验证一些东西可能会被使用。

*** t-sql ***

我使用string_agg作为廉价的hack来获得cheep上的一些伪规范化。(好吧，它是多路复用的，我知道它很糟糕，我用一些精心制作的80年代风格的盒子图来补偿你的痛苦，享受吧!~)

这里有一个例子(想出名字替代品很有趣:D)

select 
    vendorId,   
    affiliate_type_code, 
    parent_vendor_id,
    state_abbr,
    county_abbr,
    litigation_activity_indicator,
    string_agg(employee_id,',') as employee_ids,
    string_agg(employee_in_deep_doodoo,',') as 'employee-inventory connections'
from (
    select distinct top 10000 -- so I could pre-order my employee id's - didn't want mixed sorting in those concats
    mi.missing_invintory_identifier as rqid,
    vendorId,   
    affiliate_type_code, 
    parent_vendor_id,
    state_abbr,
    county_abbr,
    litigation_activity_indicator,
    employee_identifier as employee_id,
    concat(employee_identifier,'-',mi.missing_invintory_identifier) as employee_in_deep_doodoo
    from 
        missing_invintory as mi 
        inner join vendor_employee_view as ev
        on mi.missing_invintory_identifier = ev.missing_invintory_identifier        
    where ev.litigation_activity_indicator = 'N'
    order by employee_identifier desc

)  as x
    group by 
    vendorId,   
    affiliate_type_code, 
    parent_vendor_id,
    state_abbr,
    county_abbr,
    litigation_activity_indicator
    having count(employee_id) > 1



┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ vendorId ┃ affiliate_type ┃ parent_vendor_id ┃ state_abbr ┃ county_abbr       ┃ litigation_indicator ┃ employee_ids        ┃ employee-inventory connections ┃
┣━━━━━━━━━━╋━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃      123 ┃ EXP            ┃               17 ┃ CA         ┃ SDG               ┃ N                    ┃ 112358,445678       ┃ 112358-1212,1534490-1212       ┃
┣━━━━━━━━━━╋━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃     4567 ┃ PRI            ┃              202 ┃ TX         ┃ STB               ┃ Y                    ┃ 998754,332165       ┃ 998754-4545,332165-4545        ┃
┗━━━━━━━━━━┻━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛