select distinct(zoneno) zon ,'a1' type from tbname
union
select distinct(zoneno) zon ,'a2' type from tbname;
union
select distinct(zoneno) zon ,'a2' type from tbname;
解决方案 »
- 问个group by的问题
- 求一句SQL语句应该怎么写
- oracle行专列
- 想认识几个编程序的高手~~
- 那有可以用于ADO的oracle驱动可以下载?
- 在线急等!!!!oracle存储过程怎么一直提示Parameter 'v_UserID' is declared but never used in 'forums_PrivateMessages_Get'???
- 怎么写这样一个sql,有什么技巧吗?
- 问个有关同义词的问题?
- 关于数据备份!高手指导啊,急!!!!!!!!
- oracle 服务的一个问题(急)
- 关于database link 的问题,请高手指教。
- 为什么在pl/sql develop中调试存过速度会那么慢呢?
实现:如:
create or replace procedure T_INSERT as
I_NAME VARCHAR2(100);
CURSOR C_CONS IS
SELECT zoneno ,COUNT(*) FROM TAB_NAME GROUP BY zoneno HAVING COUNT(*) < 3;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO T_NAME,T_CONS_NAME;
EXIT WHEN C_CONS%NOTFOUND;
INSERT INTO zoneno ......
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
when NO_DATA_FOUND then
NULL;
end;
/
表中
zoneno type value
1202 A1 2
1203 A2 4
1204 A1 1
1204 A2 3怎样变到
1202 A1 2
1202 A2 0
1203 A1 0
1203 A2 4
1204 A1 1
1204 A2 3
如果本来没有的,value都为0,这样的话怎么做呢???
---------
10
20
30
40
50
60
100
110
180已选择9行。SQL> select distinct (time) time,'a1' type from test
2 union
3 select distinct (time) time,'a2' type from test; TIME TY
--------- --
10 a1
10 a2
20 a1
20 a2
30 a1
30 a2
40 a1
40 a2
50 a1
50 a2
60 a1
60 a2
100 a1
100 a2
110 a1
110 a2
180 a1
180 a2已选择18行。SQL>
( select distinct(zoneno) zon ,'a1' type from tbname
union
select distinct(zoneno) zon ,'a2' type from tbname) t
where (t.zon,type) not in (select zoneno,type from tbname)
union
select zoneno,type,value from tbname;