首页 文章

SQL - 两个表上的SELECT语句

提问于
浏览 次
1

我想创建一个SELECT语句，从同一个数据库中的两个表中选择两个不同的列 .

SELECT  TNC301X.DIRIN, NC301B.PATH301
FROM    TNC301X, NC301B
WHERE   TNC301X.EDIPROC like 'P30_' AND NC301B.EDIPROC like 'P30_'
    AND (LASTENRIN > 0) OR (LF301M > 0) 
    AND (DIRIN NOT LIKE '%saptemp%')
    AND (DIRIN NOT LIKE '%SAPTEMP%')  
    AND (DIRIN NOT LIKE '%usr%')  
    AND (DIRIN NOT LIKE '%Windows%');

我只想选择列DIRIN和PATH301中的内容 .

TNC301X.DIRIN         NC301B.PATH301
\\ABC\DEF\            \\ABC\DEF\
\\GHI\JKL\            \\GHI\JKL\ 
\\MNO\PQR\            \\MNO\PQR\

声明似乎在语法上是正确的，但输出不是 . 当我为每个表创建一个语句时，输出是正确的 . 表格不相等 .

SELECT-PATH301

SELECT  PATH301 
FROM    NC301B
WHERE   EDIPROC like 'P30_'
    AND (LF301M > 0) 
    AND (PATH301 NOT LIKE '%saptemp%')
    AND (PATH301 NOT LIKE '%SAPTEMP%')  
    AND (PATH301 NOT LIKE '%usr%')  
    AND (PATH301 NOT LIKE '%Windows%');

SELECT-DIRIN

SELECT  DIRIN
FROM    TNC301X
WHERE   EDIPROC like 'P30_'
    AND (LASTENRIN > 0) 
    AND (DIRIN NOT LIKE '%saptemp%')
    AND (DIRIN NOT LIKE '%SAPTEMP%')  
    AND (DIRIN NOT LIKE '%usr%')  
    AND (DIRIN NOT LIKE '%Windows%');

我想要做的就是结合这些陈述，但我不知道如何 .

编辑：我明白了 .

FYI

SELECT DIRIN 
FROM TNC301X 
WHERE EDIPROC like 'P30_' 
AND (DIRIN NOT LIKE '%saptemp%') 
AND (DIRIN NOT LIKE '%SAPTEMP%')
AND (DIRIN NOT LIKE '%usr%')  
AND (DIRIN NOT LIKE '%Windows%')
UNION ALL 
SELECT PATH301 
FROM NC301B 
WHERE EDIPROC like 'P30_' 
AND (PATH301 NOT LIKE '%saptemp%') 
AND (PATH301 NOT LIKE '%SAPTEMP%')
AND (PATH301 NOT LIKE '%usr%')  
AND (PATH301 NOT LIKE '%Windows%');
sql oracle

2 回答

  • 0

    要加入两个表，您需要匹配两个表的id，如'where table1.id = table2.id' . 在这种情况下我认为加入这两个表的id是EDIPROC列 . 我可能错了 .

    SELECT NC.PATH301,TNC.DIRIN
FROM (
      SELECT  PATH301 
      FROM    NC301B
      WHERE   EDIPROC like 'P30_'
      AND (LF301M > 0) 
      AND (PATH301 NOT LIKE '%saptemp%')
      AND (PATH301 NOT LIKE '%SAPTEMP%')  
      AND (PATH301 NOT LIKE '%usr%')  
      AND (PATH301 NOT LIKE '%Windows%')
 ) NC, 
 (   
      SELECT  DIRIN
      FROM    TNC301X
      WHERE   EDIPROC like 'P30_'
      AND (LASTENRIN > 0) 
      AND (DIRIN NOT LIKE '%saptemp%')
      AND (DIRIN NOT LIKE '%SAPTEMP%')  
      AND (DIRIN NOT LIKE '%usr%')  
      AND (DIRIN NOT LIKE '%Windows%')
 ) TNC
 WHERE NC.EDIPROC  = TNC.EDIPROC
    回复于
  • 2

    Union 正是您要找的 . 只需合并你的两个 Select 语句 . 如果您希望在表格中找到重复的结果，并且希望所有结果都显示在输出中，请使用 Union all More info on w3schools

    回复于

相关问题