sql server—在sql查询中添加包含项的正确方法是什么？

mbzjlibv 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(286)

我正在使用下面的测试sql查询，根据接收特定电子邮件的logic+获取客户计数（确保客户存在于email sent表中）

select 
    count(distinct tab1.order_key) as VALUE
from 
    cust_order_type tab1   
inner join 
    cust_membership tab2 on tab1.transaction_id = tab2.transaction_id   
inner join 
    cust_transactions tab3 on tab1.transaction_id = tab3.transaction_id   
                           and tab3.coupon_id not in (123, 456) 
                           ---CHECK IF THEY RECEIVED THE EMAIL    
                           and tab3.customer_key IN (select customer_key from  cust_table tab4  
                                                      where tab4.email in (select email from Email_Sent_Date_List tab5 where tab5.EmailA_Date IS NOT NULL AND tab5.EmailA_Date >= '2020-07-06'))
inner join 
    cust_product_variation tab6 on tab3.product_variation_id = tab6.product_variation_id 
where 
    tab6.country in ('USA', 'CANADA')   
    and tab1.trans_date = '2020-07-06'

此查询返回600的输出。
上面的查询一直在工作，但我注意到，include查询逻辑用于检查客户是否收到电子邮件，它存在于内部连接中。说到这个：

---CHECK IF THEY RECEIVED THE EMAIL
and tab3.customer_key IN (select customer_key from  cust_table tab4  where  tab4.email in (select email from Email_Sent_Date_List tab5 where tab5.EmailA_Date IS NOT NULL AND tab5.EmailA_Date >= '2020-07-06'))

现在，我试图理解和质疑，这是否是正确的地方，为该标准？
因为，如果我把这个条件从内部连接中移出来，然后像这样加上结尾：

select count(distinct tab1.order_key) as VALUE
from cust_order_type tab1   
inner join cust_membership tab2
    on tab1.transaction_id = tab2.transaction_id   
   inner join cust_transactions tab3    
    on tab1.transaction_id = tab3.transaction_id   
    and tab3.coupon_id not in (123, 456) 
inner join cust_product_variation tab6 
    on tab3.product_variation_id = tab6.product_variation_id 
where tab6.country  IN ('USA','CANADA')   
    and tab1.trans_date  '2020-07-06'
    ---CHECK IF THEY RECEIVED THE EMAIL    
    and tab3.customer_key IN (select customer_key from  cust_table tab4  where  tab4.email in (select email from Email_Sent_Date_List tab5 where tab5.EmailA_Date IS NOT NULL AND tab5.EmailA_Date >= '2020-07-06'))

现在这个查询返回18000的输出。
输出的数字不一样，怎么回事？有人能帮我理解这一点，并提出这个标准的正确位置吗？
update：测试哪些表应该或不应该在内部联接中使用。我试着做个测试。
我删除了电子邮件发送条件并运行了以下查询：

select count(distinct tab1.order_key) as VALUE
from cust_order_type tab1   
inner join cust_membership tab2
    on tab1.transaction_id = tab2.transaction_id   
   inner join cust_transactions tab3    
    on tab1.transaction_id = tab3.transaction_id   
    and tab3.coupon_id not in (123, 456) 
inner join cust_product_variation tab6 
    on tab3.product_variation_id = tab6.product_variation_id 
where tab6.country  IN ('USA','CANADA')   
    and tab1.trans_date  '2020-07-06'

产量：12万
现在添加了一个带有cust表的内部连接（仍然没有电子邮件发送条件）

select count(distinct tab1.order_key) as VALUE
from cust_order_type tab1   
inner join cust_membership tab2
    on tab1.transaction_id = tab2.transaction_id   
   inner join cust_transactions tab3    
    on tab1.transaction_id = tab3.transaction_id   
    and tab3.coupon_id not in (123, 456) 
inner join cust_product_variation tab6 
    on tab3.product_variation_id = tab6.product_variation_id

INNER JOIN cust_table tab4
ON tab3.customer_key = tab4.customer_key

where tab6.country  IN ('USA','CANADA')   
    and tab1.trans_date  '2020-07-06'

同产量：12万
请让我知道，如果这有助于了解如何正确使用客户表除了电子邮件发送的标准？

sql sql-server

来源：https://stackoverflow.com/questions/63293155/what-is-the-correct-way-to-add-an-inclusion-in-the-sql-query-in-terms-of-join

2条答案

按热度按时间

eyh26e7m1#

回答：对于任何正在寻找类似答案或只是试图理解逻辑的人。
在我的例子中，query1给出了预期的结果（与query2的结果相比，query2的结果给出的计数要比email sent表中实际存在的联系人数量多）。
通过保持query1的逻辑相同，我最终对其进行了轻微的更新（这将得到与以前完全相同的结果）
这是我的工作更新query1（给出与前面相同的结果，与query2相比，这是一个预期的输出）：

select count(distinct tab1.order_key) as VALUE
from cust_order_type tab1   
inner join cust_membership tab2
    on tab1.transaction_id = tab2.transaction_id   
   inner join cust_transactions tab3    
    on tab1.transaction_id = tab3.transaction_id   
    and tab3.coupon_id not in (123, 456)
inner join cust_table tab4 
on tab3.customer_key = tab4.customer_key
inner join Email_Sent_Date_List tab5
on tab4.email = tab5.email       
    and (select email from Email_Sent_Date_List tab5 where tab5.EmailA_Date IS NOT NULL AND tab5.EmailA_Date >= '2020-07-06')  ---CHECK IF THEY RECEIVED THE EMAIL 
inner join cust_product_variation tab6 
    on tab3.product_variation_id = tab6.product_variation_id 
where tab6.country  IN ('USA','CANADA')   
    and tab1.trans_date  '2020-07-06'

赞(0）回复(0）举报 2021-07-24

cyvaqqii2#

考虑调整具体的 JOIN 更紧凑、更清晰的逻辑：

inner join Email_Sent_Date_List tab5
    on  tab4.email = tab5.email       
    and tab5.EmailA_Date IS NOT NULL      -- CHECK IF THEY RECEIVED THE EMAIL 
    and tab5.EmailA_Date >= '2020-07-06'  -- CHECK IF THEY RECEIVED THE EMAIL

事实上，你可能不需要任何外部的 WHERE 所有联接都是 INNER JOIN 以及 WHERE 可以认为是一个隐含的 JOIN . 请参阅更完整的调整和更有用的别名。

select count(distinct o.order_key) as [VALUE]
from cust_order_type o   
inner join cust_membership m
    on o.transaction_id = m.transaction_id   
    and o.trans_date = '2020-07-06'        -- MOVED FROM WHERE TO ON
inner join cust_transactions t    
    on o.transaction_id = t.transaction_id   
    and t.coupon_id not in (123, 456)
inner join cust_table c 
    on t.customer_key = c.customer_key
inner join Email_Sent_Date_List e
    on c.email = e.email       
    and e.EmailA_Date IS NOT NULL          -- CHECK IF THEY RECEIVED THE EMAIL 
    and e.EmailA_Date >= '2020-07-06'      -- CHECK IF THEY RECEIVED THE EMAIL 
inner join cust_product_variation p 
    on t.product_variation_id = p.product_variation_id 
    and p.country IN ('USA','CANADA')      -- MOVED FROM WHERE TO ON

赞(0）回复(0）举报 2021-07-24

我来回答

sql server—在sql查询中添加包含项的正确方法是什么？

2条答案

相关问题

热门标签

最新问答