*** 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        ┃
┗━━━━━━━━━━┻━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

我们可以简单地这么做。

   select *
   from 
    table1 t, CRM_VCM_CURRENT_LEAD_STATUS c
    WHERE  t.CM_PLAN_ID = c.CRM_VCM_CURRENT_LEAD_STATUS
    and t.Individual_ID = c.Individual_ID

我觉得这样更容易

Select * 
from table1 
WHERE  (convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)) 
IN 
(
 Select convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)
 From CRM_VCM_CURRENT_LEAD_STATUS 
 Where Lead_Key = :_Lead_Key 
) 

希望这对你有所帮助:)

简单的EXISTS子句是最干净的

select *
from table1 t1
WHERE
EXISTS
(
 Select * --or 1. No difference...
 From CRM_VCM_CURRENT_LEAD_STATUS Ex
 Where Lead_Key = :_Lead_Key
-- correlation here...
AND
t1.CM_PLAN_ID = Ex.CM_PLAN_ID AND t1.CM_PLAN_ID =  Ex.Individual_ID
)

如果相关性中有多行，那么JOIN会在输出中给出多行，因此需要distinct。这通常使EXISTS更有效。

注意带有JOIN的SELECT *还包括行限制表中的列

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

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

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

Postgres SQL  : version 9.6
Total records on tables : mjr_agent = 145, mjr_transaction_item = 91800

1.使用EXISTS[平均查询时间:1.42s]

SELECT count(txi.id) 
FROM 
mjr_transaction_item txi
WHERE 
EXISTS ( SELECT 1 FROM mjr_agent agnt WHERE agnt.agent_group = 0 AND (txi.src_id = agnt.code OR txi.dest_id = agnt.code) ) 

2.使用两行IN子句[平均查询时间:0.37s]

SELECT count(txi.id) FROM mjr_transaction_item txi
WHERE 
txi.src_id IN ( SELECT agnt.code FROM mjr_agent agnt WHERE agnt.agent_group = 0 ) 
OR txi.dest_id IN ( SELECT agnt.code FROM mjr_agent agnt WHERE agnt.agent_group = 0 )

3.使用inner JOIN模式[平均查询时间:2.9s]

SELECT count(DISTINCT(txi.id)) FROM mjr_transaction_item txi
INNER JOIN mjr_agent agnt ON agnt.code = txi.src_id OR agnt.code = txi.dest_id
WHERE 
agnt.agent_group = 0

所以，我选择了第二种选择。