【www.bbyears.com--php与数据库】
union all 这个指令的目的也是要将两个 sql 语句的结果合并在一起。 union all 和 union 不同之处在于 union all 会将每一笔符合条件的资料都列出来,无论资料值有无重复。
union all 的语法如下:
[sql 语句 1]
union all
[sql 语句 2]
我们用和上一页同样的例子来显示出 union all 和 union 的不同。同样假设我们有以下两个表格,
1> create table employee(
2> id int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> go
1>
2> insert into employee (id, name, salary, start_date, city, region)
3> values (1, "jason", 40420, "02/01/94", "new york", "w")
4> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (2, "robert",14420, "01/02/95", "vancouver","n")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (3, "celia", 24020, "12/03/96", "toronto", "w")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (4, "linda", 40620, "11/04/97", "new york", "n")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (5, "david", 80026, "10/05/98", "vancouver","w")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (6, "james", 70060, "09/06/99", "toronto", "n")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (7, "alison",90620, "08/07/00", "new york", "w")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (8, "chris", 26020, "07/08/01", "vancouver","n")
3> go(1 rows affected)
1> insert into employee (id, name, salary, start_date, city, region)
2> values (9, "mary", 60020, "06/09/02", "toronto", "w")
3> go(1 rows affected)
1>
2> select * from employee
3> go
id name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 jason 40420 1994-02-01 00:00:00.000 new york w
2 robert 14420 1995-01-02 00:00:00.000 vancouver n
3 celia 24020 1996-12-03 00:00:00.000 toronto w
4 linda 40620 1997-11-04 00:00:00.000 new york n
5 david 80026 1998-10-05 00:00:00.000 vancouver w
6 james 70060 1999-09-06 00:00:00.000 toronto n
7 alison 90620 2000-08-07 00:00:00.000 new york w
8 chris 26020 2001-07-08 00:00:00.000 vancouver n
9 mary 60020 2002-06-09 00:00:00.000 toronto w(9 rows affected)
1>
2>
3> select id, name from employee where name like "c%"
4> union all
5> select id, name from employee where name like "a%"
6> go
id name
----------- ----------
3 celia
8 chris
7 alison(3 rows affected)
1>
2> drop table employee
3> go
1>