カテゴリー: SQL

SQL CURSOR

DECLARE @CurrentDate DATETIME
DECLARE @CurrentDateStr VarCHAR(200)
DECLARE @CloseTime int

DECLARE @CTRLTIME Date
DECLARE @DCFLG CHAR(1)
DECLARE @TERMID CHAR(5)

DECLARE @Exist_Cnt int
DECLARE @REC_Cnt int = 0
DECLARE @REC_Cnt_MAX int = 60
DECLARE @Delay_Time int
DECLARE @Delay_Time_MAX int = 60

DECLARE @Temp_ABCD TABLE(
[TERMID] char NOT NULL,
[DCFLG] char NOT NULL,
[AAAA] char NOT NULL,
[BBBB] [int] NOT NULL,
[CCCC] varchar NULL,
[ANS] char NULL,
[ANSCODE]char NULL
);

SET @Delay_Time = 10
SET @Delay_Time_MAX = 60

Declare CUR_ABCD CURSOR FOR
SELECT TERMID
,DCFLG
,BBBB
FROM [KKKK_Common_C].[dbo].[ABCD]

SET @CurrentDate = (SELECT GETDATE())
SET @CurrentDateStr = CONVERT(varchar,@CurrentDate,120)

PRINT ‘@CurrentDateStr = ‘ + @CurrentDateStr

SET @CloseTime = CAST(SUBSTRING(@CurrentDateStr,12,2) as int)
PRINT ‘@CloseTime = ‘ + CAST(@CloseTime AS VARCHAR)

WHILE @REC_Cnt < 10
BEGIN
OPEN CUR_ABCD
FETCH NEXT FROM CUR_ABCD
INTO @TERMID, @DCFLG, @CTRLTIME

 WHILE @@FETCH_STATUS = 0
 BEGIN 
     IF @DCFLG = '1'
     BEGIN
        SET @Delay_Time = CAST(datediff(second,@CTRLTIME,getdate()) as int)
        IF @Delay_Time > @Delay_Time_MAX
        BEGIN
             SELECT @Exist_Cnt = COUNT(*) FROM @Temp_ABCD WHERE [TERMID] = @TERMID AND [CTRLTIME] = @CTRLTIME
             IF @Exist_Cnt = 0
             BEGIN
                 INSERT INTO @Temp_ABCD SELECT* FROM [KKKK_Common_C].[dbo].[ABCD] WHERE [TERMID] = @TERMID
             END
        END
     END

     FETCH NEXT FROM CUR_ABCD
     INTO @TERMID, @DCFLG, @CTRLTIME
 END

 CLOSE CUR_ABCD
 SELECT @REC_Cnt = COUNT(*) FROM @Temp_ABCD

 waitfor delay '00:00:00:10'

END

DEALLOCATE CUR_ABCD

SELECT * FROM @Temp_ABCD

WHILE

–Select * INTO Table_A FROM Table_B;

–Select Count(*) FROM Table_A;

–Insert INTO Table_A SELECT * FROM Table_B;

DECLARE @iLoop int

DECLARE @Auto1 int

DECLARE @INDEXNO CHAR(16)=’4444555566667777′

SET @iLoop =1

SET @Auto1 = 800

WHILE @iLoop < 200

BEGIN

INSERT INTO Table_A(AA,BB,CC,DD,EE)

SELECT @INDEXNO AS AA, 1 as BB,@Auto1 as CC,DD,EE

FROM Table_B

WHERE INDEXNO = ‘1111222244446666’ AND DD = 1

SET @iLoop = @iLoop +1

SET @INDEXNO =

CAST(CAST(@INDEXNO as bigint) + 1 as char(16))

SET @Auto1 = @Auto1 + 1

END

SQL UNION 和 UNION ALL 操作符

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面的例子中使用的原始表：

Employees_China:

E_IDE_Name
01Zhang, Hua
02Wang, Wei
03Carter, Thomas
04Yang, Ming

Employees_USA:

E_IDE_Name
01Adams, John
02Bush, George
03Carter, Thomas
04Gates, Bill

使用 UNION 命令

实例

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1
UNION ALL
SQL Statement 2

使用 UNION ALL 命令

实例：

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill

Oracle inner join、left join、right join

Oracle inner join、left join、right join 、+左边或者右边的区别

我们以Oracle自带的表来做例子

主要两张表：dept、emp

一个是部门，一个是员工表结构如下：

namenull?Type
Empnonot nullnumber(4)
ename varchar2(10)
job varchar2(9)
mgr number(4)
hiredate date
sal number(7,2)
comm number(7,2)
deptno number(2)
namenull?Type
deptnonot nullnumber(2)
dname varchar2(14)
log varchar2(13)

这两张表 dept是主表 emp是子表，关联的列是deptnodept表现有数据

   emp表现有数据 

inner join 意思是内连接 把匹配的信息全部查出来SQL>select e.empno,e.ename,e.job,d.deptno,d.dname 
from emp e innerjoin dept d on e.deptno=d.deptno orderby e.empno;查询的结果：

 left join 左连接 意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐SQL>select e.empno,e.ename,e.job,d.deptno,d.dname 
from emp e leftjoin dept d on e.deptno=d.deptno orderby e.empno;解释SQL :这条SQL语句左边是EMP表 左连接结果查询出EMP所有的记录，然后根据左边表匹配出右边表DEPT所有的记录 查询结果如下： 

right join 右连接  意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐
SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e rightjoin dept d on e.deptno=d.deptno orderby e.empno;解释SQL：这条SQL语句EMP在左边，DEPT在右边，然后我们采用右连接，就查出右边表所有的数据 查询结构： full join  全连接 意思是左右表所有的记录全部显示出来SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e fulljoin dept d on e.deptno=d.deptno orderby e.empno;
解释SQL:这条SQL语句采用全连接，查询出左表和右表所有的值出来查询结果：

  介绍Oracle +连接方式及说明 +在左边 为右连接SQL>select e.empno,e.ename,e.job,d.deptno,d.dname 
from emp e,dept d where e.deptno(+)=d.deptno orderby e.empno; 
查询结果： 

+在右边 为左连接SQL>select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+) orderby e.empno; 

查询结果： 

Oracle 内连接（inner join）、外连接（outer join）、全连接（full join）

之前没有用过 full outer join，第一次用，学习一下

Student表

Color表

Oracle中的连接可分为，内连接（inner join）、外连接（outer join）、全连接（full join），不光是Oracle，其他很多的数据库也都有这3种连接查询方式:

内连接inner join/join

也叫自连接，这是我们经常用到的查询方式，内连接查询只能查询出匹配的记录，匹配不上的记录时无法查询出来的 ，以下三种查询结果一样

select * from student s, color c where s.stuname = c.stuname;

select * from student s inner join color c on s.stuname = c.stuname;

select * from student s join color c on s.stuname = c.stuname;

外连接outer join

可进一步分为左外连接left outer join和右外连接right outer join，（简称左连接left join，右连接 right join）。

左外连接

左连接就是以左边的表（left join 左边的表）为主表，即使有些记录关联不上，主表的信息也能全部查询出来，也就是左边的表数据全部展示，右边表的数据复合条件的展示，不符合条件的以空值代替，适合那种需要求出维度（比如求出所有人员）的需求：

select * from student s left join color c on s.stuname = c.stuname;

等同于select * from student s left outer join color c on s.stuname = c.stuname;

右外连接

如果有需求要求在结果中展现所有的颜色信息，就可以用右连接：

还有另一种写法，可以达到相同的外连接效果：比如左外连接等同于以下的语句：

select * from student s ,color c where s.stuname = c.stuname(+);

同样右连接是这样的：

select * from student s ,color c where s.stuname(+) = c.stuname;

在(+)计算时，哪个带(+)哪个需要条件符合的，另一个全部的。即放左即右连接，放右即左连接。

全连接full join/full outer join

语法是语法为full join … on …，全连接的查询结果是左外连接和右外连接查询结果的并集，即使一些记录关联不上，也能够把部分信息查询出来：

产生M+N的结果集，列出两表全部的，不符合条件的，以空值代替。

select * from student s full join color c on s.stuname = c.stuname;

select * from student s full join color c on 1=1

笛卡尔乘积cross join

即不加任何条件，达到 M*N 的结果集。

以下两种查询结果一样。

select * from student s cross join color c

select * from student s , color c

注意：如果cross join加上where on s.stuname = c.stuname条件，会产生跟自连接一样的结果（cross join 后加上 on 报错）：

加上条件，产生跟自连接一样的结果。

select * from student s cross join color c where s.stuname = c.stuname;

自连接结果集的cross join连接结果

总结

ü   所有的join连接，都可以加上类似where a.id=’1000’的条件，达到同样的效果。因为on不能做这种判断，只能是

ü   除了cross join不可以加on外，其它join连接都必须加上on关键字，后都可加where条件。

ü   虽然都可以加where条件，但是他们只在标准连接的结果集上查找where条件。比如左外连接的结果没有class的三班，所以如果加 where class.id=’C003’虽然在表中有，但在左连接结果集中没有，所以查询后，是没有记录的。

A表有100条数据，B表有80条数据，left join on 1=1,where 1=1的结果是：

应该是笛卡尔积

A表有100条数据，B表有80条数据，left join on 1=2,where 1=1的结果是：

应该是A表的值

测试：

select * from student s left join color c on 1=1 where 1=1

select * from student s left join color c on 1=2 where 1=1

SQL中Group By的使用

1、概述

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

2、原始表

3、简单Group By

示例1

select 类别, sum(数量) as 数量之和
from A
group by 类别

返回结果如下表，实际上就是分类汇总。

4、Group By 和 Order By

示例2

select 类别, sum(数量) AS 数量之和
from A
group by 类别
order by sum(数量) desc

返回结果如下表

在Access中不可以使用“order by 数量之和 desc”，但在SQL Server中则可以。

5、Group By中Select指定的字段限制

示例3

select 类别, sum(数量) as 数量之和, 摘要
from A
group by 类别
order by 类别 desc

示例3执行后会提示下错误，如下图。这就是需要注意的一点，在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

6、Group By All

示例4

select 类别, 摘要, sum(数量) as 数量之和
from A
group by all 类别, 摘要

示例4中则可以指定“摘要”字段，其原因在于“多列分组”中包含了“摘要字段”，其执行结果如下表

“多列分组”实际上就是就是按照多列（类别+摘要）合并后的值进行分组，示例4中可以看到“a, a2001, 13”为“a, a2001, 11”和“a, a2001, 2”两条记录的合并。

SQL Server中虽然支持“group by all”，但Microsoft SQL Server 的未来版本中将删除 GROUP BY ALL，避免在新的开发工作中使用 GROUP BY ALL。Access中是不支持“Group By All”的，但Access中同样支持多列分组，上述SQL Server中的SQL在Access可以写成

select 类别, 摘要, sum(数量) AS 数量之和
from A
group by 类别, 摘要

7、Group By与聚合函数

在示例3中提到group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，常见的聚合函数如下表：

函数作用支持性
sum(列名)求和　　　　
max(列名)最大值　　　　
min(列名)最小值　　　　
avg(列名)平均值　　　　
first(列名)第一条记录仅Access支持
last(列名)最后一条记录仅Access支持
count(列名)统计记录数注意和count(*)的区别

示例5：求各组平均值

select 类别, avg(数量) AS 平均值 from A group by 类别;

示例6：求各组记录数目

select 类别, count(*) AS 记录数 from A group by 类别;

示例7：求各组记录数目

8、Having与Where的区别

  • where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
  • having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例8

select 类别, sum(数量) as 数量之和 from A
group by 类别
having sum(数量) > 18

示例9：Having和Where的联合使用方法

select 类别, SUM(数量)from A
where 数量 gt;8
group by 类别
having SUM(数量) gt; 10

9、Compute 和 Compute By

select * from A where 数量 > 8

执行结果：

示例10：Compute

select *
from A
where 数量>8
compute max(数量),min(数量),avg(数量)

执行结果如下：

compute子句能够观察“查询结果”的数据细节或统计各列数据（如例10中max、min和avg），返回结果由select列表和compute统计结果组成。

示例11：Compute By

select *
from A
where 数量>8
order by 类别
compute max(数量),min(数量),avg(数量) by 类别

执行结果如下：

示例11与示例10相比多了“order by 类别”和“… by 类别”，示例10的执行结果实际是按照分组（a、b、c）进行了显示，每组都是由改组数据列表和改组数统计结果组成，另外：

  • compute子句必须与order by子句用一起使用
  • compute…by与group by相比，group by 只能得到各组数据的统计结果，而不能看到各组数据

在实际开发中compute与compute by的作用并不是很大，SQL Server支持compute和compute by，而Access并不支持