SQL UNION -是否可以在第二个SELECT中使用第一个SELECT的结果

时间:2021-07-02 15:46:24

I am connecting to an SQL server with multiple tables,

我正在连接一个有多个表的SQL服务器,

As an example lets assume there are 2 tables:

例如,假设有两个表:

  1. a table (names) linking ids to products names e.g. 5, apple,12,eggs
  2. 一个表(名称)将id与产品名称连接起来,例如:5、苹果、12、鸡蛋。
  3. a table (prices) linking product names to prices e.g. apple, $4, eggs, $5,oranges, $12
  4. 将产品名称与价格联系在一起的表格,例如:苹果,4美元,鸡蛋,5美元,橙子,12美元

I would like to write a select command that can link this data together, something along the lines of:

我想写一个select命令,可以将这些数据连接在一起,类似于:

SELECT id, name 
FROM names 
UNION 
SELECT price 
FROM prices where name = **name from the first bit**

The returned result would be something like:

返回的结果如下:

5, apple, $4
12, eggs, $5

Oranges wouldn't be returned as it wasn't in the names table.

橘子不会被归还,因为它不在名字表中。

For a bit of background: My initial plan was to first get the names then iterate through them making a new query for every name to get the price.

对于一些背景知识:我最初的计划是首先获取名称,然后迭代它们,为每个名称创建一个新的查询,以获得价格。

However this is going to be implemented with C# and Visual Studio won't allow a second query to be opened if there is currently on on the same connection

然而,这将用c#实现,Visual Studio不会允许在当前正在进行相同连接的情况下打开第二个查询。

Changing to an approach similar to what I outlined above seems like a better way to achieve what I want (less queries, tidier, etc.) but other suggestions are welcome

改变一种类似于我上面概述的方法似乎是实现我想要的更好的方法(更少的查询,更整洁,等等),但是其他的建议是受欢迎的

2 个解决方案

#1


3  

This is a JOIN, not a UNION

这是联合,不是联合

SELECT n1.id, n1.name, p1.price
FROM names n1
INNER JOIN prices p1
ON n1.name = p1.name

#2


1  

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. And you need INNER JOIN to combine records from two (or more) tables:

UNION将两个或多个查询的结果合并到一个结果集中,该结果集包含所有属于UNION中的所有查询的行。您需要内部连接来合并来自两个(或多个)表的记录:

SELECT  n.id, 
        n.name, 
        p.price 
FROM names n
INNER JOIN prices p
    ON n.name = p.name --or id

#1


3  

This is a JOIN, not a UNION

这是联合,不是联合

SELECT n1.id, n1.name, p1.price
FROM names n1
INNER JOIN prices p1
ON n1.name = p1.name

#2


1  

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. And you need INNER JOIN to combine records from two (or more) tables:

UNION将两个或多个查询的结果合并到一个结果集中,该结果集包含所有属于UNION中的所有查询的行。您需要内部连接来合并来自两个(或多个)表的记录:

SELECT  n.id, 
        n.name, 
        p.price 
FROM names n
INNER JOIN prices p
    ON n.name = p.name --or id