如何在单个表上执行 UNION? [英] How to do a UNION on a single table?

问题描述

我只需要显示居住在Peters"或Crows"大道的所有客户的姓名、地址和出生日期.

I need to display the name and surname and address and DOB for all customers who reside in 'Peters' or 'Crows' avenue only.

这很好，我是这样做的:

This is fine I did it like so:

SELECT Customers.FirstName, Customers.Surname,
       Customers.CustomerAddress, Customers.DOB
FROM Customers
WHERE 
(  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%' 
or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%')

但后来我读得更用力了，它说:

but then I read a bit harder and it said:

使用 UNION 查询生成结果.

Use a UNION query to produce the results.

所以我阅读了一些关于 UNION 的内容，但大多数情况下我看到来自两个 SELECT 查询的返回值必须具有相同的长度，通常示例使用 2 个不同的表?

So I read up a bit on UNIONs, but mostly I see that the returned values from both SELECT queries must be of the same length, and normally examples are using 2 different tables?

所以我需要在同一张桌子上执行一个 UNION 这样所有在他们的地址中带有 Peters 和 Crows 字样的客户都是显示.我试过了:

So I need to perform a UNION on the same table such the all the customers with the words Peters and Crows in their address are shown. I tried:

SELECT Customers.CustomerAddress
FROM Customers
WHERE 
(  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%'
or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%')
UNION
SELECT Customers.FirstName, Customers.Surname,
       Customers.CustomerAddress, Customers.DOB
FROM Customers

但我收到错误:

所有使用 UNION、INTERSECT 或 EXCEPT 运算符组合的查询都必须在它们的目标列表中有相同数量的表达式.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

这是可以理解的，因为我的第一个 SELECT 只返回 3 个结果(即我正在寻找的结果)，而另一个返回所有地址(包括我需要的结果).

which is understandable because my first SELECT only returns 3 results (i.e the results I'm looking for) while the other returns all the addressed (including the ones I need).

所以我的确切问题是，我如何在同一张表(客户总共 10 条记录)上执行 UNION 以便所有带有 Peters 和 Crows 显示在他们的地址中?(其中 3 条记录符合条件，其他 7 条不符合)

So my exact problem is, How do I do I perform a UNION on the same table (Customers total of 10 records) so that all the customers with the words Peters and Crows in their address are shown? (3 of the records match the condition the other 7 dont)

推荐答案

 SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress
 FROM Customers
 WHERE Customers.CustomerAddress LIKE '%'+ 'Main' + '%'  
 UNION
 SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress
 FROM Customers
 WHERE Customers.CustomerAddress LIKE '%'+ 'Gladys'+ '%'

在联合中，两个或多个查询在 SELECT 语句中应始终具有相同数量的字段.WHERE 子句似乎是您联合查询中的问题.

In a union, the two or more queries should always have the same number of fields in the SELECT statement. The WHERE clause seemed to be the problem in your union query.

这篇关于如何在单个表上执行 UNION?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！