卡在具有多个连接的SQL查询上。

Alright, the system I got is a pretty outdated ERP system based around an Ingres database. The database schema is ... well ... not very nice (not really normalized) but basically it works out. Please understand that I cannot change anything related to the database.

我得到的系统是一个基于Ingres数据库的过时的ERP系统。数据库模式是…嗯…不是很好(不是真正的规范化)，但是基本上是这样的。请理解我不能更改任何与数据库相关的内容。

Consider the following SQL statement:

考虑下面的SQL语句:

SELECT
  -- some selected fields here
FROM    
    sta_artikelstamm s
    left join sta_chargen c on c.artikel_nr = s.artikel_nr and c.lager != 93
    left join sta_artikelbeschreib b on s.artikel_nr = b.artikel_nr  and b.seite = 25 and b.zeilennr = 1
    left join sta_einkaufskonditionen ek on s.artikel_nr = ek.artikel_nr AND s.lieferant_1 = ek.kunden_nr
    left join sta_kundenstamm ks on ek.kunden_nr = ks.nummer AND ks.nummer = s.lieferant_1
    left join tab_teilegruppe2 tg2 on s.teilegruppe_2 = tg2.teilegruppe
WHERE 
    (s.status = 0)
AND 
    (s.teilegruppe_2 IS NOT NULL) AND (s.teilegruppe_2 != '')

So far, this works as expected, I get exactely 40742 results back. The result set looks alright, the number matches about what I would expect and the statement has shown no duplicates. I explicitly use a LEFT JOIN since some fields in related tables may not contain entries but I would like to keep the info from the main article table nonetheless.

到目前为止，这是预期的工作，我得到了确切的40742的结果。结果集看起来很好，数字与我所期望的相符，并且语句没有显示重复。我显式地使用左连接，因为相关表中的某些字段可能不包含条目，但我还是希望将信息保留在主文章表中。

Now, table tab_teilegruppe2 consists of 3 fields (bezeichnung = description, teilegruppe = part group == primary key, taricnr - please ignore this field, it may be null or contain some values but I don't need it).

现在，表tab_teilegruppe2包含3个字段(bezeichnung = description, teilegruppe = part group == = primary key, taricnr -请忽略这个字段，它可能是null或包含一些值，但我不需要它)。

I though of adding the following SQL part to only include rows in the resultset which do NOT appear in a specific part group. I therefore added the following line at the very end of the SQL statement.

我想添加下面的SQL部分，只包含resultset中不出现在特定部分组中的行。因此，我在SQL语句的末尾添加了以下一行。

AND (s.teilegruppe_2 NOT IN (49,57,60,63,64,65,66,68,71,73,76,77,78,79,106,107))

I'm by no means an SQL expert (you probably have guessed that already), but shouldn't an additional WHERE statement remove rows instead of adding? As soon as I add this simple additional statement in the WHERE clause, I get 85170 result rows.

我绝不是SQL专家(您可能已经猜到了)，但是一个附加的WHERE语句不应该删除行而不是添加行吗?只要在WHERE子句中添加这个简单的附加语句，就会得到85170结果行。

Now I'm guessing it has to do with the "NOT IN" statement, but I don't understand why I suddenly get more rows than before. Anyone can give me a pointer where to look for my error?

现在我猜这与“不在”语句有关，但是我不明白为什么我突然得到比以前更多的行。谁能给我一个指针去查找我的错误?

1 个解决方案

#1

What is the type of the s.teilegruppe_2 column? Is it an integer or some sort of string (VARCHAR)?

s的类型是什么?teilegruppe_2列?它是整数还是某种字符串(VARCHAR)?

The (s.teilegruppe_2 != '') suggests it is a string but your NOT IN is comparing it against a list of integers.

(s。teilegruppe_2 != ")表示它是一个字符串，但您的NOT IN将它与一个整数列表进行比较。

If the column involved is a string then the NOT IN list will match all the values since none of them are going to match an integer value.

如果所涉及的列是一个字符串，那么NOT IN列表将匹配所有的值，因为它们都不匹配一个整数值。

#1