SQL server 2005 报表的一个例子

最新推荐文章于 2015-01-27 19:03:52 发布

rorger

最新推荐文章于 2015-01-27 19:03:52 发布

阅读量1.8k

点赞数 1

分类专栏： DadaBase 文章标签：报表 sql server parameters insert table comments

本文链接：https://blog.csdn.net/rorger/article/details/5996226

版权

DadaBase 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

SQL server 2005 报表的一个例子

订单主表orderMaster

订单编号	OrderNo	Char(12)
客户号	customerNo	Char(9)
业务员编号	SaleNo	Char(8)
订单金额	Ordersum	Numeric
订货日期	Orderdate	Datetime
出货日期	Shipdate	Datetime
发票号码	InvoiceNo	Char(10)

订单明细表orderDetail

订单编号	OrderNo	Char(12)
商品编号	ProductNo	Char(9)
销售数量	Qty	Int
成交单价	Price	Numeric

1）为了体现每一个销售类别对销售总额的贡献，需实现一个报表：将全部的销售额根据金额大小分类，分类区间为500元。类别为0-499.99,500-999.99 … 以此类推，格式如下：

范围下限	销售金额	所占比重（％）
0	2300.00	10
500.00	5200.00	23
1000.00	5600.00	34
…	…	…

2) 编写存储过程实现上述报表。

3）通过实验1所用高级程序语言调用上述存储过程，并在相应的数据控件显示报表结果。

4)现需增加新的报表内容，给出各种范围金额的排名。

范围下限	销售金额	所占比重（％）	排名
0	2300.00	10	3
500.00	5200.00	23	2
1000.00	5600.00	34	1
…	…	…	…

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <rorger>
-- Create date: <2010-11-8>
-- Description: <1）为了体现每一个销售类别对销售总额的贡献，需实现一个报表：将全部的销售额根据金额大小分类，
--分类区间为500元。类别为0-499.99,500-999.99 … 以此类推>
-- =============================================
CREATE PROCEDURE procOrderInfo
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    --第一步，计算orderMaster中的orderSum字段
    UPDATE dbo.orderMaster
    SET dbo.orderMaster.Ordersum=temp.ordersum
    FROM dbo.orderMaster,
          (SELECT SUM(dbo.orderDetail.Price*dbo.orderDetail.Qty) 'ordersum',OrderNo
             FROM dbo.orderDetail GROUP BY OrderNo) AS temp
    WHERE dbo.orderMaster.OrderNo=temp.OrderNo

    -- Insert statements for procedure here
    --判断临时表是否存在，如果不存在则新建立,注意必须有tempdb.dbo.引导
IF (SELECT OBJECT_ID('tempdb.dbo.#temp') ) IS NOT NULL
     DROP TABLE #temp;
CREATE TABLE #temp(region NUMERIC(18,2), --区域
amount NUMERIC(18,2), --金额
   weighted INT -- 金额占总金额的百分比
   );

DECLARE @step NUMERIC   --步值
DECLARE @total NUMERIC(18,2) --总额
DECLARE @numlines INT   --行数
DECLARE @maxRegion NUMERIC    --最高金额

SET @step=500 --设置步值
SELECT @total= SUM(dbo.orderMaster.Ordersum) FROM dbo.orderMaster --计算总金额 @total
SELECT @maxRegion=   MAX(dbo.orderMaster.Ordersum) FROM dbo.orderMaster
    SET @numlines=@maxRegion/@step --计算要显示的函数 @numlines=@maxRegion/@step
    IF (@maxRegion%@step !=0)    --如果@maxRegion/@step不能整除,那么numlines 增加 1
        SET @numlines = @numlines + 1

   --下面将统计信息逐行插入到临时表
   DECLARE @i INT
   SET @i=0
   WHILE(@i <@numlines)
   BEGIN
    DECLARE @scope NUMERIC -- @scope 表示划分范围,每行增加一个@step
    DECLARE @iamount NUMERIC --@scope范围内的总额
    DECLARE @weighted INT
    SET @scope=(@i+1) * @step      --比如：起始为0, @scope 为0-499.99
    SELECT @iamount= SUM(dbo.orderMaster.Ordersum)   FROM orderMaster
        WHERE Ordersum<@scope AND Ordersum> @i*@step --计算某个范围内的总金额
    SELECT @weighted=@iamount * 100 /@total; --计算所占的比重

INSERT INTO #temp
         ( region, amount, weighted )
VALUES ( @i * @step, -- region - numeric
           @iamount, -- amount - numeric
           @weighted -- weighted - int
           )
SET @i = @i +1
   END --while end

SELECT * FROM #temp
END
GO

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <rorger>
-- Create date: <2010-11-8>
-- Description: <1）为了体现每一个销售类别对销售总额的贡献，需实现一个报表：将全部的销售额根据金额大小分类，
--分类区间为500元。类别为0-499.99,500-999.99 … 以此类推现需增加新的报表内容，给出各种范围金额的排名。>
-- =============================================
CREATE PROCEDURE procOrderInfo
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    --第一步，计算orderMaster中的orderSum字段
    UPDATE dbo.orderMaster
    SET dbo.orderMaster.Ordersum=temp.ordersum
    FROM dbo.orderMaster,
          (SELECT SUM(dbo.orderDetail.Price*dbo.orderDetail.Qty) 'ordersum',OrderNo
             FROM dbo.orderDetail GROUP BY OrderNo) AS temp
    WHERE dbo.orderMaster.OrderNo=temp.OrderNo

看看结果：

region                                  amount                                  weighted    row
--------------------------------------- --------------------------------------- ----------- --------------------
0.00                                    860.00                                  28          2
500.00                                  760.00                                  25          3
1000.00                                 1420.00                                 46          1