简单的SQL代码逃避了我.. Unionize两个不匹配的表

I'm selecting 1 field from 1 table and storing it into a temp table.

我从1个表中选择1个字段并将其存储到临时表中。

Sometimes that table ends up with 0 rows.

有时该表最终会有0行。

I want to add that field onto another table that has 20+ fields

我想将该字段添加到另一个包含20多个字段的表中

Regular union won't work for me because of the field # mismatch. Outer wont work for me because there is nothing to compare. NVL doesn't work on the first temp table.

由于字段#matmatch不合适,常规联合对我不起作用。外面不适合我,因为没有什么比较。 NVL不适用于第一个临时表。

Anyone know how to do it?

谁知道怎么做?

UPDATED:

I failed to mention.... When the table that retrieves 1 field finds a match in other cases, this code that I'm using now works....

我没有提到....当检索1字段的表在其他情况下找到匹配时,我现在使用的这段代码现在可用....

SELECT DISTINCT reqhead_rec.resp_name<br>
FROM reqhead_rec, biglist<br>
WHERE reqhead_rec.req_no = biglist.req_no
 AND reqhead_rec.frm = biglist.req_frm<br>
INTO TEMP grabname with no log;

SELECT biglist.*, grabname.resp_name<br>
FROM biglist, grabname<br>
ORDER BY prnt_item, account_amt<br>
INTO TEMP xxx with no log;

3 个解决方案

#1

What field would it match with? BTW, here's how to line them up:

它会匹配哪个领域?顺便说一下,这是如何排列它们:

SELECT NULL, NULL, NULL, NULL, MySingleField, NULL, NULL, NULL... FROM #temp
UNION ALL
SELECT Col1, Col2, Col3, Col4, Col5, Col6,... FROM OtherTable

UPDATE:

OK, after reading your update... I don't think you want a UNION at all, but rather, and incredibly simple SUBSELECT

好了,看完你的更新后...我觉得你根本不想要一个UNION,而是非常简单的SUBSELECT

SELECT
    *,
    (SELECT TOP 1 Name FROM Blah WHERE Blah.SomeID = MyTable.SomeID) AS ExtraCol
FROM
    MyTable

#2

It sounds like you do want a join, not a union.

听起来你确实想要一个联盟,而不是一个联盟。

You don't need to compare anything to do a join. You end up with a cross product if you specify no join condition:

您无需比较任何内容即可进行连接。如果未指定连接条件,则最终会得到交叉产品:

SELECT t20.*, t1.*
FROM table_with_20_columns AS t20
  LEFT OUTER JOIN temp_table_with_1_column AS t1 ON (1=1);

When there are zero rows in the temp table, it'll be reported as NULL in the result of the above query.

当临时表中有零行时,它将在上述查询的结果中报告为NULL。

However, if there are multiple rows in the temp table, you'll get the cross product with the first table. I can't tell from your question what you want.

但是,如果临时表中有多行,您将获得第一个表的交叉产品。我无法从你的问题中看出你想要什么。

edit: The join condition expressed in the ON or USING clause should be optional according to the SQL standard, but at least as I test it in MySQL 5.0, it's a syntax error to omit that clause. But you can use ON (1=1).

编辑:根据SQL标准,ON或USING子句中表示的连接条件应该是可选的,但至少在MySQL 5.0中测试它时,省略该子句是语法错误。但你可以使用ON(1 = 1)。

edit: Answering your question in the comment:

编辑:在评论中回答您的问题:

SELECT COALESCE(reqhead_rec.resp_name, dflt.resp_name) AS resp_name
FROM (SELECT 'default name' AS resp_name) dflt
  LEFT OUTER JOIN reqhead_rec ON (1=1)
WHERE reqhead_rec.req_no = biglist.req_no AND reqhead_rec.frm = biglist.req_frm 
INTO TEMP grabname WITH NO LOG;

Actually, you may be able to skip the temp table altogether. Just LEFT JOIN your main table to reahead_rec. Put those conditions into the ON clause of the join, not in the WHERE clause. Then use COALESCE() in the select-list of that query to give a default name when one is not found in the other table.

实际上,您可以完全跳过临时表。只需将主表左键连接到reahead_rec即可。将这些条件放入连接的ON子句中,而不是WHERE子句中。然后在该查询的选择列表中使用COALESCE(),以便在另一个表中找不到一个时提供默认名称。

SELECT b.*, COALESCE(r.resp_name, 'default name') AS resp_name
FROM biglist AS b
  LEFT OUTER JOIN reqhead_rec AS r
    ON (b.req_no = r.req_no AND r.frm = b.req_frm)
INTO TEMP xxx WITH NO LOG;

#3

Try selecting nvl(NULL,NULL) for missing values

尝试为缺失值选择nvl(NULL,NULL)

#1