㈠ 如何生成指定sql語句的AWR報表
1，生成指定SQL語句的統計報表
如果希望對多實例的資料庫做對比，那就要使用$Oracle_HOME/rdbms/admin/awrddrpi.sql腳本了。該腳本的操作基本與單實例基本相同，這里不再演示，感興趣的朋友可以自己測試下。
2 ，生成指定SQL語句的統計報表
這項統計專門用來分析某條指定的SQL語句，通過awrsqrpt.sql腳本，awr能夠生成指定sql(曾經執行過的SQL)的執行計劃，消耗的資源等等信息，有助於DBA進行SQL調優。
[oracle@even ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction on Sun Dec 23 11:39:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Proction
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procere successfully completed.
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2100083002 TEST 1 test
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2100083002 1 TEST test even.oracle.
com
Using 2100083002 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
test TEST 34 23 Dec 2012 11:00 1
35 23 Dec 2012 11:30 1
36 23 Dec 2012 11:40 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 34
Begin Snapshot Id specified: 34
Enter value for end_snap: 36
End Snapshot Id specified: 36
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
注意,這里要指定分析的SQL_ID，你可能想問，要分析的SQL ID從何而來呢？一般來說，SQL_ID可以通過V$SQL(及其它相關視圖)，要麼是通過AWR/STATSPACK等工具。
Enter value for sql_id: 4vsh055snc3
SQL ID specified: 4vsh055snc3
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_34_36.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awr_20121231.html
Using the report name /home/oracle/awr_20121231.html
<HTML><HEAD><TITLE>AWR SQL Report</TITLE><style type="text/css">body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black;
......
......
.....
</BODY></HTML>
Report written to /home/oracle/awr_20121231.html
㈡ 用sql 實現報表查詢
select sales.a,sales.b - inbuy.b - cost1.b - cost2.b
from
(select date as a,sum(銷售額) as b from 銷售表 where date between 開始日期 and 結束日期 group by date ) as sales,
(select date as a,sum(進貨額) as b from 進貨表 where date between 開始日期 and 結束日期 group by date) as inbuy,
(select date as a,sum(支出額) as b from 企業支出費用表 where date between 開始日期 and 結束日期 group by date) as cost1,
(select date as a,sum(費用額) as b from 其他費用表 where date between 開始日期 and 結束日期 group by date) as cost2
where
sales.a = inbuy.a and
sales.a = cost1.a and
sales.a = cost2.a
㈢ 用sql語句做報表，怎麼算折扣和工資
--mssqlserver:
selecta.日期,a.物料,a.工序,a.設備,a.班組,a.操作工,a.數量,a.單價,
(
casewhenb.物料種類數>1orb.物料種類數=1andc.設備種類數>1andd.工序種類數=1then0.8else1end
)as折扣,
a.數量*a.單價*(
casewhenb.物料種類數>1orb.物料種類數=1andd.工序種類數=1andc.設備種類數>1then0.8else1end
)as工資
from表名aleftjoin
(select日期,count(物料)as物料種類數,操作工from表名groupby日期,操作工)b
ona.日期=b.日期anda.操作工=b.操作工
leftjoin
(select日期,count(設備)as設備種類數,操作工from表名groupby日期,操作工)c
ona.日期=c.日期anda.操作工=c.操作工
leftjoin
(select日期,count(工序)as工序種類數,操作工from表名groupby日期,操作工)d
ona.日期=d.日期anda.操作工=d.操作工
㈣ SQL語句製作報表
t1,t2,t3對應表123
列名用了拼音首字母代替，自己看下吧
SELECT isnull(t1.GG,isnull(t2.gg,t3.gg)) 規格,
isnull(SR,0) 收入,isnull(FC,0) 發出,isnull(jc,0) 結存
FROM
t1 full join t2
on t1.gg=t2.gg
full join t3
on t1.gg = t3.gg
㈤ 用MSsql語句寫一個報表
[查看方式]為'99'時只顯示貨號為99開頭的商品
-- select p.* from sys_procts p where p.proct_code like '99%';
[查看方式]為'非99'時只顯示貨號為非99開頭的商品
-- select p.* from sys_procts p where p.proct_code not like '99%';
[查看方式]為'全部'亂卜櫻時則顯示所有品種
-- select p.* from sys_procts p;
如果是傳參的方式，以弊棚${@mothod_code}代表傳入的參數嘩叢』99『、』非99『，』全部『，可寫成如下方式：
select p.* from sys_procts p
where
(${@mothod_code} = '99' and p.proct_code like '99%')
or
(${@mothod_code} = '非99' and p.proct_code not like '99%')
or
(${@mothod_code} = '全部') ;
㈥ 請問用SQL如何編制以下報表
SQL Server 2008及以上的話可以這樣：
--測試數據
with表A(倉庫,物料代碼,物料名稱,規格型號,單位,單價,收入數量,收入金額)as(
select'原料倉','01.01','原料1','注塑','PCS',2,100,200unionall
select'原料倉','01.02','原料2','','PCS',3,100,300unionall
select'原料倉','01.03','原料3','噴油','PCS',4,100,400unionall
select'原料倉','01.15','原料6','','PCS',1.2,1000,1200unionall
select'成品倉','02.00001','等級品測試A','','PCS',23.37,2,46.74unionall
select'成品倉','02.00004','等級品測試B','','PCS',46.34,2,92.67unionall
select'成品倉','02.03','成品3','','PCS',16.54,30,496.16unionall
select'成品倉','02.04','成品4','','PCS',2.13,200,426.15unionall
select'成品倉','02.06','替代測試0911','','PCS',0.4,100,40
)
--按倉庫分類匯總，並與原始數據一起展示
select倉庫,物料代碼,物料名稱,規格型號,單位,單價,收入數量,收入金額from(
select0asordercode,*from表A
unionall
selectgrouping_id(倉庫),isnull(倉庫+'(小計)','合計'),null,null,null,null,null,sum(收入數量),sum(收入金額)from表A
groupbycube(倉庫)
)t
orderbyordercode,倉庫
結果：
㈦ Access資料庫做報表，請教SQL語句如何寫：
SELECT 交易類別, Sum(IIf(Month(交易時間)=1,收入,0)) AS 一月, Sum(IIf(Month(交易時間)=2,收入,0)) AS 二月, Sum(IIf(Month(交易時間)=3,收入,0)) AS 三月, Sum(IIf(Month(交易時間)=4,收入,0)) AS 四月, Sum(IIf(Month(交易時間)=5,收入,0)) AS 五月, Sum(IIf(Month(交易時間)=6,收入,0)) AS 六月, Sum(IIf(Month(交易時間)=7,收入,0)) AS 七月, Sum(IIf(Month(交易時間)=8,收入,0)) AS 八月, Sum(IIf(Month(交易時間)=9,收入,0)) AS 九月, Sum(IIf(Month(交易時間)=10,收入,0)) AS 十月, Sum(IIf(Month(交易時間)=11,收入,0)) AS 十一月, Sum(IIf(Month(交易時間)=12,收入,0)) AS 十二月
FROM 支付表
WHERE (((Year([交易時間]))=[請輸入年份]))
GROUP BY 交易類別;