在结果中带有额外字段的SQL UNION查询 [英] SQL UNION Query with Extra Field in Results

问题描述

我有一个运行良好的SQL查询，除了我希望将第一个数据库中的另一列的结果带到该数据库中.这是我目前拥有的:

I have a SQL query that is working well except I would like to bring the results of another column in the first db with it. This is what I currently have:

SELECT parts1.PART_NBR, parts1.NIIN
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

这是我基本上想要的(当然这是行不通的):

This is what I basically want (of course this wont work):

SELECT parts1.PART_NBR, parts1.NIIN, parts1.ANOTHER_FIELD
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

我该如何编写查询以使其执行相同的操作，但实际上会带回结果中的多余字段?

How do I write the query so that it does the same thing but actually brings back the extra field in the results?

推荐答案

创建一个空字段，返回NULL

Create an empty field, returning NULL

SELECT parts1.PART_NBR, parts1.NIIN, parts1.ANOTHER_FIELD
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION ALL
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN, NULL AS ANOTHER_FIELD
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

从您的评论中，您看到的结果为

From your comment you are seeing the results as

Part Number: 21223 NIIN: 008194914 Name: CAPACITOR
Part Number: 21223 NIIN: 011241926 Name: HEAT SINK
Part Number: 21223 NIIN: 003901600 Name: KNIFE
Part Number: 21223 NIIN: 003901600 Name: 
Part Number: 21223 NIIN: 008194914 Name:
Part Number: 21223 NIIN: 011241926 Name:

第一个结果来自表parts1，第二个结果来自parts2，空白的Name字段是您返回NULL的位置.

The first results are coming from the table parts1 the second from parts2, the blank Name fields are where you are returning NULL.

根据您提供的信息，我看不到您为什么使用UNION从这两个表中获取结果，因为它们似乎包含相同的信息，除了第一个表还具有Name字段.

From the information you have given I don't see why you are using a UNION to get the results from these two tables as they seem to contain the same information except the first table also has the Name field.

JOIN零件/参考编号上的表格来选择名称会更好吗?

Would it not be better to JOIN the tables on the Part/Reference number in order to select the name?

正如您在评论中所说，以前使用UNION会得到DISTINCT结果集.使用NULL字段，行不再唯一，查询将返回所有行.

As you said in your comment, previously you were getting a DISTINCT result set because of using UNION. With the NULL field the rows are no longer unique and the query returns all the rows.

我在评论中说，我看不到当前的UNION语句正在为您做什么，因为似乎两个表都具有相同的信息.除了您告诉我们的内容之外，此查询还有其他内容吗?

I said in a comment that I do not see what the current UNION statement is doing for you as it seems the same information is both tables. Is there more to this query than what you have told us?

这篇关于在结果中带有额外字段的SQL UNION查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！