目前,我希望将两个SQL查询合并为一个。这有点类似于SQL: Taking the result of of query and using it another - combine。假设有两个查询:
SQL语句
1.) SELECT *
FROM (SELECT B.example1
FROM EXAMPLE1A A
INNER JOIN EXAMPLE1B B ON A.ID = B.ID
WHERE A.ABC ='ABC'
ORDER BY A.ORDER_BY ) as colstate
2.) SELECT colstate
FROM EXAMPLE_TABLE
WHERE EFG LIKE '%'
AND BGTHAN >= '1'
AND SMTHAN <= '100'
ORDER BY ORDER_BY ASC我想使用查询1中的结果。)作为查询2中的colstate (列语句)。但是:
我尝试过的是:
SELECT (SELECT B.example1
FROM EXAMPLE1A A
INNER JOIN EXAMPLE1B B
ON A.ID = B.ID
WHERE A.ABC ='ABC'
ORDER BY A.ORDER_BY )
FROM EXAMPLE_TABLE
WHERE EFG LIKE '%'
AND BGTHAN >= '1'
AND SMTHAN <= '100'
ORDER BY ORDER_BY ASC结果是错误:标量子查询只允许返回一行,我应该如何将"=“替换为"IN"?还是我的说法完全错了?
发布于 2013-11-07 08:09:30
“将两个查询合并为一个”--这不是一个好的规范。试着找出你到底想要什么作为一个平面的二维表,把嵌套的SELECT想象成嵌套的循环,里面的循环只能为父行设置一个值。如下所示:
[Outer loop - parent row]
[Inner loop - children rows]
// all you can do here is change a single parent's field to anything
// like constant/sum/avg/topmost/ugly-subquery-returning-a-single-result
[/Inner loop]
[/Outer loop]发布于 2013-11-07 07:48:12
错误指出,您使用的查询作为列语句最多只能返回一行。
它可能应该是这样的:
SELECT (SELECT B.example1
FROM EXAMPLE1A A
INNER JOIN EXAMPLE1B B
ON A.ID = B.ID
WHERE A.ABC ='ABC'
AND A.SOME_COLUMN = E.SOMECOLUMN // retrieve only relevant data for this row
ORDER BY A.ORDER_BY )
FROM EXAMPLE_TABLE E
WHERE EFG LIKE '%'
AND BGTHAN >= '1'
AND SMTHAN <= '100'
ORDER BY ORDER_BY ASChttps://stackoverflow.com/questions/19830371
复制相似问题