jc_dreaming
- 浏览: 1882957 次
- 性别:
- 来自: 合肥
-
文章分类
- 全部博客 (514)
- OPEN (41)
- WARN (33)
- EXPER (16)
- RESOURCE (7)
- TOOL (4)
- DWR (10)
- Struts1.x (10)
- Ibtais (18)
- MyEclipse (30)
- Sql Server (64)
- Tomcat (7)
- APACHE (4)
- JSP (18)
- SERVLET (6)
- ENGLISH (0)
- ECSide (8)
- JasperReports (7)
- JAVA (24)
- JS (42)
- XML (26)
- CVS (8)
- Mind (1)
- JQUERY (2)
- IBATIS (6)
- PROJECT (0)
- STRUTS2 (0)
- PROXOOL (0)
- SPRING (4)
- Hibernate (0)
- SSI (0)
- JBPM (11)
- FLEX (3)
- JSON (2)
- GWT (1)
- jeecms v3 (1)
- Flash (2)
- DATA (1)
- ORACLE (3)
- 查询oracle 中逗号分隔字符串中所有值 (1)
最新评论
-
小小西芹菜：
GoEasy web三步轻松实现web实时推送1. 引入goe ...
服务器推送技术 java -
kg_1997：
这个方法太棒了，可以不用to_date函数，实在是棒！！！
java/oracle日期处理 -
wodesunday：
:idea:
SQL的分段统计查询语句 -
wodesunday：
引用
SQL的分段统计查询语句 -
BlueSkator：
讲的有点浅，没有深入进去
tomcat需要的重新发布和重启服务器的几种情况
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[getBanJianTongJibaobiao1](
@distictId INT,
@deId INT,
@startdate VARCHAR(25),
@dateEnd VARCHAR(25)
)
AS
------------------------------------------------------------------------
-- SQL 存储过程 办件汇总表
-- @distictId 区号
-- deptId 部门号
-- windowNo 窗口号
-- @startdate 开始时间
-- @startdate 结束时间
-- typeContext 办件状态名称
-- shouJian 收件数量
-- banJian 办件数量
------------------------------------------------------------------------
BEGIN
-- 声明变量
DECLARE @typeContext_TMP VARCHAR(50);
DECLARE @shouJian_TMP bigint ;
DECLARE @banJian_TMP bigint ;
DECLARE @ishouJian_TMP bigint ;
DECLARE @ibanJian_TMP bigint ;
DECLARE @ideptId bigint ;
DECLARE @at_end INT ;
DECLARE @Id1 bigint;
DECLARE @sbJian_TMP bigint ;
select @shouJian_TMP=0,@banJian_TMP=0,@ishouJian_TMP=0,@ibanJian_TMP=0,@ideptId=0,@at_end=0,@sbJian_TMP=0
--声明异常
--DECLARE not_found CONDITION FOR SQLSTATE '02000';
--定义临时表空间
CREATE TABLE #SESSION_TEMP
(
casecontext varchar(50),
shoujian int,
banjian int
)
--1、求办件状态值
DECLARE cursor1 CURSOR FOR
select dictValue,dicId from T_DICTIONAY
where dictTypeId=73 ;
--DECLARE cursor2 CURSOR FOR
--select casecontext,shoujian,banjian from #SESSION_TEMP;
--DECLARE CONTINUE HANDLER FOR not_found
--SET @at_end = 1;
--找出所属部门下的所有子部门包括自己
open cursor1;
--open cursor2;
fetch next from cursor1 into @typeContext_TMP,@Id1;
--WHILE @at_end=0
WHILE @@FETCH_STATUS = 0
BEGIN
set @shouJian_TMP=0;
set @banJian_TMP=0;
set @ishouJian_TMP=0;
set @ibanJian_TMP=0;
if @distictId=0 --全部区
begin
if @deId=0 --全部部门
begin
--收件数
select @shouJian_TMP=isnull(sum(caseNumber),0) from t_caseBaseInfo
where convert(varchar(10),caseStartDate,121)>=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),caseStartDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121) and caseTypeId=@Id1;
--办件数
select @banJian_TMP=isnull(sum(caseNumber),0) from t_caseBaseInfo a
where convert(varchar(10),a.casePromiseDate,121)>=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),a.casePromiseDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121)
and a.caseTypeId=@Id1 and a.caseStatusId in(141,142,143,199) ;--已办结，已退回，已作废，已发证
end
end
else --某个区
begin
if @deId=0 --所有部门
begin
select @shouJian_TMP=isnull(sum(caseNumber),0) from t_caseBaseInfo a
where convert(varchar(10),caseStartDate,121)>=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),caseStartDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121)
and caseTypeId=@Id1
and a.caseDeptId in(select DEPTID from t_department where DISTID=@distictId and enable='启用' and ifreport='是');
select @banJian_TMP=isnull(sum(caseNumber),0) from t_caseBaseInfo a
where convert(varchar(10),a.casePromiseDate,121)>=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),a.casePromiseDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121)
and a.caseTypeId=@Id1 and a.caseStatusId in(141,142,143,199) --已办结，已退回，已作废，已发证
and a.caseDeptId in(select DEPTID from t_department where DISTID=@distictId and enable='启用' and ifreport='是');
end
else --某个区某个部门
begin
DECLARE R_CRSR2 CURSOR FOR
with t(i)as(select deptid from t_department where deptid=@deId and enable='启用' and ifreport='是'
union all
select d.deptid from t_department d,t
where t.i=d.parentid and d.enable='启用' and d.ifreport='是')
select i from t;
OPEN R_CRSR2;
FETCH next from R_CRSR2 INTO @ideptId;
--WHILE @at_end=0
WHILE @@FETCH_STATUS = 0
begin
--set @shouJian_TMP=0;
--set @banJian_TMP=0;
set @ishouJian_TMP=0;
set @ibanJian_TMP=0;
set @sbJian_TMP=0;
set @ishouJian_TMP=(select isnull(sum(caseNumber),0) from t_caseBaseInfo
where convert(varchar(10),caseStartDate,121)>=convert(varchar(10),cast(@startdate as datetime),121)
and convert(varchar(10),caseStartDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121)
and caseTypeId=@Id1 and CASEDEPTID=@ideptId);
set @shouJian_TMP=@ishouJian_TMP+@shouJian_TMP;
/*
Select @jibanjian1 = isNull(Sum(CASENUMBER),0)
from t_casebaseinfo a
where a.CASETYPEID = 151
and a.CASEDEPTID = @DeNo
and a.CASESTATUSID in (141,143,199)
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
*/
set @ibanJian_TMP=(select isnull(sum(caseNumber),0) from t_caseBaseInfo a
where convert(varchar(10),a.casePromiseDate,121)>=convert(varchar(10),cast(@startdate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121)<=convert(varchar(10),cast(@dateEnd as datetime),121)
and a.caseStatusId in(141,142,143,199) --已办结，142已退回，已作废，已发证
and a.caseTypeId=@Id1 and CASEDEPTID=@ideptId) ;
set @banJian_TMP=@ibanJian_TMP+@banJian_TMP;
FETCH next from R_CRSR2 INTO @ideptId;
end;
CLOSE R_CRSR2
deallocate R_CRSR2
end
end
insert into #SESSION_TEMP values(@typeContext_TMP,@shouJian_TMP,@banJian_TMP)
--set @at_end=0;
fetch next from cursor1 into @typeContext_TMP,@Id1;
END
CLOSE cursor1
deallocate cursor1
--add 咨询件统计
set @typeContext_TMP = '咨询件';
if @distictId=0 --全部区
begin
if @deId=0 --全部部门
begin
select @shouJian_TMP=count(*),@banJian_TMP=count(*) from t_sendno,T_ADVISORY where sendno=advicaseno
and convert(varchar(10),senddate,121) >=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),senddate,121) <= convert(varchar(10),cast(@dateEnd as datetime),121)
end
end
else --某个区
begin
if @deId=0 --所有部门
begin
select @shouJian_TMP=count(*),@banJian_TMP=count(*) from t_sendno,T_ADVISORY where sendno=advicaseno
and convert(varchar(10),senddate,121) >=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),senddate,121) <= convert(varchar(10),cast(@dateEnd as datetime),121)
and senddeptid in(select deptid from t_department where distid = @distictId)
end
else --某个区某个部门
begin
select @shouJian_TMP=count(*),@banJian_TMP=count(*) from t_sendno,T_ADVISORY where sendno=advicaseno
and convert(varchar(10),senddate,121) >=convert(varchar(10),cast(@startdate as datetime),121) and convert(varchar(10),senddate,121) <= convert(varchar(10),cast(@dateEnd as datetime),121)
and senddeptid in(@deId);
end
end
insert into #SESSION_TEMP values(@typeContext_TMP,@shouJian_TMP,@banJian_TMP)
SELECT casecontext,shoujian,banjian FROM #SESSION_TEMP;
DROP table #SESSION_TEMP;
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--drop PROCEDURE getAllBanJianTongJiData
ALTER PROCEDURE [dbo].[getAllBanJianTongJiData]
@p_district_no int,
@StartDate datetime,
@EndDate datetime
AS
set nocount on
Declare @Name char(50)
Declare @jibanjian bigint
Declare @jibanjian1 bigint
Declare @tuihuijian bigint
Declare @bubanjian1 bigint
Declare @bubanjian2 bigint
Declare @chengnuojian1 bigint
Declare @chengnuojian2 bigint
Declare @chengnuojian3 bigint
Declare @chengnuojian4 bigint
Declare @lianbanjian1 bigint
Declare @lianbanjian2 bigint
Declare @shangbaojian1 bigint
Declare @shangbaojian2 bigint
Declare @DeNo bigint
Declare @sumbj bigint
Declare @sumbj1 bigint
declare @huzu1 bigint
declare @huzu2 bigint
CREATE TABLE #t (
Name char(50),
jibanjian bigint,
jibanjian1 bigint,
tuihuijian bigint,
bubanjian1 bigint,
bubanjian2 bigint,
chengnuojian1 bigint,
chengnuojian2 bigint,
chengnuojian3 bigint,
chengnuojian4 bigint,
lianbanjian1 bigint,
lianbanjian2 bigint,
shangbaojian1 bigint,
shangbaojian2 bigint,
sumbj bigint,
sumbj1 bigint,
huzu1 bigint,
huzu2 bigint
)
begin
Declare My_CURSOR CURSOR
FOR
Select DEPTID from t_department where ENABLE= '启用' and DISTID = 1 and deptType=1 and deptid not in (243,244)--and is_convenience=@tjType jc_dreaming
Order by DEPTCODE /**ONLY VALID DEPARTMENT */
open My_CURSOR
end
FETCH NEXT FROM My_CURSOR INTO @DeNo
while (@@FETCH_STATUS = 0)
BEGIN
Select @Name = DEPTNAME from t_department where DEPTID = @DeNo and deptType=1 and deptid not in (243,244)
Select @jibanjian = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 151--instance of 1
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @jibanjian1 = isNull(Sum(CASENUMBER),0)
from t_casebaseinfo a
where a.CASETYPEID = 151
and a.CASEDEPTID = @DeNo
and a.CASESTATUSID in ((141,142,143,199)
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @tuihuijian = isNull(Sum(CASENUMBER),0)
from t_casebaseinfo a
where a.CASETYPEID = 203--instance of3
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @bubanjian1 =isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 202
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @bubanjian2 =isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 202
and a.CASESTATUSID in (141,142,143,199)
and a.CASEDEPTID = @DeNo
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
---CASEPROMISEDATE is not null
Select @chengnuojian1 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID =132
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @chengnuojian2 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID =132
and a.CASESTATUSID in (141,142,143,199)
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @chengnuojian3 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a,t_CaseBusinessInfo b
where a.CASETYPEID = 132
and a.caseid=b.busiCaseId
and b.BUSIOPERATE = 3
and a.CASESTATUSID in (141,142,143,199)
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,120)>=convert(varchar(10),b.BUSIOPERATEDATE,120)
Select @chengnuojian4 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a, t_CaseBusinessInfo b
where a.CASETYPEID = 132
and a.caseid=b.busiCaseId
and b.BUSIOPERATE = 3
and a.CASESTATUSID in (141,142,143,199)
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,120)<convert(varchar(10),b.BUSIOPERATEDATE,120)
Select @lianbanjian1 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 134
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @lianbanjian2 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 134
and a.CASEDEPTID = @DeNo
and a.CASESTATUSID in(2,4)
--++++++++++++++++++++
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @shangbaojian1 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASETYPEID = 133
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @shangbaojian2 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where CASETYPEID = 133
and CASESTATUSID in (141,143,199)
and a.CASEDEPTID = @DeNo
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @sumbj = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASEDEPTID = @DeNo
and convert(varchar(10),a.casePromiseDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),a.casePromiseDate,121)<=convert(varchar(10),cast(@endDate as datetime),121)
Select @sumbj1 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASEDEPTID = @DeNo
and casestatusid in (141,142,143,199)
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast(@startDate as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @huzu1 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast('2009-10-08' as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Select @huzu2 = isNull(Sum(CASENUMBER),0) from t_casebaseinfo a
where casestatusid in (141,142,143,199)
and a.CASEDEPTID = @DeNo
and convert(varchar(10),caseStartDate,121)>= convert(varchar(10),cast('2009-10-08' as datetime),121)
and convert(varchar(10),caseStartDate,121) <=convert(varchar(10),cast(@endDate as datetime),121)
Insert Into #t (Name,jibanjian,jibanjian1,tuihuijian,bubanjian1,bubanjian2,
chengnuojian1,chengnuojian2,chengnuojian3,chengnuojian4,
lianbanjian1,lianbanjian2,shangbaojian1,shangbaojian2,sumbj,sumbj1,huzu1,huzu2)
Values(@Name,@jibanjian,@jibanjian1,@tuihuijian,@bubanjian1,@bubanjian2,
@chengnuojian1,@chengnuojian2,@chengnuojian3,@chengnuojian4,
@lianbanjian1,@lianbanjian2,@shangbaojian1,@shangbaojian2,@sumbj,@sumbj1,@huzu1,@huzu2)
FETCH NEXT FROM My_CURSOR INTO @DeNo
END
CLOSE My_CURSOR
DEALLOCATE My_CURSOR
select * from #t
分享到：
发表评论
-
ORACLE中使用递归查询 (
2012-12-27 16:35 979在数据库查询中常常会碰到要查询树形结构的数据，需要用一个字段的 ... -
函数与存储过程区别
2011-06-01 18:17 1320本质上没区别。只是 ... -
SQL Server2000安装常见问题
2011-05-25 14:15 1495一：解决安装sql时文件挂起的问题： (1)、点击“开始》运 ... -
MySQL语句优化的原则
2011-04-12 14:21 10001、使用索引来更快 ... -
转SQL-group by学习
2011-04-12 14:18 1273在select 语句中可以使用group by 子句将行划分成 ... -
SQL Select语句完整的执行顺序：
2011-04-12 14:15 1403SQL Select语句完整的执行顺序： 1、from子句组 ... -
SQL截取字符串(substring与patindex的使用)
2011-03-31 13:55 5716SQL截取字符串(substring与patinde ... -
SQL Server2005杂谈（1）：使用公用表表达式（CTE）简化嵌套SQL
2011-03-16 09:21 1316先看下面一个嵌套的查 ... -
SQL CHARINDEX (Transact-SQL)
2010-12-06 09:05 1482返回字符串中指定表达式的开始位置。 Transact-SQ ... -
SQL Server基本函数
2010-12-06 08:53 1613SQL Server基本函数 1.字符串函数 ： ... -
SQL2005级联删除,层叠,设置空,设置默认值
2010-12-01 15:46 2451浅谈SQL SERVER 2005 之引用 ... -
sql 显示插入主键
2010-10-22 16:28 1311set identity_insert [T_ADMINTYP ... -
取小数
2010-10-22 16:12 1308使用这个方式来计算 SELECT convert(decima ... -
oracle 创建表空间
2010-10-12 11:28 1536一.创建表空间 CREATE SMALLFILE TABLES ... -
sql 约束大全
2010-09-27 16:28 1369--------添加主键约束(bookid作为主键) alt ... -
sql 主外键语法
2010-09-27 08:39 1743alter table dbo.t_commAtta ... -
在SQL Server中，关于with as使用介绍
2010-09-26 13:32 36094一．WITH AS的含义 ... -
procedure 存储过程多个返回测试
2010-09-01 10:26 1190create procedure test(@temp1 va ... -
sql 实现按月份，季度统计报表
2010-08-30 12:01 14973呵呵，我们在处理设计到日期统计时候经常会按照年度，季度，月份统 ... -
SQL Server DATEPART() 不能言
2010-08-30 11:29 1828今天做按月份，按年度，按季度统计报表，后来发现了最简单最可扩展 ...
相关推荐
在SQL Server数据库管理系统中，存储过程是预编译的SQL语句集合，它们可以用于执行复杂的数据库操作，包括报表数据的生成。存储过程是数据库设计中的一个重要组成部分，它提供了提高性能、安全性和代码重用的机会。...
基于Microsoft SQL Server存储过程的院内医疗报表统计分析 本文主要介绍了基于Microsoft SQL Server存储过程的院内医疗报表统计分析方法，该方法旨在快速、准确地统计及分析医院医疗业务运行情况，提供Methodology...
在SQL中，生成统计报表，特别是包含小计与合计的部分，是常见的需求。本文将详细介绍如何利用SQL实现这一功能，主要关注"利用临时表"这一方法，因为这种方法具有逻辑清晰且执行效率高的特点，尽管在大数据量时可能会...
在数据库管理与开发领域，SQL Server作为一个广泛应用的数据库系统，提供了强大的存储过程功能，允许开发者在数据库层面执行复杂的逻辑操作。存储过程是存储在SQL Server中的预编译的SQL代码块，它可以通过调用执行...
在用友U8中，自定义报表可以使用存储过程来生成报表，而不需要编写复杂的SQL语句。存储过程可以根据用户的输入参数来生成报表，而用户可以根据需要来设置报表的条件和格式。 使用存储过程的优点是可以生成复杂的...
"用SQL语言制作多表数据环境下的统计报表" 本文主要介绍如何使用 SQL 语言制作多表数据环境下的统计报表。SQL（Structured Query Language）是一种关系数据库语言，用于管理和操作关系数据库。它可以实现对表和视图...
在SQL中，存储过程是一种预编译的SQL代码集合，它可以执行特定的任务并被多次调用，从而提高数据库操作的效率。在这个特定的场景中，我们关注的是如何使用存储过程来实现“横向显示列的数据”。这通常涉及到数据的...
基于 SQL Server 的存储过程技术研究 存储过程是 SQL Server 7.0 的重要内容之一，它是由 SQL 语句和控制流语句构成的语句集台。它能够带有参数，调用其他过程，返回状态值给调用者指示成功或失败，还能在远程 SQL ...
存储过程可以应用于各种数据库场景中，如数据查询、数据修改、数据统计、备份恢复等。存储过程可以帮助开发者和DBA更好地管理和维护数据库，提高数据库的性能和安全性。 在实际应用中，存储过程可以用于实现业务...
SQL报表的制作过程涉及多个环节，包括但不限于统计变量的确定、软件安装配置、数据源设置以及最终报表的设计与生成。 #### 二、统计变量与报表分类 在制作SQL报表之前，首先需要明确报表中需要包含哪些统计变量。...
3. 默认情况下，通过存储过程生成的报表被视为统计表，这意味着`WHERE`子句中的条件通常在存储过程中处理，同时支持使用`GROUP BY`进行数据聚合。如果创建新报表时选择了存储过程，报表类型会被隐藏并默认设置为统计...
《使用SQL和数组进行库存统计报表的设计》这篇文章深入探讨了如何利用SQL和数组技术来构建库存统计报表，以此提升企业管理效率。SQL（Structured Query Language）是数据库查询和管理的核心工具，而数组则在处理大量...
他在项目中运用了Spring、Ibatis等技术，结合Oracle的存储过程进行数据处理，如加班时长统计报表的生成，这体现了他对业务逻辑和数据库操作的综合运用能力。此外，他还熟悉Junit单元测试，确保代码质量，以及Quartz...
接下来，执行一系列的UPDATE语句通过`sp_executesql`存储过程来修改ICChatBillTitle表中的字段值，从而达到双表头的效果。 ### 注意事项 - 此方案只适用K3V11（非多语言版本）之前的所有版本。 - 修改表头后，报表...
- SQL Server提供了许多高级特性，如视图、存储过程、触发器、索引、分区等，这些都可以在泛微系统的背景下发挥重要作用。例如，视图可以简化复杂查询，存储过程可以封装常用操作提高性能，索引可以加速数据检索，...
首先，我们需要在SQL Server数据库端创建一个存储过程，该过程接受必要的参数，根据这些参数动态生成交叉报表所需的SQL查询。存储过程可以灵活处理数据行和列的组合，以及各种汇总和过滤条件，以满足不同类型的交叉...
AWR报表由多个部分组成，包括但不限于系统信息、等待事件统计、SQL统计信息等。这些信息对于评估数据库性能问题、识别瓶颈以及制定改进策略都极为关键。 #### 三、AWR报表生成机制 AWR报表的生成涉及到Oracle内部...
SQL（Structured Query Language）是用于管理和处理数据库的标准编程语言，它在报表统计中扮演着核心角色。本题所提及的SQL语句展示了如何在数据库中进行复杂的报表统计，尤其是按日期区间对特定类型的数据进行汇总...
在生成统计报表时，聚合函数是SQL查询技术的基础。常用的聚合函数有SUM（求和）、COUNT（汇总）、AVG（平均值）等。例如，使用COUNT函数可以统计满足条件的记录数，使用AVG函数可以计算某列的平均值。此外，GROUP BY...
Oracle 存储过程是一种可以在 Oracle 数据库中创建和执行的程序单元，它可以完成多种操作，如数据处理、数据报表、数据统计等。下面是 Oracle 存储过程的基本语法。 创建存储过程 CREATE OR REPLACE PROCEDURE ...