SELECT MAX(CASE 字段名2 WHEN 0 THEN 字段名1 ELSE '' END) AS 字段1 MAX(CASE 字段名2 WHEN 1 THEN 字段名1 ELSE '' END) AS 字段2 FROM TABLENAME
掉了个逗号, SELECT MAX(CASE 字段名2 WHEN 0 THEN 字段名1 ELSE '' END) AS 字段1, MAX(CASE 字段名2 WHEN 1 THEN 字段名1 ELSE '' END) AS 字段2 FROM TABLENAME
create table t ( t1 varchar(10), t2 int , )insert into t select 'a1',0 insert into t select 'a2',0 insert into t select 'a3',0 insert into t select 'b1',1 insert into t select 'b2',1 select t1,22 as t2 ,identity(int,1,1 ) id into #t1 from t where t2= 0 select t1,22 as t2 ,identity(int,1,1 ) id into #t2 from t where t2= 1select a.t1 ,b.t1 from #t1 a left join #t2 b on a.id =b.id t1 t1 ---------- ---------- a1 b1 a2 b2 a3 NULL(所影响的行数为 3 行)
重复帖?create table tb ( 字段名1 char(2), 字段名2 int ) insert into tb(字段名1,字段名2) values('a1',0) insert into tb(字段名1,字段名2) values('a2',0) insert into tb(字段名1,字段名2) values('a3',0) insert into tb(字段名1,字段名2) values('b1',1) insert into tb(字段名1,字段名2) values('b2',1) goselect id=identity(int,1,1) , 字段名1 into test1 from tb where 字段名2 = 0 select id=identity(int,1,1) , 字段名1 into test2 from tb where 字段名2 = 1select test1.字段名1 as A , test2.字段名1 as B from test1 left join test2 on test1.id = test2.iddrop table tb drop table test1 drop table test2A B ---- ---- a1 b1 a2 b2 a3 NULL(所影响的行数为 3 行)
用一个sql不行吗,还要定义这个 select id=identity(int,1,1) , 字段名1 into test1 from tb where 字段名2 = 0 select id=identity(int,1,1) , 字段名1 into test2 from tb where 字段名2 = 1 多个sql,我想要一个sql的,因为写在程序里
MAX(CASE 字段名2 WHEN 1 THEN 字段名1 ELSE '' END) AS 字段2
FROM TABLENAME
SELECT MAX(CASE 字段名2 WHEN 0 THEN 字段名1 ELSE '' END) AS 字段1,
MAX(CASE 字段名2 WHEN 1 THEN 字段名1 ELSE '' END) AS 字段2
FROM TABLENAME
(
t1 varchar(10),
t2 int ,
)insert into t select 'a1',0
insert into t select 'a2',0
insert into t select 'a3',0
insert into t select 'b1',1
insert into t select 'b2',1
select t1,22 as t2 ,identity(int,1,1 ) id into #t1 from t where t2= 0
select t1,22 as t2 ,identity(int,1,1 ) id into #t2 from t where t2= 1select a.t1 ,b.t1
from #t1 a left join #t2 b on a.id =b.id t1 t1
---------- ----------
a1 b1
a2 b2
a3 NULL(所影响的行数为 3 行)
(
字段名1 char(2),
字段名2 int
)
insert into tb(字段名1,字段名2) values('a1',0)
insert into tb(字段名1,字段名2) values('a2',0)
insert into tb(字段名1,字段名2) values('a3',0)
insert into tb(字段名1,字段名2) values('b1',1)
insert into tb(字段名1,字段名2) values('b2',1)
goselect id=identity(int,1,1) , 字段名1 into test1 from tb where 字段名2 = 0
select id=identity(int,1,1) , 字段名1 into test2 from tb where 字段名2 = 1select test1.字段名1 as A , test2.字段名1 as B
from test1 left join test2 on test1.id = test2.iddrop table tb
drop table test1
drop table test2A B
---- ----
a1 b1
a2 b2
a3 NULL(所影响的行数为 3 行)
select id=identity(int,1,1) , 字段名1 into test1 from tb where 字段名2 = 0
select id=identity(int,1,1) , 字段名1 into test2 from tb where 字段名2 = 1
多个sql,我想要一个sql的,因为写在程序里
a1
a2
a3
b1
b2
null我想要的是这样的,能有别的办法吗,谢谢
a1 b1
a2 b2
a3 null