文章/答案/技术大牛

发布

问多个内连接- SQL
EN

Stack Overflow用户

提问于 2017-04-07 16:06:07

回答 1查看 512关注 0票数 2

我有这个SQL请求，但不能工作...我真的不知道为什么，因为我从SQL开始...

Select CDR.*,
  p1.*,
  p2.*,
  p3.*
From dbo.T_CDR_APPEL CDR
  Inner Join dbo.POS As p1 On CDR.CDR_LOC_CODE = p1.Loc_Code And
    CDR.CDR_LOC_CODE Like 'SC%VI%' And CDR.CDR_TYPE Like 'I%'
  Inner Join dbo.POS As p2 On CDR.CDR_LAST_REDIR_NO = p2.Post_type And
    CDR.CDR_LOC_CODE Like 'CCN%' And CDR.CDR_TYPE Like 'I'
  Inner Join dbo.POS As p3 On CDR.CDR_LAST_REDIR_NO = p3.Backup_Number And
    CDR.CDR_LOC_CODE Like 'VoiceMail%' And CDR.CDR_TYPE Like 'IF'

谢谢你的帮忙

sql

sql-server

回答 1

Stack Overflow用户

发布于 2017-04-07 16:14:29

您的select语句

Select CDR.*,
  p1.*,
  p2.*,
  p3.*
From dbo.T_CDR_APPEL CDR
  Inner Join dbo.POS As p1 On CDR.CDR_LOC_CODE = p1.Loc_Code And
    CDR.CDR_LOC_CODE Like 'SC%VI%' And CDR.CDR_TYPE Like 'I%'
  Inner Join dbo.POS As p2 On CDR.CDR_LAST_REDIR_NO = p2.Post_type And
    CDR.CDR_LOC_CODE Like 'CCN%' And CDR.CDR_TYPE Like 'I'
  Inner Join dbo.POS As p3 On CDR.CDR_LAST_REDIR_NO = p3.Backup_Number And
    CDR.CDR_LOC_CODE Like 'VoiceMail%' And CDR.CDR_TYPE Like 'IF'

基本上与

SELECT CDR.*,
  p1.*,
  p2.*,
  p3.*
FROM dbo.T_CDR_APPEL CDR
  INNER JOIN dbo.POS AS p1 ON CDR.CDR_LOC_CODE = p1.Loc_Code
  INNER JOIN dbo.POS AS p2 ON CDR.CDR_LAST_REDIR_NO = p2.Post_type
  INNER JOIN dbo.POS AS p3 ON CDR.CDR_LAST_REDIR_NO = p3.Backup_Number
WHERE
    CDR.CDR_LOC_CODE LIKE 'SC%VI%' AND     
    CDR.CDR_LOC_CODE LIKE 'CCN%' AND 
    CDR.CDR_LOC_CODE LIKE 'VoiceMail%' AND 
    CDR.CDR_TYPE LIKE 'I%' AND 
    CDR.CDR_TYPE LIKE 'I' AND 
    CDR.CDR_TYPE LIKE 'IF'

因此，问题出在WHERE子句中，对于相同的列CDR.CDR_LOC_CODE和CDR.CDR_TYPE，有许多AND条件的组合。请尝试将AND替换为OR。

更新：由于p1,p2,p3属于同一个表，为了避免重复列，请尝试执行以下操作

SELECT CDR.*, p.* FROM 
(
   SELECT *, 
   (CASE 
        WHEN CDR_TYPE LIKE 'I%' THEN CDR_LOC_CODE
        WHEN CDR_TYPE LIKE 'I' THEN CDR_LAST_REDIR_NO
        WHEN CDR_TYPE LIKE 'IF' THEN CDR_LAST_REDIR_NO
        ELSE ''
    END) TMP_COL FROM dbo.T_CDR_APPEL 
) CDR
INNER JOIN dbo.POS p ON
   (CASE 
        WHEN CDR.CDR_TYPE LIKE 'I%' THEN p.Loc_Code
        WHEN CDR.CDR_TYPE LIKE 'I' THEN p.Post_type
        WHEN CDR.CDR_TYPE LIKE 'IF' THEN p.Backup_Number
        ELSE ''
    END) = CDR.TMP_COL
WHERE
   (CDR.CDR_LOC_CODE LIKE 'SC%VI%' AND CDR.CDR_TYPE LIKE 'I%') OR
   (CDR.CDR_LOC_CODE LIKE 'CCN%' AND CDR.CDR_TYPE LIKE 'I') OR
   (CDR.CDR_LOC_CODE LIKE 'VoiceMail%' AND CDR.CDR_TYPE LIKE 'IF')

注意:对于大表，运行上述查询可能需要一段时间。您可能希望在WHERE子句中添加一些条件，以便进一步调优。另外，我们应该将WHERE子句放入子查询，因为CDR_LOC_CODE和CDR_TYPE属于同一个表dbo.T_CDR_APPEL。另外，为动态列TMP_COL选中Null或empty，以避免联接表ON '' = ''或ON NULL = NULL。

SELECT CDR.*, p.* FROM 
(
   SELECT *, 
   (CASE 
        WHEN CDR_TYPE LIKE 'I%' THEN CDR_LOC_CODE
        WHEN CDR_TYPE LIKE 'I' THEN CDR_LAST_REDIR_NO
        WHEN CDR_TYPE LIKE 'IF' THEN CDR_LAST_REDIR_NO
        ELSE ''
    END) TMP_COL FROM dbo.T_CDR_APPEL 
    WHERE
       (CDR_LOC_CODE LIKE 'SC%VI%' AND CDR_TYPE LIKE 'I%') OR
       (CDR_LOC_CODE LIKE 'CCN%' AND CDR_TYPE LIKE 'I') OR
       (CDR_LOC_CODE LIKE 'VoiceMail%' AND CDR_TYPE LIKE 'IF')
) CDR
INNER JOIN dbo.POS p ON
   (CASE 
        WHEN CDR.CDR_TYPE LIKE 'I%' THEN p.Loc_Code
        WHEN CDR.CDR_TYPE LIKE 'I' THEN p.Post_type
        WHEN CDR.CDR_TYPE LIKE 'IF' THEN p.Backup_Number
        ELSE ''
    END) = CDR.TMP_COL AND NULLIF(CDR.TMP_COL, '') IS NOT NULL

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/43272890

复制

相似问题

问多个内连接- SQL
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问多个内连接- SQLEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问多个内连接- SQL
EN