㈠ 如何生成指定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 交易类别;