我的SQL学习贴

autumnalRain

为什么SUM函数中的字段名必须加[]？

1. select 银行,sum([2021年])AS 合计余额 from LOAN where 科目="长期借款" group by 银行

复制代码

autumnalRain

SQL语句中加中括号的作用
在操作SQLServer数据库的时候，经常能看到用中括号（[]）括住的字段，这些字段可能是表名或者字段名等等。那么为什么要加中括号呢？解释如下：

为了防止某些关键字在使用中引起歧义，如果加上中括号，则代表这是一个字段名，而不是关键字。例如：表中有一个字段叫select，而select是SQL中的一个关键字，如果直接写作《 select select from 表名》，那么语句是会报错的。如果加上中括号《select [select] from 表名》，数据库则能识别。
解决较长的中文名表名可能会被不识别的问题
注意：不仅列名可加中括号，包括表名，存储过程名，函数名等都可以按需要加中括号。

autumnalRain

autumnalRain 发表于 2021-11-4 17:42
SQL语句中加中括号的作用
在操作SQLServer数据库的时候，经常能看到用中括号（[]）括住的字段，这些字段可 ...

1、为避免未知的错误，尽量任何时候都加上[]，或者在语句不能运行的时候，试试把字段值或表名加上[]2、数据表中数字字段如金额数量等字段若为空值最好以0填充，否则可能出现使用聚合函数无法得出正确数值的情况。

jiangxiaoyun

autumnalRain 发表于 2021-11-4 19:10
1、为避免未知的错误，尽量任何时候都加上[]，或者在语句不能运行的时候，试试把字段值或表名加上[]2、数 ...

还不如起一个好的字段名称，名字够帅，自然啥问题都没

autumnalRain

autumnalRain 发表于 2021-11-4 17:39
为什么SUM函数中的字段名必须加[]？

1. SELECT 银行,SUM(2021年) AS 合计 FROM [Sheet1$] WHERE 科目="长期借款" GROUP BY 银行

复制代码

autumnalRain

autumnalRain 发表于 2021-11-4 19:10
1、为避免未知的错误，尽量任何时候都加上[]，或者在语句不能运行的时候，试试把字段值或表名加上[]2、数 ...

sql在excel中的应用（常见问题解答）



1. 常见问题及解答。
1：参数不足，期待为X
意思：在表中，有X个字段找不到。

举例：select 姓名,销售,金额 from [数据源$];当我们输入这个语句时,假如提示“参数不足,期待为2”，那么，说明在表[数据源$]中，有2个字段是找不到的

即：姓名、销售、金额这个三个字段有两个是找不到。

解决方法：检查字段是否写错，或者用第一把破妄之剑将字段名称提取出来；检查工作表路径是否出错。

2. Microsoft Jet数据库引擎找不到对象XXX。请确认对象是否存在，并正确地写出它的名称和路径。
意思：路径出错或表名称写错或表已经不存在。

解决方法：通常出现这个问题，往往是文件被转移了，一般通过修改路径就可以。修改路径不行，就看看表是否存在或名称是否输错。

3：Microsoft Jet数据库引擎打不开文件''。它已经被别的用户以独占方式打开，或没有查看数据的权限。(如下两图）
出现这个问题的原因未知。解决方法：将该表窗口最小化，再恢复。

4：语法错误（操作符丢失）在查询表达式'XX'中。
这个要具体分析。

以下图为例子，是由于字段“部 门”的字段名称含空格，必须用“[]”或“``”括住字段名称。

以下图为例子，我们输入select * [sheet1$]，出现如下提示，这是由于缺省from。

除这个两种情况外，还有很多。大家以后遇到可以参照SQL的各语句和函数的语法和字段的命名来作相应修改。

5：无法从数据库中提取数据。如下图：
分析：产生这类问题的原因，一般是由于SQL语句中进行无法计算的运算。如文本字段＋数字等。

解决方法：具体问题具体分析。这个说法很“腔”，实际上考验的就是对知识的积累。

6：字段命名规则。
当查询的字段名称中含有空格或特殊符号，必须用“[]”或“``”括住字段名称，当字段名称是空或字段名称以数字开头，SQL会自动的将该字段的名称更正为Fx

其中，x为该字段在表中的位置。

当进行单表查询时，不用声明字段所属表，单进行多表查询时，必须声明表所属位置。因为你不声明，SQL就不知道你想返回的是那个表的字段。（这个在多表查询时再说）

7：AS（别名）
AS指令意思是让用户自行为字段指定新的名称，即别名。使用别名可以简化语句，也利于减少出错。

除了字段可以使用别名外，表也可以使用别名（这个留待多表查询时说）。

8：标点符号。
SQL是不区分大小写的，采用的标点是西式标点。使用中文标点会出错。

如：select * from 表与SeLeCT * FROM 表 是一样的。

在07（含07）以下版本中，SQL对字段的记录区分大小写，而在10版本中，不区域大小写。


9：查询字段名如果是以数字开头需要加[]
例子： 链接[2018年度考核成绩错误量]+[2019年度考核成绩错误量]

两个字段是，如果不加[]；（链接2018年度考核成绩错误量+2019年度考核成绩错误量），就会报错，操作符丢失。一定要注意。


10：excel日期转换为文本 format函数
日期->文本

select format(#2008-08-25#,'yyyy-mm-dd');

文本->日期

SELECT cdate('2008-08-25');

11：left join 后如果要接select时需要用括号括起来，不然会报错（from子句错误）

autumnalRain

SQL语句执行顺序

ql和mysql执行顺序，发现内部机制是一样的。最大区别是在别名的引用上。

一、sql执行顺序
(1)from
(3) join
(2) on
(4) where
(5)group by------------------------------------(开始使用select中的别名，后面的语句中都可以使用)???
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by

从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
第一步：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）
第二步：接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2
第三步：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3
第四步：如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。
第五步：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4，在这有个比较重要的细节不得不说一下，对于包含outer join子句的查询，就有一个让人感到困惑的问题，到底在on筛选器还是用where筛选器指定逻辑表达式呢？on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来，而where的移除的最终的。举个简单的例子，有一个学生表（班级,姓名）和一个成绩表(姓名,成绩)，我现在需要返回一个x班级的全体同学的成绩，但是这个班级有几个学生缺考，也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在on子句指定学生和成绩表的关系（学生.姓名=成绩.姓名）那么我们是否发现在执行第二步的时候，对于没有参加考试的学生记录就不会出现在vt2中，因为他们被on的逻辑表达式过滤掉了,但是我们用left outer join就可以把左表（学生）中没有参加考试的学生找回来，因为我们想返回的是x班级的所有学生，如果在on中应用学生.班级='x'的话，left outer join会把x班级的所有学生记录找回（感谢网友康钦谋__康钦苗的指正），所以只能在where筛选器中应用学生.班级='x' 因为它的过滤是最终的。
第六步：group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。
第七步：应用cube或者rollup选项，为vt5生成超组，生成vt6.
第八步：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。
第九步：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.
第十步：应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。
第十一步：应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。
第十二步：应用top选项。此时才返回结果给请求者即用户。

二、mysql的执行顺序
SELECT语句定义
一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下：
SQL代码

• <SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]
  

SELECT子句是必选的，其它子句如WHERE子句、GROUP BY子句等是可选的。
一个SELECT语句中，子句的顺序是固定的。例如GROUP BY子句不会位于WHERE子句的前面。

SELECT语句执行顺序
SELECT语句中子句的执行顺序与SELECT语句中子句的输入顺序是不一样的，所以并不是从SELECT子句开始执行的，而是按照下面的顺序执行：
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果
每个子句执行后都会产生一个中间结果，供接下来的子句使用，如果不存在某个子句，就跳过
对比了一下，mysql和sql执行顺序基本是一样的, 标准顺序的 SQL 语句为:

• select 考生姓名, max(总成绩) as max总成绩
• from tb_Grade
• where 考生姓名 is not null
• group by 考生姓名
• having max(总成绩) > 600   
• order by max总成绩
  

在上面的示例中 SQL 语句的执行顺序如下:

　　 (1). 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据

　　 (2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL 的数据

　　 (3). 执行 GROUP BY 子句, 把 tb_Grade 表按 "学生姓名" 列进行分组(注：这一步开始才可以使用select中的别名，他返回的是一个游标，而不是一个表，所以在where中不可以使用select中的别名，而having却可以使用，感谢网友  zyt1369  提出这个问题)

　　 (4). 计算 max() 聚集函数, 按 "总成绩" 求出总成绩中最大的一些数值

　　 (5). 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的.

　　 (7). 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序.

autumnalRain

SQL中Group By的使用

1、概述

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

2、原始表

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png

3、简单Group By

示例1

select 类别, sum(数量) as 数量之和

from A

group by 类别

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

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png

4、Group By 和 Order By

示例2

select 类别, sum(数量) AS 数量之和

from A

group by 类别

order by sum(数量)desc

返回结果如下表

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png

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

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

示例3

select 类别, sum(数量) as 数量之和, 摘要

from A

group by 类别

order by 类别 desc

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

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png

6、Group By All

示例4

select 类别, 摘要, sum(数量) as 数量之和

from A

group by all 类别, 摘要

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

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png

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

SQL Server中虽然支持“groupby all”，但MicrosoftSQL 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

执行结果：   

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image006.png

示例10：Compute

select *

from A

where 数量>8

compute max(数量),min(数量),avg(数量)

执行结果如下：

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image007.png

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

示例11：Compute By

select *

from A

where 数量>8

order by 类别

compute max(数量),min(数量),avg(数量) by 类别

执行结果如下：

file:///C:/Users/HUANGJ~1/AppData/Local/Temp/msohtmlclip1/01/clip_image008.png

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

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

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

autumnalRain

用some,any和all对子查询中返回的多行结果进行处理。下面我们来简单介一下这几个关键词的含义。
Some在此表示满足其中一个的意义，是用or串起来的比较从句。
Any也表示满足其中一个的意义，也是用or串起来的比较从句，区别是any一般用在非“=”的比较关系中，这也很好理解，英文中的否定句中使用any肯定句中使用sone，这一点是一样的。
All则表示满足其其中所有的查询结果的含义，使用and串起来的比较从句。
下面是一些例子
找出员工中，只要比部门号为10的员工中的任何一个员工的工资高的员工的姓名个工资。也就是说只要比部门号为10的员工中的那个工资最少的员工的工资高就满足条件。

select ename,sal
From emp
Where sal > any(select sal from emp where deptno = 10);

这里推荐用any，如果你非要用some也是没有任何问题的，结果是一样的，只是一般来讲some用在“=”的比较从句中。
select ename,sal
From emp
Where sal > some(select sal from emp where deptno = 10);

上面的用法完全OK的。
select ename,sal
From emp
Where sal = some(select sal from emp where deptno = 30) and deptno not in (select deptno from emp where deptno = 30);

上面才是some的正常用法。其含义是找到和30部门员工的任何一个人工资相同的那些员工。虽然没有找到。
最后一个关键字all的用法也很简单就是要与子查询的每一结果都要匹配。

select ename,sal
From emp
Where sal > all(select sal from emp where deptno = 20);

上面的SQL语句的意义与前面的就完全不一样了，其意义是找到比部门号为20的员工的所有员工的工资都要高的员工，也就是比那个工资最高的员工的还要高的员工
总的来说some和any用法意义是一样的，仅在词法上有不同，都表示对子查询结果集中“或”的比较关系，而all则是对子查询结果集总每一个结果“与”的关系