表一
ID NAME VALUE FLAG
001 A 200 0
001 A 300 1
002 B 400 1
002 B 200 0
003 C 300 0
得到表2
ID NAME 0 1
001 A 200 300
002 B 200 400
003 C 300 0
查询语句怎么写啊
ID NAME VALUE FLAG
001 A 200 0
001 A 300 1
002 B 400 1
002 B 200 0
003 C 300 0
得到表2
ID NAME 0 1
001 A 200 300
002 B 200 400
003 C 300 0
查询语句怎么写啊
WHEN FLAG='1' THEN VALUE END FROM TABLE_NAME;没环境手写的,不知道对不对,你试试看。思路是用case when 进行处理。
或者用decode 也行》。。
decode(flag,1,value) as 1
from tb_name
select id,name,
case when flag='0' then value
case when flag='1' then value
from tb_name
2 id varchar2(10),
3 name varchar2(10),
4 value number(18,0),
5 flag number(1,0)
6 );表已创建。scott@TBWORA>
scott@TBWORA> CREATE TABLE tb2(
2 id varchar2(10),
3 name varchar2(10),
4 flag_0 number(18,0),
5 flag_1 number(18,0)
6 );表已创建。scott@TBWORA>
scott@TBWORA> INSERT INTO tb1(id,name,value,flag) values('001', 'A', 200, 0);已创建 1 行。scott@TBWORA> INSERT INTO tb1(id,name,value,flag) values('001', 'A', 300, 1);已创建 1 行。scott@TBWORA> INSERT INTO tb1(id,name,value,flag) values('002', 'B', 400, 1);已创建 1 行。scott@TBWORA> INSERT INTO tb1(id,name,value,flag) values('002', 'B', 200, 0);已创建 1 行。scott@TBWORA> INSERT INTO tb1(id,name,value,flag) values('003', 'C', 300, 0);已创建 1 行。scott@TBWORA> COMMIT;提交完成。scott@TBWORA>
scott@TBWORA> -- 查询语句:
scott@TBWORA> with t1 as (select tb1.id, tb1.name, tb1.value from tb1 where tb1.flag=0),
2 t2 as (select tb1.id, tb1.name, tb1.value from tb1 where tb1.flag=1)
3 SELECT nvl(t1.id,t2.id) as id, nvl(t1.name,t2.name) as name,
4 nvl(t1.value,0) as flag_0,
5 nvl(t2.value,0) as flag_1
6 FROM t1 full join t2 on t1.id=t2.id;ID NAME FLAG_0 FLAG_1
-------------------- -------------------- ---------- ----------
001 A 200 300
002 B 200 400
003 C 300 0scott@TBWORA>
scott@TBWORA> -- 插入语句:
scott@TBWORA> INSERT INTO tb2(id,name,flag_0, flag_1)
2 with t1 as (select tb1.id, tb1.name, tb1.value from tb1 where tb1.flag=0),
3 t2 as (select tb1.id, tb1.name, tb1.value from tb1 where tb1.flag=1)
4 SELECT nvl(t1.id,t2.id) as id, nvl(t1.name,t2.name) as name,
5 nvl(t1.value,0) as flag_0,
6 nvl(t2.value,0) as flag_1
7 FROM t1 full join t2 on t1.id=t2.id;已创建3行。
scott@TBWORA> SELECT tb1.id, tb1.name,
2 sum(decode(flag,0,value,0)) as flag_0,
3 sum(decode(flag,1,value,0)) as flag_1
4 from tb1
5 group by tb1.id, tb1.name;ID NAME FLAG_0 FLAG_1
-------------------- -------------------- ---------- ----------
002 B 200 400
001 A 200 300
003 C 300 0
scott@TBWORA> SELECT tb1.id, tb1.name,
2 sum(decode(flag,0,value,0)) as flag_0,
3 sum(decode(flag,1,value,0)) as flag_1
4 from tb1
5 group by tb1.id, tb1.name
6 order by tb1.id;ID NAME FLAG_0 FLAG_1
-------------------- -------------------- ---------- ----------
001 A 200 300
002 B 200 400
003 C 300 0
NAME,
SUM(CASE
WHEN FLAG = '0' THEN
VALUE
ELSE
0
END) AS "0",
SUM(CASE
WHEN FLAG = '1' THEN
VALUE
ELSE
0
END) AS "1"
FROM T1
GROUP BY ID, NAME
ORDER BY 1;