我有左外连接的问题 [英] i have problem with left outer join

问题描述

我想找到（左外联接）客户本月没有任何订单。我写了但由于上个月的记录而无法正常工作。为什么会发生这种情况？请指教我。



简单

我有两张桌子：



表1

 + ------ + ----- -  + 
 | col1 | col2 | 
 + ------ + ------ + 
 | 1 | a | 
 | 2 | b | 
 | 3 | c | 
 + ------ + ------ + 

表2

 + ------ + ------ + ------ + 
 | col1 | col2 | col3 | 
 + ------ + ------ + ------ + 
 | 1 | x | 0 | 
 | 2 | y | 0 | 
 | 1 | x | 1 | 
 + ------ + ------ + ------ + 
 2 y 1 

记录（ 2，'y'，1 ），未插入Table2，我希望通过左外连接找到它，如下所示。

  SELECT  *  FROM  table1 
 < span class =code-keyword> WHERE  col1  NOT   IN （ SELECT  col1  FROM  table2 
  WHERE  col3 = 1 ） AND  col1  IN （ SELECT  col1 < span class =code-keyword> FROM  table2）; 

 + ------ +  - ---- + 
 | col1 | col2 | 
 + ------ + ------ + 
 | 2 | b | 
 + ------ + ------ + 

  CREATE   TABLE `table1`（
`col1`  int ，
`col2`  varchar （ 1 ））; 
 
  CREATE   TABLE `table2`（
`col1` < span class =code-keyword> int ，
`col2`  varchar （ 1 ），
`col3`  int ）; 
 
  INSERT   INTO  table1  VALUES （ 1 ，'  a'），（ 2 ，'  b'），（ 3 ，'  c'）; 
 
  INSERT   INTO  table2  VALUES （ 1 ，'  x'， 0 ），（ 2 ，'  y'， 0 ），（ 1 ，'  x'， 1  ）; 

祝福。

解决方案

我不确定如果这是你想要的，但试试这个：

  SELECT  t1.col1，
 t1.col2 
  FROM  table1  AS  t1 
  LEFT   OUTER   JOIN （ SELECT  col1 
  FROM  table2 
  WHERE  col3 =  1 ） AS  t2 
  ON  t2.col1 = t1.col1 
  LEFT   OUTER   JOIN （ SELECT  col1 
 < span class =code-keyword> FROM  table2） AS  t3 
  ON  t3.col1 = t1.col1 
  WHERE  t2.col1  IS   NULL  
  AND  t3.col1  IS  不  NULL  

请告诉我它是如何运作的。

I want to found (with left outer join) customers that this month haven't any order. i wrote it but it not work properly because of previous month record.why this happent?, please guid me.

For simple
I have two tables:

Table1

+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

Table2

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | x    |    0 |
|    2 | y    |    0 |
|    1 | x    |    1 |
+------+------+------+
     2   y          1

Record (2,'y',1), not inserted In Table2 and I want found it with Left Outer Join, like below.

SELECT * FROM table1 
WHERE col1 NOT IN (SELECT col1 FROM table2 
                   WHERE col3=1) AND col1 IN (SELECT col1 FROM table2);

+------+------+
| col1 | col2 |
+------+------+
|    2 | b    |
+------+------+

CREATE TABLE `table1` (
  `col1` int,
  `col2` varchar(1));

CREATE TABLE `table2` (
  `col1` int,
  `col2` varchar(1),
  `col3` int);

INSERT INTO table1 VALUES (1,'a'),(2,'b'),(3,'c');

INSERT INTO table2 VALUES (1,'x',0),(2,'y',0),(1,'x',1);

Best wishes.

解决方案

I'm not sure if this is what you want, but try this:

SELECT t1.col1,
       t1.col2
FROM   table1 AS t1
       LEFT OUTER JOIN (SELECT col1
                        FROM   table2
                        WHERE  col3 = 1) AS t2
                    ON t2.col1 = t1.col1
       LEFT OUTER JOIN (SELECT col1
                        FROM   table2) AS t3
                    ON t3.col1 = t1.col1
WHERE  t2.col1 IS NULL
       AND t3.col1 IS NOT NULL

Please let me how it works for you.

这篇关于我有左外连接的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！