联合两个表，其中一个 where 子句 [英] Union on two tables with a where clause in the one

问题描述

目前我有 2 个表，这两个表具有相同的结构并且将在 Web 应用程序中使用.两个表是生产和临时.临时表包含一个名为 [signed up] 的附加列.目前我使用在每个表中找到的两列(recno 和 name)生成一个列表.使用这两个字段，我能够支持我的 Web 应用程序搜索功能.现在我需要做的是支持限制可以在第二个表的搜索中使用的项目数量.这样做的原因是，一旦一个人注册"，就会在生产表中创建一个类似的记录，并将拥有自己的 recno.

Currently I have 2 tables, both of the tables have the same structure and are going to be used in a web application. the two tables are production and temp. The temp table contains one additional column called [signed up]. Currently I generate a single list using two columns that are found in each table (recno and name). Using these two fields I'm able to support my web application search function. Now what I need to do is support limiting the amount of items that can be used in the search on the second table. the reason for this is become once a person is "signed up" a similar record is created in the production table and will have its own recno.

正在做:

Select recno, name
  from production
UNION ALL
Select recno, name
  from temp

...将向我展示每个人.我试过了:

...will show me everyone. I have tried:

Select recno, name
  from production
UNION ALL
Select recno, name
  from temp
 WHERE signup <> 'Y'

但这什么都不返回?有人可以帮忙吗?

But this returns nothing? Can anyone help?

推荐答案

不确定我是否完全理解您想要什么.如果您从临时表注册后在生产表中创建记录，并且您只需要尚未注册的人......您根本不需要查看生产表.简单地说:

Not sure if I'm understanding what you want exactly. If you create records in the production table once they have signed up from the temp table, and you only want people who haven't signed up...you don't need to look in the production table at all. Simply:

SELECT recno, name FROM temp WHERE signup='N'

或者您试图限制搜索.如果由于某种原因您确实需要联合但您试图消除重复项，则必须修改您的语句以删除 ALL 子句.Union ALL 会导致您获得重复项.如果您不想要重复的值，则不想在 UNION 中使用 ALL.您可以在此处阅读Unions.

Or however you're trying to limit your search. If for some reason you do need a union but you're trying to eliminate duplicates you'd have to modify your statement to remove the ALL clause. Union ALL causes you to get duplicates. If you don't want duplicate values, you want to not use ALL in your UNION. You can read up on Unions here.

这篇关于联合两个表，其中一个 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！