Union on two tables with a where clause in the one

问题:

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.

doing:

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?

回答1:

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'

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.

回答2:

For what you are asking, you could do it this style.

SELECT * FROM
(
    SELECT '1' as `col`
    UNION 
    SELECT '2' as `col`
) as `someAlias`
where `someAlias`.`col` = '1'

Put the entire union inside parenthesis, give it an alias, then give the condition.