select col1, col2, col3 from tbName union all select col1, col4, col5 from tbName
create table A(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10)) insert A select 1, 'A', 'B', 'a1', 'b1'select col1, col2, col3 from A union all select col1, col4, col5 from A--result col1 col2 col3 ----------- ---------- ---------- 1 A B 1 a1 b1(2 row(s) affected)
--错了, 改改select col1, col2, col4 from A union all select col1, col3, col5 from A--result col1 col2 col4 ----------- ---------- ---------- 1 A a1 1 B b1(2 row(s) affected)
明白了,谢谢!忘记说了,还要加一条判断 col1 col2 col3 col4 col5 1 A B a1 b1col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1)否则跟col5(b1)如何写sql
create table A(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10)) insert A select 1, 'A', 'B', 'a1', 'b1' insert A select 1, 'B', 'A', 'a2', 'b2'select col1, col2, [col3]=case when col2='A' then col4 else col5 end from A union all select col1, col3, [col3]=case when col3='B' then col5 else col4 end from A--result col1 col2 col3 ----------- ---------- ---------- 1 A a1 1 B b2 1 B b1 1 A a2(4 row(s) affected)
不好意思case 后面只能有一个字段,可俺有多个col1 col2 col3 col4 col5 col6 col7 1 A B a1 b1 a2 b2col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1),col6(a2) 否则跟col5(b1),col7(b2)如何写sql不骂俺这个问题解决了马上结
create table T(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10), col6 varchar(10), col7 varchar(10))insert T select 1, 'A', 'B', 'a1', 'b1', 'a2', 'b2' insert T select 1, 'B', 'A', 'a3', 'b3', 'a4', 'b4'select col1, col2, col2=case col2 when 'A' then col4+' '+col6 when 'B' then col5+' '+col7 else '' end from T union all select col1, col3, col3=case col2 when 'B' then col4+' '+col6 when 'A' then col5+' '+col7 else '' end from T--result col1 col2 col2 ----------- ---------- --------------------- 1 A a1 a2 1 B b3 b4 1 B b1 b2 1 A a3 a4(4 row(s) affected)
不是要合在一起,不好意思case 后面只能有一个字段,可俺有多个col1 col2 col3 col4 col5 col6 col7 1 A B a1 b1 a2 b2col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1),col6(a2) 否则跟col5(b1),col7(b2)如何写sql 例:col1 col2 col4 col6 1 A a1 a2 1 B b1 b2
create table T(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10), col6 varchar(10), col7 varchar(10))insert T select 1, 'A', 'B', 'a1', 'b1', 'a2', 'b2' insert T select 1, 'B', 'A', 'a3', 'b3', 'a4', 'b4'select col1, col2, col4=case col2 when 'A' then col4 when 'B' then col5 else '' end, col6=case col2 when 'A' then col6 when 'B' then col7 else '' end from T union all select col1, col3, col4=case col3 when 'A' then col4 when 'B' then col5 else '' end, col6=case col3 when 'A' then col6 when 'B' then col7 else '' end from T--retul col1 col2 col4 col6 ----------- ---------- ---------- ---------- 1 A a1 a2 1 B b3 b4 1 B b1 b2 1 A a3 a4(4 row(s) affected)
union all
select col1, col4, col5 from tbName
insert A select 1, 'A', 'B', 'a1', 'b1'select col1, col2, col3 from A
union all
select col1, col4, col5 from A--result
col1 col2 col3
----------- ---------- ----------
1 A B
1 a1 b1(2 row(s) affected)
union all
select col1, col3, col5 from A--result
col1 col2 col4
----------- ---------- ----------
1 A a1
1 B b1(2 row(s) affected)
col1 col2 col3 col4 col5
1 A B a1 b1col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1)否则跟col5(b1)如何写sql
create table A(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10))
insert A select 1, 'A', 'B', 'a1', 'b1'
insert A select 1, 'B', 'A', 'a2', 'b2'select col1, col2, [col3]=case when col2='A' then col4 else col5 end from A
union all
select col1, col3, [col3]=case when col3='B' then col5 else col4 end from A--result
col1 col2 col3
----------- ---------- ----------
1 A a1
1 B b2
1 B b1
1 A a2(4 row(s) affected)
1 A B a1 b1 a2 b2col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1),col6(a2) 否则跟col5(b1),col7(b2)如何写sql不骂俺这个问题解决了马上结
create table T(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10),
col5 varchar(10), col6 varchar(10), col7 varchar(10))insert T select 1, 'A', 'B', 'a1', 'b1', 'a2', 'b2'
insert T select 1, 'B', 'A', 'a3', 'b3', 'a4', 'b4'select col1, col2,
col2=case col2 when 'A' then col4+' '+col6 when 'B' then col5+' '+col7 else '' end from T
union all
select col1, col3,
col3=case col2 when 'B' then col4+' '+col6 when 'A' then col5+' '+col7 else '' end from T--result
col1 col2 col2
----------- ---------- ---------------------
1 A a1 a2
1 B b3 b4
1 B b1 b2
1 A a3 a4(4 row(s) affected)
1 A B a1 b1 a2 b2col2有时为A 有时为B(和col3互换)当他为A时后面跟col4(a1),col6(a2) 否则跟col5(b1),col7(b2)如何写sql 例:col1 col2 col4 col6
1 A a1 a2
1 B b1 b2
col5 varchar(10), col6 varchar(10), col7 varchar(10))insert T select 1, 'A', 'B', 'a1', 'b1', 'a2', 'b2'
insert T select 1, 'B', 'A', 'a3', 'b3', 'a4', 'b4'select col1, col2,
col4=case col2 when 'A' then col4 when 'B' then col5 else '' end,
col6=case col2 when 'A' then col6 when 'B' then col7 else '' end from T
union all
select col1, col3,
col4=case col3 when 'A' then col4 when 'B' then col5 else '' end,
col6=case col3 when 'A' then col6 when 'B' then col7 else '' end from T--retul
col1 col2 col4 col6
----------- ---------- ---------- ----------
1 A a1 a2
1 B b3 b4
1 B b1 b2
1 A a3 a4(4 row(s) affected)
诲人不倦!
忠心感谢!