简单存储过程的编写

最新推荐文章于 2021-06-07 10:11:08 发布
最新推荐文章于 2021-06-07 10:11:08 发布
阅读量421 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_15237993/article/details/78479788
-- ================================================
-- 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: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================


CREATE PROCEDURE [dbo].[DataCompare]   --新建存储过程
-- Add the parameters for the stored procedure here
@AreaCode varchar(50),--区域代码，参数都是输入参数
@AreaUnits varchar(50),--统计区域单位
@BeginTime nvarchar(10),--统计开始时间
@EndTime nvarchar(10)--统计结束时间
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--删除零时表数据
delete from temp_CompareDate

if @AreaUnits='CommunityCode'  
begin
--社区代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CommunityCode' AreaUnits,a.CommunityCode from
(select StationID,avg(eday) StationAVG,CommunityCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CommunityCode,'')!=''   group by StationID,CommunityCode) as a,
 (select AVG(eday) AreaAVG,CommunityCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(CommunityCode,'')!=''  group by CommunityCode) as b
 where a.CommunityCode=b.CommunityCode and a.StationAVG<b.AreaAVG  
--乡镇代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'TownCode' AreaUnits,a.TownCode from
(select StationID,avg(eday) StationAVG,TownCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')!=''  group by StationID,TownCode) as a,
 (select AVG(eday) AreaAVG,TownCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')!=''  group by TownCode) as b
 where a.TownCode=b.TownCode and a.StationAVG<b.AreaAVG  
--乡镇代码为空，县区代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CountyCode' AreaUnits,a.CountyCode from
(select StationID,avg(eday) StationAVG,CountyCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')!='' group by StationID,CountyCode) as a,
 (select AVG(eday) AreaAVG,CountyCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')!='' group by CountyCode) as b
 where a.CountyCode=b.CountyCode and a.StationAVG<b.AreaAVG 
--县区代码为空，城市代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CityCode' AreaUnits,a.CityCode from
(select StationID,avg(eday) StationAVG,CityCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
 AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by StationID,CityCode) as a,
 (select AVG(eday) AreaAVG,CityCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
 AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by CityCode) as b
 where a.CityCode=b.CityCode and a.StationAVG<b.AreaAVG 
 --县区代码为空，城市代码为空，省份代码不为空 
   insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'ProvinceCode' AreaUnits,a.ProvinceCode from
(select StationID,avg(eday) StationAVG,ProvinceCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by StationID,ProvinceCode) as a,
 (select AVG(eday) AreaAVG,ProvinceCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(CommunityCode,'')='' AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by ProvinceCode) as b
 where a.ProvinceCode=b.ProvinceCode and a.StationAVG<b.AreaAVG 
 --省份代码为空的不考虑
 end 

if @AreaUnits='TownCode'  
begin
--乡镇代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'TownCode' AreaUnits,a.TownCode from
(select StationID,avg(eday) StationAVG,TownCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(TownCode,'')!=''  group by StationID,TownCode) as a,
 (select AVG(eday) AreaAVG,TownCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(TownCode,'')!=''  group by TownCode) as b
 where a.TownCode=b.TownCode and a.StationAVG<b.AreaAVG  
--乡镇代码为空，县区代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CountyCode' AreaUnits,a.CountyCode from
(select StationID,avg(eday) StationAVG,CountyCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')!='' group by StationID,CountyCode) as a,
 (select AVG(eday) AreaAVG,CountyCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')!='' group by CountyCode) as b
 where a.CountyCode=b.CountyCode and a.StationAVG<b.AreaAVG 
--县区代码为空，城市代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CityCode' AreaUnits,a.CityCode from
(select StationID,avg(eday) StationAVG,CityCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
 AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by StationID,CityCode) as a,
 (select AVG(eday) AreaAVG,CityCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
 AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by CityCode) as b
 where a.CityCode=b.CityCode and a.StationAVG<b.AreaAVG 
 --县区代码为空，城市代码为空，省份代码不为空 
   insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'ProvinceCode' AreaUnits,a.ProvinceCode from
(select StationID,avg(eday) StationAVG,ProvinceCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by StationID,ProvinceCode) as a,
 (select AVG(eday) AreaAVG,ProvinceCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
    AND ISNULL(TownCode,'')='' AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by ProvinceCode) as b
 where a.ProvinceCode=b.ProvinceCode and a.StationAVG<b.AreaAVG 
 --省份代码为空的不考虑
 end 

if @AreaUnits='CountyCode'  
begin 
--县区代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CountyCode' AreaUnits,a.CountyCode from
(select StationID,avg(eday) StationAVG,CountyCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CountyCode,'')!='' group by StationID,CountyCode) as a,
 (select AVG(eday) AreaAVG,CountyCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CountyCode,'')!='' group by CountyCode) as b
 where a.CountyCode=b.CountyCode and a.StationAVG<b.AreaAVG 
--县区代码为空，城市代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CityCode' AreaUnits,a.CityCode from
(select StationID,avg(eday) StationAVG,CityCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
 AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by StationID,CityCode) as a,
 (select AVG(eday) AreaAVG,CityCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')!='' group by CityCode) as b
 where a.CityCode=b.CityCode and a.StationAVG<b.AreaAVG 
 --县区代码为空，城市代码为空，省份代码不为空
   insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'ProvinceCode' AreaUnits,a.ProvinceCode from
(select StationID,avg(eday) StationAVG,ProvinceCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by StationID,ProvinceCode) as a,
 (select AVG(eday) AreaAVG,ProvinceCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CountyCode,'')='' AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by ProvinceCode) as b
 where a.ProvinceCode=b.ProvinceCode and a.StationAVG<b.AreaAVG 
 --省份代码为空的不考虑
 end 

  if @AreaUnits='CityCode'  
begin 
--城市代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'CityCode' AreaUnits,a.CityCode from
(select StationID,avg(eday) StationAVG,CityCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CityCode,'')!='' group by StationID,CityCode) as a,
 (select AVG(eday) AreaAVG,CityCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CityCode,'')!='' group by CityCode) as b
 where a.CityCode=b.CityCode and a.StationAVG<b.AreaAVG 
 --城市代码为空，省份代码不为空
   insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'ProvinceCode' AreaUnits,a.ProvinceCode from
(select StationID,avg(eday) StationAVG,ProvinceCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by StationID,ProvinceCode) as a,
 (select AVG(eday) AreaAVG,ProvinceCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(CityCode,'')='' AND ISNULL(ProvinceCode,'')!='' group by ProvinceCode) as b
 where a.ProvinceCode=b.ProvinceCode and a.StationAVG<b.AreaAVG 
 --省份代码为空的不考虑
 end 
 
  if @AreaUnits='ProvinceCode'  
begin 
--省份代码不为空
      insert into temp_CompareDate( StationID, StationAVG, AreaAVG, AreaUnits, AreaCode) 
       select a.StationID,a.StationAVG,b.AreaAVG,'ProvinceCode' AreaUnits,a.ProvinceCode from
(select StationID,avg(eday) StationAVG,ProvinceCode from vStationRunBasicDatalast where createdate>@BeginTime and createdate<@EndTime
AND ISNULL(ProvinceCode,'')!='' group by StationID,ProvinceCode) as a,
 (select AVG(eday) AreaAVG,ProvinceCode from vStationRunBasicDatalast  where createdate>@BeginTime and createdate<@EndTime
  AND ISNULL(ProvinceCode,'')!='' group by ProvinceCode) as b
 where a.ProvinceCode=b.ProvinceCode and a.StationAVG<b.AreaAVG 
 --省份代码为空的不考虑
 end 
 
 
 
END
GO
android简单存储、文件存储的步骤
wuwndj的博客
10-31 1554
移动应用开发 10.18知识点整理 任务：实现用户信息的存储 一、简单存储 SharedPreferences 1）存放位置 SharedPreferences 数据存储在/data/data//shared_prefs 目录下。通过 Device File Explorer 面板可以查看 2）存取方式 存入数据 引入SharedPreferences对象 共经过4步完成： 1、获取 SharedPreferences 对象。 SharedPreferences spf = getSharedPrefere
达梦数据库简单存储过程编写-----工具向
Alan_lsz的博客
09-08 7481
达梦数据库简单存储过程编写-----工具向 这里展示的是简单的存储过程编写，主要针对于可视化管理工具的使用 首先我们选择一个模式名下，存储过程中创建存储过程 注意尽量不要使用sysdba的模式名 设置一个存储过程名（一般为了不出现各种bug，我们建议表，存储过程，各种命名用大写） 在参数表中设置一个参数，参数名请为大写！ 并为参数设置数据类型，长度，参数类型设置为IN 并在存储过程体中编写存储过...
写个简单的存储过程
fulong0406的博客
06-07 1127
-- 如果需要存储过程已存在，则可以先执行删除存储过程操作 DROP PROCEDURE IF EXISTS `proc_auto_insertservice_account_test`; -- 创建存储过程 CREATE PROCEDURE `proc_auto_insertservice_account_test`( ) -- 开始存储过程的执行 BEGIN -- 声明存储过程中的变量 DECLARE number INTEGER; -- DECLARE name1 varchar; DECLARE .
写一个简单的存储过程
weixin_30849403的博客
09-25 410
-- MySQL中的三中循环 while 、 loop 、repeat 求 1-n 的和 -- 第一种 while 循环 -- 求 1-n 的和 /* while循环语法： while 条件 DO 循环体; end while; */ create procedure ad...
1.一个简单的存储过程
wubinyu的专栏
10-07 1178
来源：网易云课堂《精通MySQL存储过程、函数和触发器》课程 讲师：huangxifeng607 笔记： 1.一个简单的存储过程 delimiter$$ create procedure testa() begin select uid from hxf where uid=2; end; $$ delimiter; 存储过程的结构组成： 1.创建格式：create p
oracle存储过程编写
03-20
以下是对"Oracle存储过程编写"的详细讲解。 一、存储过程的基本概念 1. 存储过程：存储在数据库服务器上的预编译的SQL和PL/SQL代码集合，可以在需要时调用，减少了网络流量，提高了系统性能。 2. PL/SQL：Oracle的...
ASP下存储过程编写入门全接触第1/5页
10-30
ASP下的存储过程编写是数据库应用程序开发中的重要环节，它能够提升效率、增强安全性和促进代码重用。存储过程是一组预先编译好的SQL语句，存储在数据库中，供应用程序调用执行。在这个主题中，我们将深入探讨如何在...
mysql存储过程简单应用编写
03-06
在本篇总结文档中，我们将探讨MySQL存储过程的简单应用，包括循环结构、创建临时表、删除数据、分页查询以及动态存储过程的编写。 首先，让我们了解一下什么是存储过程。存储过程是一组为了完成特定功能的SQL语句集...
sql存储过程几个简单例子
12-15
- 在例1中，我们看到了如何创建一个简单的存储过程`proc_stu`，它接受两个输入参数`sname`和`pwd`，然后查询`ren`表中对应名字和密码的记录。创建存储过程的语法是`CREATE PROC [存储过程名] @参数1 数据类型, @...
一个简单的存储过程
weixin_30550271的博客
12-08 171
创建一个简单的存储过程 其中涉及到是基本的传参，定义参数，参数赋值，条件语句，循环语句，用游标遍历等基本语法 --创建一个存储过程 create or replace procedure addgoods( -- 传入两个变量，一个输入变量，一个输出变量，输出变量用来作为返回值 gname1 user_table.user_skey%type , p...
一个简单的存储过程(1)
weixin_30698527的博客
06-05 116
一个简单的存储过程 delimiter$$ create procedure testa() begin select uid from hxf where uid=2; end; $$ delimiter; 存储过程：1.创建格式：create procedure 存储过程名2.包含一个以上代码块，代码块使用begin和end 之间3.在命令行中创建需要定义分隔符 d...
一个简单的存储过程范例
aaaa7410的博客
06-24 127
1 delimiter $$ 2 CREATE PROCEDURE `proc_Ranking`(IN sku VARCHAR(20),IN skuname VARCHAR(20),IN status VARCHAR(20),IN sales VARCHAR(20),IN today VARCHAR(20),IN old_time VARCHAR(20)) 3 BEGIN ...
存储过程（简单入门）
dy061951的博客
02-25 341
存储过程（Stored Procedure） 是在大型数据库系统中，一组为了完成特定功能的SQL语句，它存储在数据库中，一次编译后永久有效。 存储过程分类 1.有返回值 2.无返回值 Mysql常用函数 CONCAT(str1,str2,…)：拼接多个字符串（多用于字符串与数字的拼接 字符串用单引号引起来） select CONCAT(‘hello’,‘mtx’,1) RAND()：获取一个0...
Mysql 创建简单的存储过程
Maodyuan的博客
01-06 837
1.  新建一张简单的boox表如下 2.  开始 写存储过程 （可以参考我的另一篇文章   mysql 创建函数 和存储过程比较一下，二者非常相似） -- 创建  查询存储过程 -- 删除可能存在的 add_boox  DROP PROCEDURE IF EXISTS add_boox; DELIMITER//    -- 设
写一个简单的存储过程实例的笔记
weixin_30535565的博客
08-08 100
1，运行在phpmyadmin 上，用于插入数据，关联产品与运费表 1 /*定义存储过程分解符*/ 2 delimiter // 3 DROP PROCEDURE IF EXISTS doShipping// 4 CREATE PROCEDURE doShipping() 5 BEGIN 6 /*声明变量*/ 7 DECLARE no_more_re...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值