select a from table1 union select b from table1 union select c from table1
SELECT A FROM TB UNION SELECT B FROM TB UNION SELECT C FROM TB
select a as result from table1 where a is not null union select b from table1 where b is not null union select c from table1 where c is not null;
SELECT * FROM (SELECT A FROM TB UNION SELECT B FROM TB UNION SELECT C FROM TB)AS T WHERE A IS NOT NULL
select a from table1 union select b from table1 union select c from table1
union 去除重复 union ALL 不去除重复的记录
我使用了union 与 is not null 可结果是: 01 2 3还是有空行
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-25 14:02:05 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[table1] if object_id('[table1]') is not null drop table [table1] go create table [table1]([A] int,[B] int,[C] int) insert [table1] select 1,0,2 union all select 2,null,null union all select 3,null,0 --------------开始查询-------------------------- select a as result from table1 where a is not null union select b from table1 where b is not null union select c from table1 where c is not null ----------------结果---------------------------- /* result ----------- 0 1 2 3(4 行受影响) */
SQL code SELECT * FROM (SELECT A FROM TB UNION SELECT B FROM TB UNION SELECT C FROM TB)AS T WHERE A IS NOT NULL。 这种写法可行...
drop table table1; create table table1(a int, b int, c int);insert into table1(a,b,c) select 1,0,2 union all select 2,null,null union all select 3, null,0;select * from table1; ---正如3楼小梁的,UNION 之前排除空值,其速度可能比多嵌套一层查询要快! SELECT A FROM table1 WHERE A IS NOT NULL UNION SELECT B FROM table1 WHERE B IS NOT NULL UNION SELECT C FROM table1 WHERE C IS NOT NULL;
union select b from table1
union select c from table1
union
select b from table1 where b is not null
union
select c from table1 where c is not null;
union select b from table1
union select c from table1
union ALL 不去除重复的记录
可结果是:
01
2
3还是有空行
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-25 14:02:05
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([A] int,[B] int,[C] int)
insert [table1]
select 1,0,2 union all
select 2,null,null union all
select 3,null,0
--------------开始查询--------------------------
select a as result from table1 where a is not null
union
select b from table1 where b is not null
union
select c from table1 where c is not null
----------------结果----------------------------
/* result
-----------
0
1
2
3(4 行受影响)
*/
这种写法可行...
create table table1(a int, b int, c int);insert into table1(a,b,c)
select
1,0,2 union all select
2,null,null union all select
3, null,0;select * from table1;
---正如3楼小梁的,UNION 之前排除空值,其速度可能比多嵌套一层查询要快!
SELECT A FROM table1 WHERE A IS NOT NULL
UNION
SELECT B FROM table1 WHERE B IS NOT NULL
UNION
SELECT C FROM table1 WHERE C IS NOT NULL;