现在有30条SQL语句:
select A from B where C = "1"
select A from B where C = "2"
select A from B where C = "3"
select A from B where C = "4"
....select A from B where C = "30"一条一条执行起来,数据库比较费时间。现在想合并成一条SQL语句,一下子查询到30条数据。空的话,就返回个空的结果集。
就像
A A A A ... A
10 10 14 ... 20
select A from B where C = "1"
select A from B where C = "2"
select A from B where C = "3"
select A from B where C = "4"
....select A from B where C = "30"一条一条执行起来,数据库比较费时间。现在想合并成一条SQL语句,一下子查询到30条数据。空的话,就返回个空的结果集。
就像
A A A A ... A
10 10 14 ... 20
解决方案 »
- 一个sql或存储过程的问题。
- 在命令行中如何创建存储过程
- 在客户端用 SQL*PLUS WORKWHEET登陆数据库服务器提示错误,请高手指点.
- 如何通过 dbms_job 生成 “每周 2,3,4,5,6 凌晨 1~4点,每半小时执行 SP:test_SP ”的任务 ?
- PL SQL 的SQL窗口无法执行存储过程
- oracle 11g怎么设定job执行存储过程,存储过程有输入参数
- 合计问题,急呀
- 怎样向过程传递SQL语句?
- 出错
- pl/sql beautifier,提示错误,求告诉
- oracle 中怎样执行自定义函数(函数中有修改数据库的操作)
- 请教一份er模型请问这叫什么哇。。我究竟了好几天了。各种资料找不到。。
sleect A from B where C <=30 --C什么类型?--这样不行吗?
unoin
select A from B where C = '2'.....
decode(C,'2',A,null),
decode(C,'3',A,null),
...
from B
SELECT DECODE(C, '1', A,
'2', A,
'3', A,
......,
NULL) AS 'A'
FROM B;
如果是一条
select decode(C,'1',A,null),
decode(C,'2',A,null),
decode(C,'3',A,null),
...
from B
where C <= 30 这样呢?
union不可以
select A from B where C = "1"
union
select A from B where C = "2"如果C= "2"这条记录不存在,那么只返回一个结果集,我需要的是2个结果集。
不对的,这样一来,假设B里面只有1条数据,那么按照你这个SQL,岂不是只抽出了一条数据?
其他的29条空的结果集我也需要啊。
如果A字段的数据类型是数值:SELECT SUM(DECODE(C,'1',A,0)),
SUM(DECODE(C,'2',A,0)),
SUM(DECODE(C,'3',A,0)),
SUM(DECODE(C,'4',A,0)),
SUM(DECODE(C,'5',A,0)),
......
SUM(DECODE(C,'30',A,0))
FROM B;
如果A字段的数据类型是字符串:SELECT WM_CONCAT(DECODE(C,'1',A,'')),
WM_CONCAT(DECODE(C,'2',A,'')),
WM_CONCAT(DECODE(C,'3',A,'')),
WM_CONCAT(DECODE(C,'4',A,'')),
WM_CONCAT(DECODE(C,'5',A,'')),
......
WM_CONCAT(DECODE(C,'30',A,''))
FROM B;
SQL> WITH b AS (
2 SELECT 'a1' a, 1 c FROM DUAL UNION ALL
3 SELECT 'a2' a, 1 c FROM DUAL UNION ALL
4 SELECT 'a1' a, 2 c FROM DUAL UNION ALL
5 SELECT 'a1' a, 3 c FROM DUAL
6 ),c_tb AS (
7 SELECT LEVEL c FROM DUAL CONNECT BY LEVEL <= 30
8 )
9 SELECT t1.c,t2.a FROM c_tb t1
10 LEFT JOIN b t2 ON t1.c = t2.c
11 ORDER BY t1.c
12 ; C A
---------- --
1 a1
1 a2
2 a1
3 a1
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 C A
---------- --
20
21
22
23
24
25
26
27
28
29
30 31 rows selected