是否可以在单个SQL查询中将三个单独的选择放在一起，其中两个选择抓取第一个选择的数据？

Question

如果我措辞最初的问题，希望这个描述清除了它。我有一个类似选择的SQL查询，所以

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster
on master.column = notmaster.column2
where master.number = 55555
and notmaster.column3 in (~~~DATA I'M MISSING AND NEED~~~)

现在我的问题是我需要在此SQL查询期间获取一个整数列表（查询底部缺少的数据IM丢失），我相信我需要两个选择来除了初始选择之外，请执行此操作。我还有另外两个我需要使用的表格，首先我需要使用ID（ID可以是初始查询中的55555）才能从第一表中获取一个值，

select secondValue as identifier from THEDB.THETABLE foo
where foo.id = 55555

然后使用该值，我们只是使用该值得到，我将在第二个表中寻找一场比赛，然后从类似的行中拿起一个匹配的行中获取一个值。

select valuesNeeded as listofvalues from THEOTHERDB.TABLE bar
where bar.value = identifier

我们抓取的名为ListOfValues的值将是第一个选择中我需要的整数列表，因此，一旦被抓住，我就可以在部分过程中使用该值。有没有办法在严格的SQL和一个查询中进行此操作？我可以更改代码，但是当前对我来说，仅更改SQL查询本身要容易得多。感谢您的任何帮助/指示！

Answer 1

您可以使用with功能来创建以后可以使用的“临时”表。

这样的东西也许？

WITH identifier AS (
  select secondValue as identifier 
  from THEDB.THETABLE foo
  where foo.id = 55555
), valuelist AS (
  select valuesNeeded as listofvalues 
  from THEOTHERDB.TABLE bar
  where bar.value in (SELECT identifier FROM identifier)
)
select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster on master.column = notmaster.column2
where master.number = 55555
  and notmaster.column3 in (SELECT listofvalues FROM valuelist)

您也可以进行子查询 - 在功能上与上述相同：

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster on master.column = notmaster.column2
where master.number = 55555
  and notmaster.column3 in (
    select valuesNeeded as listofvalues 
    from THEOTHERDB.TABLE bar
    where bar.value in (
      select secondValue as identifier 
      from THEDB.THETABLE foo
      where foo.id = 55555
    )
  )

但是SQL中的最佳方法是加入

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
join DATABASETWO.table2 notmaster on master.column = notmaster.column2
join THEOTHERDB.TABLE bar on notmaster.column3 = bar.valuesNeeded
join THEDB.THETABLE foo on foo.id = 5555 AND bar.value = foo.secondValue
where master.number = 55555