数据错误，不存在 [英] Data wrong with not exists

问题描述

我想比较两个表，我想选择不在另一个智能卡列表中的智能卡，但我得到的数字非常高，应该是最多90或120，但带给我248k，表A有312k记录，表B有478K记录，不一致的数量通常不超过120，想知道我的查询是否有问题。

 选择 a.SMARTCARD，a.STB，a.AREA，a。 ESTATE 
 来自 REPORT_CAS a 
 其中 不 存在（选择 SMARTCARD 来自 REPORT_IW b 
 其中 a.smartcard = b.smartcard 
 和 a.stb = b.stb）
  ' 和 a.smartcard  string> 1303379968' 和 '  1303969791 ' 
 和 len（智能卡）=  10  
 和 ltrim（rtrim（stb））    （'  0'）
  group  按 a.SMARTCARD，a.STB，a.AREA，a.ESTATE 

解决方案

你可以直接使用except子句。

请阅读问题的评论并按照那里提供的链接。



试试这个：

  SELECT  a.SMARTCARD，a.STB，a.AREA，a.ESTATE 
  FROM  REPORT_CAS  AS  a  LEFT   OUTER   JOIN （
  SELECT  SMARTCARD 
  FROM  REPORT_IW 
） AS  b  ON  a.stb = b.stb  AND  a.smartcard = b.smartcard 
  WHERE  a.smartcard  BETWEEN   1303379968   AND   1303969791   AND  b.stb  IS   NULL   AND  b .smartcard  IS   NULL  

hi,
I am trying to compare two tables, I want to select the smartcards that are not in another the other list of smartcards , but the number I get is extremely high , should be around 90 or 120 maximum , but brings me 248k , Table A has 312k records and table B has 478K records , the number of inconsistencies is usually not more than 120 and wanted to know if I'm doing something wrong with my query.

Select a.SMARTCARD,a.STB,a.AREA,a.ESTATE
From REPORT_CAS a
Where not exists(  select SMARTCARD From REPORT_IW b
                 Where a.smartcard = b.smartcard
                 And a.stb = b.stb)
And a.smartcard  between '1303379968' and '1303969791' 
and len(smartcard) = 10
and ltrim(rtrim(stb)) not in ('0')          
group by a.SMARTCARD,a.STB,a.AREA,a.ESTATE

解决方案

You can directly use except clause.

Please, read comments to the question and follow the link provided there.

Try this:

SELECT a.SMARTCARD,a.STB,a.AREA,a.ESTATE
FROM REPORT_CAS AS a LEFT OUTER JOIN (
    SELECT SMARTCARD
    FROM REPORT_IW
) AS b ON a.stb = b.stb AND a.smartcard = b.smartcard
WHERE a.smartcard BETWEEN 1303379968 AND 1303969791 AND b.stb IS NULL AND b.smartcard IS NULL

这篇关于数据错误，不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！