1. oracle 利用 分組求和一張報表的 欄位 金額 sql
select decode(t.f_disqua_level,'1','A類','2','B類','3','C類','4','D類','') as 等級, t.f_process as 發現地點, t.f_discovery_time as 發現時間, t.f_description as 不合格描述, b.f_tydept as 責任部門, b.f_assess_multiple as 考核倍數, b.f_assess_sum as 金額（元）, c.sum_count as 部門合計
from t_quality_dis_process t,t_quality_dis_tydept b,
(select a.f_tydept,
sum(a.f_assess_sum) OVER(PARTITION BY a.f_assess_sum) sum_count
from t_quality_dis_tydept a
group by a.f_tydept) c
where t.f_status=4
and b.f_disqua_id=t.f_id
and to_char(t.f_discovery_time,'yyyymm') between '201503' and '201503'
and c.f_tydept = b.f_tydept
order by t.f_discovery_time
2. ORACLE PL/SQL問題解決
終於做完了，很辛苦的，多給點分哈～～
Q1:
COL DEPTNO FORM 999
COL DNAME FORM A15
SELECT NVL(A.DEPTNO,100) DEPTNO,NVL(B.DNAME,'SUM') DNAME,A.人數,A."SUM"
FROM
(select deptno,count(ename) 人數,SUM(SAL) "SUM" from emp group by rollup(deptno)) A
LEFT JOIN DEPT B
ON A.DEPTNO=B.DEPTNO
ORDER BY 1
;
DEPTNO DNAME 人數 SUM
------ --------------- ---------- ----------
10 ACCOUNTING 3 8750
20 RESEARCH 5 10875
30 SALES 6 9400
100 SUM 14 29025
Q2:
SQL> CREATE SEQUENCE SEQ_TAB1;
序列已創建。
SQL> CREATE OR REPLACE TRIGGER TRG_TAB1
2 BEFORE INSERT ON TABLE1
3 FOR EACH ROW
4 BEGIN
5 SELECT SEQ_TAB1.NEXTVAL INTO :NEW.ID FROM DUAL;
6 END;
7 /
觸發器已創建
SQL> INSERT INTO TABLE1(NAME,ADDRESS) VALUES('AAA','ABC');
已創建 1 行。
SQL> COL NAME FORM A10
SQL> COL ADDRESS FORM A10
SQL> SELECT * FROM TABLE1;
ID NAME ADDRESS
---------- ---------- ----------
1 AAA ABC
SQL> CREATE TABLE TABLE2(ID NUMBER,
2 CATEGORY CHAR(1) CHECK(CATEGORY IN ('A','B','C')),
3 FRIEND VARCHAR2(50),
4 FOREIGN KEY(ID) REFERENCES TABLE1(ID));
表已創建。
SQL> INSERT INTO TABLE2 VALUES(2,'A','BB');
INSERT INTO TABLE2 VALUES(2,'A','BB')
*
第 1 行出現錯誤:
ORA-02291: 違反完整約束條件 (SCOTT.SYS_C005786) - 未找到父項關鍵字
SQL> INSERT INTO TABLE2 VALUES(1,'A','BB');
已創建 1 行。
SQL> INSERT INTO TABLE2 VALUES(1,'D','CC');
INSERT INTO TABLE2 VALUES(1,'D','CC')
*
第 1 行出現錯誤:
ORA-02290: 違反檢查約束條件 (SCOTT.SYS_C005785)
SQL> INSERT INTO TABLE2 VALUES(1,'B','CC');
已創建 1 行。
SQL> COL FRIEND FORM A10
SQL> SELECT * FROM TABLE2;
ID CA FRIEND
---------- -- ----------
1 A BB
1 B CC
SQL> CREATE VIEW V_FRIENDS AS
2 SELECT A.NAME,B.CATEGORY,B."COUNT"
3 FROM TABLE1 A
4 INNER JOIN (SELECT ID,CATEGORY,COUNT(DISTINCT FRIEND) "COUNT"
5 FROM TABLE2
6 GROUP BY ID,CATEGORY) B
7 ON A.ID=B.ID
8 /
視圖已創建。
SQL> SELECT * FROM V_FRIENDS;
NAME CA COUNT
---------- -- ----------
AAA A 1
AAA B 1
Q3：可修改中文輸出形式
CREATE OR REPLACE FUNCTION FUNC_CHN_NUM(V_NUM NUMBER)
RETURN VARCHAR2
IS
V_FLAG NUMBER:=0;
V_OUT VARCHAR2(10);
V_OUTSTR VARCHAR2(200);
V_LEN NUMBER;
BEGIN
SELECT LENGTH(V_NUM) INTO V_LEN FROM DUAL;
IF V_LEN>12
THEN RETURN '超出選值范圍！';--到千億
ELSE
WHILE V_FLAG<V_LEN
LOOP
-- SUBSTR(V_NUM,LENGTH(V_NUM)-V_FLAG,1) into v_out FROM DUAL;
SELECT DECODE(SUBSTR(V_NUM,LENGTH(V_NUM)-V_FLAG,1),0,'零',1,'壹',2,'貳',3,'叄',4,'肆',5,'伍',6,'陸',7,'柒',8,'捌',9,'玖') INTO V_OUT FROM DUAL;
IF MOD(V_FLAG,4)=1 THEN V_OUT:=V_OUT||'拾'; END IF;
IF MOD(V_FLAG,4)=2 THEN V_OUT:=V_OUT||'佰'; END IF;
IF MOD(V_FLAG,4)=3 THEN V_OUT:=V_OUT||'仟'; END IF;
IF V_FLAG=0 THEN V_OUT:=V_OUT||'個'; END IF;
IF V_FLAG=4 THEN V_OUT:=V_OUT||'萬'; END IF;
IF V_FLAG=8 THEN V_OUT:=V_OUT||'億'; END IF;
V_OUTSTR:=V_OUT||V_OUTSTR;
V_FLAG:=V_FLAG+1;
End LOOP;
V_OUTSTR:=REPLACE(V_OUTSTR,'零拾','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零佰','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零仟','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'零萬','萬');
V_OUTSTR:=REPLACE(V_OUTSTR,'零億','億');
V_OUTSTR:=REPLACE(V_OUTSTR,'億萬','億');
V_OUTSTR:=REPLACE(V_OUTSTR,'零零','零');
V_OUTSTR:=REPLACE(V_OUTSTR,'壹拾','拾');
V_OUTSTR:=REPLACE(V_OUTSTR,'零個','個');
V_OUTSTR:=REPLACE(V_OUTSTR,'個','');
RETURN V_OUTSTR;
END IF;
END;
測試：
SQL> SELECT FUNC_CHN_NUM(03100000780) FROM DUAL;
FUNC_CHN_NUM(03100000780)
------------------------------------------------
叄拾壹億零柒佰捌拾
SQL> SELECT FUNC_CHN_NUM(12345678910123) FROM DUAL;
FUNC_CHN_NUM(12345678910123)
---------------------------------------------------
超出選值范圍！
SQL> SELECT FUNC_CHN_NUM(300000000008) FROM DUAL;
FUNC_CHN_NUM(300000000008)
-------------------------------------------------
叄仟億零捌
SQL> SELECT FUNC_CHN_NUM(300007000008) FROM DUAL;
FUNC_CHN_NUM(300007000008)
-------------------------------------------------
叄仟億零柒佰萬零捌
SQL> SELECT FUNC_CHN_NUM(300007000018) FROM DUAL;
FUNC_CHN_NUM(300007000018)
-------------------------------------------------
叄仟億零柒佰萬零拾捌
SQL> SELECT FUNC_CHN_NUM(300007000010) FROM DUAL;
FUNC_CHN_NUM(300007000010)
-------------------------------------------------
叄仟億零柒佰萬零拾
SQL> SELECT FUNC_CHN_NUM(300007000100) FROM DUAL;
FUNC_CHN_NUM(300007000100)
-------------------------------------------------
叄仟億零柒佰萬零壹佰
SQL> SELECT FUNC_CHN_NUM(300007001000) FROM DUAL;
FUNC_CHN_NUM(300007001000)
-------------------------------------------------
叄仟億零柒佰萬壹仟
Q4:
SQL> CREATE TABLE TEST(日期 DATE, 收入 NUMBER(8),支出 NUMBER(8));
表已創建。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/1','YYYY/MM/DD'),50,30);
已創建 1 行。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/2','YYYY/MM/DD'),45,60);
已創建 1 行。
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/5','YYYY/MM/DD'),60,10);
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,收入,支出 FROM TEST;
日期 收入 支出
---------- ---------- ----------
2000/03/01 50 30
2000/03/02 45 60
2000/03/05 60 10
Q4-1:
SQL> SELECT A.日期,收入,支出,SUM(A.余額) OVER(ORDER BY A.日期) 余額
2 FROM
3 (SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,
4 SUM(收入) 收入,
5 SUM(支出) 支出,
6 SUM(收入)-SUM(支出) 余額
7 FROM TEST
8 GROUP BY TO_CHAR(日期,'YYYY/MM/DD')
9 ) A;
日期 收入 支出 余額
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/05 60 20 55
Q4-2:
SQL> INSERT INTO TEST VALUES(TO_DATE('2000/3/5','YYYY/MM/DD'),60,10);
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT A.日期,收入,支出,SUM(A.余額) OVER(ORDER BY A.日期) 余額
2 FROM
3 (SELECT TO_CHAR(日期,'YYYY/MM/DD') 日期,
4 SUM(收入) 收入,
5 SUM(支出) 支出,
6 SUM(收入)-SUM(支出) 余額
7 FROM TEST
8 GROUP BY TO_CHAR(日期,'YYYY/MM/DD')
9 ) A;
日期 收入 支出 余額
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/05 120 20 105
Q4-3:
SQL> SELECT
2 TO_CHAR(B.日期,'YYYY/MM/DD') 日期,
3 NVL(A.收入,0) 收入,
4 NVL(A.支出,0) 支出,
5 SUM(A.余額) OVER(ORDER BY B.日期) 余額
6 FROM
7 (
8 SELECT 日期,
9 SUM(收入) 收入,
10 SUM(支出) 支出,
11 SUM(收入)-SUM(支出) 余額
12 FROM TEST
13 GROUP BY 日期
14 ) A
15 RIGHT JOIN
16 (
17 SELECT (SELECT MIN(日期) FROM TEST)+ROWNUM-1 日期
18 FROM DUAL
19 CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期) FROM TEST)
20 ) B
21 ON A.日期=B.日期;
日期 收入 支出 余額
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/03 0 0 5
2000/03/04 0 0 5
--如果要看5號的余額，修改CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期) FROM TEST)成CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期)+1 FROM TEST)
SQL> SELECT
2 TO_CHAR(B.日期,'YYYY/MM/DD') 日期,
3 NVL(A.收入,0) 收入,
4 NVL(A.支出,0) 支出,
5 SUM(A.余額) OVER(ORDER BY B.日期) 余額
6 FROM
7 (
8 SELECT 日期,
9 SUM(收入) 收入,
10 SUM(支出) 支出,
11 SUM(收入)-SUM(支出) 余額
12 FROM TEST
13 GROUP BY 日期
14 ) A
15 RIGHT JOIN
16 (
17 SELECT (SELECT MIN(日期) FROM TEST)+ROWNUM-1 日期
18 FROM DUAL
19 CONNECT BY ROWNUM<(SELECT MAX(日期)-MIN(日期)+1 FROM TEST)
20 ) B
21 ON A.日期=B.日期;
日期 收入 支出 余額
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/03 0 0 5
2000/03/04 0 0 5
2000/03/05 120 20 105
3. ORACLE建表SQL
每個欄位後面必須得跟欄位的大小。
例如：create table student( name varchar2(20), age number(4), sal number(4), comm number(4), job varchar(4));
不然的話提示錯誤為：缺失左括弧。
希望能夠幫你。
4. 關於Oracle中的SQL生成統計表
select a.CostRegisterNo 編號,a.TopicNo 項目號,sum(nvl(a.bjf,0)) 編校費,sum(nvl(a.ysf,0)) 印刷費,sum(nvl(a.zbf,0)) 製版費
from
(select CostRegisterNo,
TopicNo,
sum(case when CostName='編校費' then CostAmt end) bjf,
sum(case when CostName='印刷費' then CostAmt end) ysf,
sum(case when CostName='製版費' then CostAmt end) zbf
from t group by CostRegisterNo,
TopicNo) a
group by a.CostRegisterNo ,a.TopicNo ;
---補充---
select a.CostRegisterNo 編號,a.TopicNo 項目號,
sum(nvl(a.bjf,0)) 編校費,sum(nvl(a.ysf,0)) 印刷費,sum(nvl(a.zbf,0)) 製版費
from
(select CostRegisterNo,
TopicNo,
sum(decode(CostName,'編校費', CostAmt)) bjf,
sum(decode(CostName,'印刷費', CostAmt)) ysf,
sum(decode(CostName,'製版費', CostAmt)) zbf
from t group by CostRegisterNo,
TopicNo) a
group by a.CostRegisterNo ,a.TopicNo ;
表名我定義的是t，你可以換一下
這里用了個nvl函數，這個主要的意思就是將查詢出來的結果為空的轉成0
5. 查詢oracle資料庫所有表sql語句怎麼寫
查詢系統表:select * from all_tables where table_name not in (select table_name from user_tables);
查詢用戶表:select * from user_tables;
6. oracle中如何通過已有的表生成相關sql語句
最簡單的方法，通過第三方工具plsql。
步驟：
1、登錄表所在資料庫及用戶。
7. 怎麼用sql語句查看oracle資料庫里的所有表
可以從表user_tables中查詢。
select
sum(num_rows)
from
user_tables
;
就是顯示用戶所有表的行數其中num_rows是每個表的行數，用sum加總一下，就是當前用戶所有表的總行數。
但因為user_tables中的數據不是實時統計，會有時間差，所以以上sql統計得到的結果與實際會存在一定誤差。