如何检索2个sql查询中指定的列数据

Question

我有一张桌子(tbl1)

 Description    EID        Basecode        
   -----------    ----       ---------    
  ssdad          1001       S2378797     
  gfd            1002       S1164478     
  gfdsffsdf      1003       R1165778     
  ssdad          1004       M0007867     
  gfd            1005       N7765111     
  gfdsffsdf      1006       W5464111     
  gfd            1005       N7765111     
  gfdsffsdf      1006       A4000011 
  gfdsffsdf      1006       W5464111     
  ssdad          1001       2378797     
  gfd            1002       1164478  
  ssdad          1001       965000
  gfd            1002       780000
  yjgk           4456       540000
  kjhkh          2009       150000
  ddd            1004       1040
  d88jg          1004       14C676
   fsa           6565       158 
   fdh           1004       2Khlm
   ggdg          2009       967

我正在检索所有Basecode列数据，该查询仅以'W‘、'N’以外的字母开头

 SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not 
 IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");

如果列数据长度>6，并以'96‘、'78’、'54‘、'15’号查询，则检索所有Basecode

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
(((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

如何获得基于上述查询而不会检索的其他数据，除了在这些查询中提到的数据以外

Description       EID        Basecode        
   -----------    ----       ---------    

  ssdad          1001       2378797     
  gfd            1002       1164478  
  ddd            1004       1040
  d88jg          1004       14C676
  fsa            6565       158 
  fdh            1004       2Khlm
   ggdg          2009       967

第三次查询无效

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
(((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

Answer 1

就像这样：

SELECT DISTINCT tbl1.EID,tbl1.Description,tabl1.Basecode 
FROM tbl1 
WHERE Basecode NOT IN
(
  SELECT tabl1.Basecode 
  FROM tbl1 
  WHERE Not IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
  UNION
  SELECT tabl1.Basecode
  FROM tbl1 
  WHERE Len([Basecode])>6 AND  Left([Basecode],2) IN ('15','54','78','96')
)

这也能起作用

SELECT DISTINCT tbl1.EID,tbl1.Description,tabl1.Basecode 
FROM tbl1 
WHERE 
  Basecode NOT IN
  (
    SELECT tabl1.Basecode 
    FROM tbl1 
    WHERE Not IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
  ) T1 AND 
  Basecode NOT IN
  (
    SELECT tabl1.Basecode
    FROM tbl1 
    WHERE Len([Basecode])>6 AND  Left([Basecode],2) IN ('15','54','78','96')
  ) T2