使用存储过程编写,然后我们通过一个这样的语

2019-08-31 作者:数据库   |   浏览(122)

放假之前老大跟我提起了一下2016有个有趣的功能叫 Temporal Table ,今天去看了一下资料整理一下。

ALTER PROCEDURE [dbo].[POR_CURSOR_FOR_UPDATE]    --创建存储过程
AS
BEGIN
  SET nocount ON                   --忽略行数显示
  DECLARE UpdateSHEBEI CURSOR              --声明游标
  FOR SELECT X_D_TJSL,X_G_JSL FROM FACT_MX  --要查询的结果集

  OPEN UpdateSHEBEI          --打开游标 
  DECLARE @X_D_TJ VARCHAR(128),@X_G_J VARCHAR(128);  --声明变量

 FETCH NEXT FROM UpdateSHEBEI INTO @X_D_TJ,@X_G_J     
 WHILE @@FETCH_STATUS = 0       --开始循环
 BEGIN 
   SELECT @X_D_TJ=CEILING(rand()*50);
    SELECT @X_G_J=CEILING(rand()*6);    --给变量赋值
    UPDATE FACT_SHEBEI_MX SET X_D_TJSL=@X_D_TJ,X_G_JSL=@X_G_J WHERE CURRENT OF UpdateSHEBEI  --更新表数据
   FETCH NEXT FROM UpdateSHEBEI INTO @X_D_TJ,@X_G_J     --下一行数据
  END
  CLOSE UpdateSHEBEI;   --关闭游标
 DEALLOCATE UpdateSHEBEI    
  SET nocount OFF      --打开计数

END

SQL 基础知识梳理(三) - 聚合和排序

【博主】反骨仔    【原文】

 

小鱼儿玄机30码姐妹 1

 

需求:按照分组,将多条记录内容合并成一条,效果如下:

这个功能看上去像是临时表,但是其实是系统维护的一个历史记录表。(在某个程度上面比起我们手动维护的历史表应该方便了一点的)

   上学时学的东西,都忘了,用到了,就翻出来学习了一下。使用存储过程编写,可直接运行该存储过程注释都写好了,变量赋值也比较清楚,需要的可以直接复制下来然后替换就好。

  这是《SQL 基础知识梳理(二) - 查询基础》的下篇。

 

小鱼儿玄机30码姐妹 2

简单直接的说,它的界面看起来是这样的(就像是T1一样) 创建了之后,就会在下面有一个T1History的表中表来记录。

目录

  • 对表进行聚合查询
  • 对表进行分组
  • 为聚合结果指定条件
  • 对查询结果进行排序

 

数据库示例:

小鱼儿玄机30码姐妹 3

一、对表进行聚合查询 

  1.聚合函数

    (1)5 个常用函数:

      ①COUNT:计算表中的记录(行)数。

      ②SUM:计算表中数值列的数据合计值。

      ③AVG:计算表中数值列的数据平均值。

      ④MAX:求出表中任意列中数据的最大值。

      ⑤MIN:求出表中任意列中数据的最小值。

 

    (2)聚合:将多行汇总成一行。

小鱼儿玄机30码姐妹 4

图1-1 Shohin 表

 

小鱼儿玄机30码姐妹,  2.计算表中数据的行数 

--示例
SELECT COUNT(*)  -- *:参数,这里代表全部列
FROM dbo.Shohin;

小鱼儿玄机30码姐妹 5

 

  3.计算 NULL 以外数据的行数

  将 COUNT(*) 的参数改成指定对象的列,就可以得到该列的非 NULL 行数。

SELECT COUNT(shiire_tanka)
FROM dbo.Shohin;

小鱼儿玄机30码姐妹 6

只计算非 NULL 的行

  【备注】除了 COUNT 函数,其它函数不能将星号作为参数。

  【备注】COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数,而 COUNT(<列名>) 会得到 NULL 之外的数据行数。

 

  4.计算合计值

SELECT SUM(hanbai_tanka) AS sum_hanbai_tanka,    --总和
    AVG(hanbai_tanka) AS avg_hanbai_tanka,        --平均
    MAX(hanbai_tanka) AS max_hanbai_tanka,        --最大
    MIN(hanbai_tanka) AS min_hanbai_tanka        --最小
FROM dbo.Shohin;

小鱼儿玄机30码姐妹 7

  【备注】所有的聚合函数,如果以列名为参数,会无视 NULL 值所在的行。

 

SELECT MAX(torokubi),  --torokubi 为日期
    MIN(torokubi)
FROM dbo.Shohin

小鱼儿玄机30码姐妹 8

  【备注】MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。

 

  5.使用聚合函数删除重复值(关键字 DISTINCT)

--示例1:计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT shohin_bunrui)
FROM dbo.Shohin;

--示例2:先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(shohin_bunrui)
FROM dbo.Shohin;

小鱼儿玄机30码姐妹 9

  【备注】在聚合函数的参数中使用 DISTINCT(示例1),可以删除重复数据。DISTINCT 不仅限于 COUNT 函数,所有的聚合函数都可以使用。

 

CREATE TABLE [t2]([NID] [bigint] NULL,[district] [nvarchar](255) NULL,[town] [nvarchar](255) NULL);
insert into t2 values(1,'淮上区','曹老集镇');
insert into t2 values(2,'淮上区','淮滨街道');
insert into t2 values(3,'淮上区','梅桥乡');
insert into t2 values(4,'淮上区','吴小街镇');
insert into t2 values(5,'淮上区','小蚌埠镇');
insert into t2 values(1,'光明新区','公明街道');
insert into t2 values(2,'光明新区','光明街道');
insert into t2 values(1,'吉利区','大庆路街道');
insert into t2 values(2,'吉利区','吉利乡');

 然后我们来试下怎么去玩这个功能。首先,确认你的Sql Server 版本是2016。然后我们通过一个这样的语句来创建表

二、对表进行分组

  1.GROUP BY 子句

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...;

--示例
SELECT shohin_bunrui AS '商品种类',
    COUNT(*) AS '数量'
FROM dbo.Shohin
GROUP BY shohin_bunrui;

小鱼儿玄机30码姐妹 10

  【备注】GROUP BY 子句中指定的列称为“聚合键”或“分组列”。

  【子句的书写顺序(暂定)】SELECT --> FROM --> WHERE --> GROUP BY

 

  2.聚合键中包含 NULL 的情况

SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
GROUP BY shiire_tanka;

小鱼儿玄机30码姐妹 11

  【备注】聚合键中包含 NULL 时,在结果中也会以 NULL 行的形式表现出来。

 

  3.WHERE 对 GROUP BY 执行结果的影响

--语法
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--WHERE <表达式>
--GROUP BY <列名1>, <列名2>, ...

SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka

小鱼儿玄机30码姐妹 12

  这里是先根据 WHERE 子句指定的条件进行过滤,然后再进行聚合处理。

  【执行顺序】FROM --> WHERE --> GROUP BY --> SELECT。这里是执行顺序,跟之前的书写顺序是不一样的。

 

  4.与聚合函数和 GROUP BY 子句有关的常见错误

  (1)易错:在 SELECT 子句中书写了多余的列

    SELECT 子句只能存在以下三种元素:

      ①常数

      ②聚合函数

      ③GROUP BY 子句中指定的列名(即聚合键)

小鱼儿玄机30码姐妹 13

易错点1

  【总结】使用 GROUP BY 子句时,SELECT 子句不能出现聚合键之外的列名。

  (2)易错:在 GROUP BY 子句中写了列的别名   

小鱼儿玄机30码姐妹 14

 

易错点2

  回顾之前说的执行顺序,SELECT 子句是在 GROUP BY 子句之后执行。所以执行到 GROUP BY 子句时无法识别别名。

  【总结】GROUP BY 子句不能使用 SELECT 子句中定义的别名。

 

  (3)易错:GROUP BY 子句的结果能排序吗?

  【解答】它是随机的。如果想排序,请使用 ORDER BY 子句。

  【总结】GROUP BY 子句结果的显示是无序的。

 

  (4)易错:在 WHERE 子句中使用聚合函数

小鱼儿玄机30码姐妹 15

易错点3

  【总结】只有 SELECT 子句和 HAVING 子句(以及 ORDER BY 子句)中能够使用聚合函数。

  

 

Use Test
go

create table T1(ID int identity primary key,
    COl1 nvarchar(50),
    TimeFrom datetime2 generated always as row start,
    TimeTo datetime2 generated always as row end,
    period for system_time(TimeFrom,TimeTo)) with (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T1History));

三、为聚合结果指定条件

  1.HAVING 子句

  WHERE 子句智能指定记录(行)的条件,而不能用来指定组的条件。

  【备注】HAVING 是 HAVE(拥有)的现在分词。

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...
--HAVING <分组结果对应的条件>

  【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING

SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2

小鱼儿玄机30码姐妹 16

从通过商品种类进行聚合分组后的结果中,取出“包含数据的行数为 2 行”的数据

  2.HAVING 子句的构成要素

    (1)3 要素:

      ①常数

      ②聚合函数

      ③GROUP BY 子句中指定的列名(即聚合键)

小鱼儿玄机30码姐妹 17

易错用法

  3.HAVING 与 WHERE

  有些条件可以写在 HAVING 子句中,又可以写在 WHERE 子句中。这些条件就是聚合键所对应的条件。

  小鱼儿玄机30码姐妹 18

结果一样

  【建议】虽然结果一样,聚合键对应的条件应该写在 WHERE 子句中,不是 HAVING 子句中。

  【理由】①WHERE 子句的执行速度比 HAVING 快。

      ②意义:WHERE 子句 = 指定行所对应的条件,HAVING 子句 = 指定组所对应的条件。

 

根据不同的SQL版本,可以有以下方法:

 

四、对查询结果进行排序

  1.ORDER BY 子句

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--ORDER BY <排序基准列1>, <排序基准列2>, ...

SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka;    --升序排列

小鱼儿玄机30码姐妹 19

销售单价由低到高(升序)

  排序键:ORDER BY 子句中书写的列名。

  【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY

 

   2.升序(ASC)和降序(DESC):

SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC;    --降序排列

小鱼儿玄机30码姐妹 20

  【备注】ORDER BY 子句中排列顺序时会默认使用升序(ASC)进行排列。

 

  3.指定多个排序键

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka, shohin_id;

小鱼儿玄机30码姐妹 21

  4.NULL 值的顺序:排序键中包含 NULL 时,会在开头或末尾进行汇总。

 

  5.在排序键中使用 SELECT 子句中的别名

SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht
FROM dbo.Shohin
ORDER BY ht, id;

小鱼儿玄机30码姐妹 22

 

  【执行顺序】FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

  【备注】ORDER BY 子句可以使用 SELECT 子句中定义的别名,GROUP BY 子句不能使用别名。

 

  6.ORDER BY 子句中使用聚合函数

SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
ORDER BY COUNT(*);

小鱼儿玄机30码姐妹 23

  7.不建议使用列的编号进行排序,虽然可以

SELECT shohin_id ,
       shohin_mei ,
       shohin_bunrui ,
       hanbai_tanka ,
       shiire_tanka ,
       torokubi
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC, shohin_id;

SELECT shohin_id ,
       shohin_mei ,
       shohin_bunrui ,
       hanbai_tanka ,
       shiire_tanka ,
       torokubi
FROM dbo.Shohin
ORDER BY 4 DESC, 1;  --这里使用列的编号,由于阅读不便,不推荐使用

小鱼儿玄机30码姐妹 24

结果是一样的

  【备注】在 ORDER BY 子句中不要使用列的编号。

 

一、SQL 2000 不支持FOR XML,不支持CONCAT。只能写自定义函数。

这里我就简单的创建一个只有自增主键和一个列的表。创建System_Versioning 的表。必须有2个声明为datetime2 的时间字段才行,因为需要用这2个字段来记录数据的产生轨迹。

传送门

  《SQL 基础知识梳理(一) - 数据库与 SQL》

  《SQL 基础知识梳理(二) - 查询基础》

 

CREATE FUNCTION dbo.townconcat(@district nvarchar(255)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str   ','   town FROM t2 WHERE district=@district 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt district, town = dbo.townconcat(district) FROM t2 GROUP BY district 

drop function dbo.townconcat
go

比如这里我是使用一个TimeFrom 的字段表示数据的作用开始时间,而TimeTo表示这行数据的失效时间(比方说数据被修改,被删除,那么TimeTo就会记录着修改,删除的时间)

备注

  这里采用 MS SQL Server 进行验证,不保证所有的 DBMS 执行结果正确。

 


【参考】《SQL ゼロからはじめるデータベース操作》

 

二、SQL 2012 支持 concat,2000版本自定义函数的基础上可少量优化

下面我们进行测试,先做测试样例,然后再说明

--将2000版中的
SELECT @str = @str   ','   town FROM t2 WHERE district=@district
--变成
SELECT @str = concat(@str,',',town) FROM t2 WHERE district=@district 

其他代码不变

Step 1:新增数据

三、SQL2005支持for xml,可以大量简化

insert into T1 (Col1) 
    values ('1111'),('2222'),('3333')

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

(3 行受影响)

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
select distinct a.district,
(SELECT town ','FROM t2 where district=a.district FOR XML PATH(''))as towns 
from t2 a

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:使用存储过程编写,然后我们通过一个这样的语

关键词: 小鱼儿玄机30码