SQL

SQL编辑器

目录

1.指定数据来源和去向 2.引用单元格
3.语句分隔符 4.注释
5.定义变量 6.引用变量
7.定义语句体 8.引用语句体
9.字符替换 10.if条件判断
11.for循环 12.while循环
13.单元格操作 14.单元格转字符串
15.引用VBA 16.运行VBA

功能按钮

功能按钮

在工作中,我们常会做一些周期性的或经常性的报表。对于这样的报表,最好是把它开发成固定的模板。从而可以大幅节省时间来做更多更有价值的事情。

同时对于经常写SQL语句的人来说,当需求特别复杂的时候,一个SQL语句可能会写几百行,从而让人感到心力交瘁。

为了解决以上问题,SqlCel的SQL语句编辑器做了一些功能上的改善和提高。

SqlCel的SQL语句编辑器提升了Excel和数据库的交互水平;为固定报表的开发提供了一个高效的工具;同时它可以让SQL语句的编写变得简洁高效。最后作为SQL语句编辑器,它具备基本的高亮显示,智能提示及查找替换等功能。

SQL编辑器界面如下:

sqlcelquery

SqlCel可向数据库提交常规的SQL语句,为了提升数据处理能力,SQL编辑器对SQL语句做了大量扩展。

需注意的是目前SqlCel的SQL语句编辑器对WPS的支持性不佳,如用WPS表格使用SQL语句编辑器,建议点击DBOPT工作表的E1单元格(SQL查询)调出SQL编辑器。

SQL语法

1. 可在SQL语句中指定查询的数据源和查询结果的存放位置,语法格式如下:

----------------查询结果传入工作表-----------------;
数据源!连接名  -> SQL语句 -> 工作表名称!单元格地址

--------------查询结果及字段传入工作表--------------;
数据源!连接名  -> SQL语句 ->> 工作表名称!单元格地址

“数据源”仅可使用以下四个字符串(不区分大小写):

  • Mysql:表示数据源来自Mysql
  • Sql:表示数据源来自SqlServer
  • Oracle:表示数据源来自Oracle
  • Folder:表示数据源来自文件夹,其后不需要指定连接名

举例:

MYSQL!MyFirstMySqlConn  -> SELECT * FROM TableName ->> Sheet1!A1

以上语句表示在MySQL连接中寻找连接名"MyFirstMySqlConn"并建立连接,然后通过该连接向数据库提交SQL语句"SELECT * FROM TableName",最后将查询结果及字段从Sheet1工作表的A1单元格向右向下写入。

以上语句如果省略了MySQL,如下:

MyFirstMySqlConn  -> SELECT * FROM TableName ->> Sheet1!A1

则寻找当前连接的数据库下的"MyFirstMySqlConn"连接名,建立连接并提交SQL语句。

以上语句如果省略了连接名,如下:

SELECT * FROM TableName ->> Sheet1!A1

则将SQL语句提交给当前连接,并将查询结果及字段传送到Sheet1工作表的A1单元格。
如果数据传送符号使用 "->" 则只传送查询的结果;
如果数据传送符号使用 "->>" 则不仅传送查询的结果同时传送字段。

以上语句如果省略了查询结果的指向,如下:

SELECT * FROM TableName

则将SQL语句提交给当前连接,并将查询结果及字段输出到"QueryTable"工作表的A1单元格,如果当前活动工作簿不存在"QueryTable"工作表则会新建"QueryTable"工作表,如果有这个表则会清空并将结果及字段从A1单元格写入。

如果SQL语句没有查询结果,比如提交的是UPDATE语句,则不做输出。

注意:

如果直接引用表名则默认引用的是当前连接的数据库中的表,如果需要在SQL语句中跨数据库引用表则需在表名前加上数据库名,规则如下:

  1. MySQL

对于MySQL数据库如果需要跨库引用表名,语法为

库名.表名
  1. SQL Server

对于SQL Server数据库如果需要跨库引用表名,语法为

库名.dbo.表名

2. 引用单元格

(1) [A1]格式
可以通过[A1]格式引用单元格。如下:

SELECT * FROM [A1]

表示将当前活动工作表的A1单元格的值传入SQL语句。

SELECT * FROM [Sheet1!A1] WHERE PRODUCT_ID ='[Sheet1!A3]'

表示将Sheet1工作表的A1和A3单元格的值传入SQL语句。

(2) [1,1]格式引用单元格。如下:

SELECT * FROM [1,1]

表示将当前活动工作表的A1单元格的值传入SQL语句。中括号中前面一个1表示行的序号,后面一个1表示列的序号。

SELECT * FROM [Sheet1!1,1] WHERE PRODUCT_ID ='[Sheet1!3,1]'

表示将Sheet1工作表的A1和A3单元格的值传入SQL语句。

3. 语句分隔符(英文分号)

SQL编辑器用英文分号作为分隔符,将语句分开成一个个片段。不同的片段都是独立的语句体,可单独执行。

所有分段从上到下逐个执行,直至结束。

4. 注释

注释以英文的双横线开始并以英文分号结束。如下:

--*************************定义直播客群***************************;

5. 定义变量

SQL编辑器支持自定义变量。可以通过以下两种方法定义变量。
(1) var x; 或 var x=0;
var x; 仅定义一个空的变量x。
var x=0; 则在定义变量x的同时,为变量x赋值为0。
(2) &x =0;
变量名需紧跟在&符号之后不能有空格。
在给变量赋值的时候,如无特别需要等号右边不要紧跟空格,因为从等号右边开始至分号结束之前的值都会赋值给变量。
比如 &x=a;给变量x赋值为"a",&x= a;给变量x赋值为" a"。

定义变量需以分号结尾。变量定义完成后,可在全局引用。

6. 引用变量

用&符号紧跟变量名引用变量,如下:

&x =202103;
select distinct user_id from live_table 
where month_no = &x -> Sheet1!A1;

7. 定义语句体

在写SQL语句的时候,为了让SQL语句的逻辑层次更加清晰,代码更加简洁,同时为了提高代码书写效率。SQL编辑器引入了定义语句体和引用语句体的功能。通过这两个功能可以简化复杂的业务逻辑。

定义语句体的语法格式如下:

def 直播客户 as(
    select distinct user_id from live_table where month_no = 202103
);

def 点播客户 as(
    select distinct user_id from vod_table where month_no = 202103
);

定义语句体以关键字"def"开始,并以分号结束。关键字"as"不要省略。小括号里面是真正的语句体。 需注意的是语句体里面不可以使用分号作为分隔符

语句体可以在任何地方定义,定义语句体和引用语句体不分先后顺序。可以将语句体定义在引用位置的上方也可以定义在引用位置的下方。

8. 引用语句体

定义语句体的目的是为了引用。引用语句体可通过关键字"ref"来实现。如下:

def 即直播又点播客户 as(
select a.user_id from
    (ref(直播客户)) a
inner join
    (ref(点播客户)) b
on a.user_id=b.user_id
);

ref(即直播又点播客户)->> Sheet1!A1;

以上语句和下面两个语句的效果相同:

select a.user_id from
    (ref(直播客户)) a
inner join
    (ref(点播客户)) b
on a.user_id=b.user_id ->> Sheet1!A1;
select a.user_id from
    (select distinct user_id from live_table where month_no = 202103) a
inner join
    (select distinct user_id from vod_table where month_no = 202103) b
on a.user_id=b.user_id ->> Sheet1!A1;

我们可以看到,以上三个SQL语句虽然效果相同,但是第一个SQL语句的逻辑层次非常清晰。而第三个SQL语句就具有比较大的阅读障碍。而在实际工作中,业务逻辑往往要复杂得多,通过定义语句体和引用语句体就可以比较清楚地理清思路。

以上定义的一个语句体比如“直播客户”,在后续的数据分析中可能会反复引用以达到对“直播客户”深入分析的目的,此时通过ref(直播客户)就可以引用这段代码从而可以省却很多代码的书写量,让代码变得简洁易懂。

引用语句体本质上是拼接字符串,通过引用语句体将之前定义的字符串拼接到引用的位置。因此,引用语句体也可以通过定义变量和引用变量实现。比如上述SQL语句也可以通过以下方式达到同样的效果。

var 直播客户 =(select distinct user_id from live_table where month_no = 202103);
var 点播客户 =(select distinct user_id from vod_table where month_no = 202103);

select a.user_id from
    &直播客户 a
inner join
    &点播客户 b
on a.user_id=b.user_id ->> Sheet1!A1;

上述"var"也可以用"&"符号代替,比如 "var 直播客户"也可以写成"&直播客户"。

用定义变量和引用变量的方式(变量方式)看起来更简洁。

9. 字符替换

replace() 函数用来将SQL语句里的一个字符串替换成另一个字符串。这在做环比的时候非常实用。环比一般只需将时间替换成上一个周期即可。
比如以下SQL语句:

def 直播客户 as(
    select distinct user_id from live_table where month_no = [sys!A1]
);

def 点播客户 as(
    select distinct user_id from vod_table where month_no = [sys!A1]
);

def 即直播又点播用户 as(
select a.user_id from
(ref(直播客户))a
inner join
(ref(点播客户))b
on a.user_id=b.user_id
);

--------输出即直播又点播用户-------------------------;
ref(即直播又点播用户) ->> Sheet1!A1;
--------替换月份后输出即直播又点播用户----------------;
ref(即直播又点播用户).REPLACE({[sys!A1]},{[sys!C1]}) ->> Sheet1!C1;

replace()函数的参数需用大括号括起来,第一个参数为待替换的字符,第二个参数为替换后的字符。

10. if条件判断

编辑器支持if过程控制,举例如下:

if([sys!a1]>=20210301)
    select count(1) from live_table where date_no>=[sys!a1] -> [Sheet1!A1048576].end[xlup].offset[1,0]
;

if([sys!a1]<20210301)
    select count(1) from live_table where date_no<[sys!a1] -> [Sheet1!A1048576].end[xlup].offset[1,0]
;

if判断以if开头,以分号结尾,是一个独立的语句体。小括号里面是条件判断语句。
IF 语句体里面可以有多条语句,多条语句用回车键分割,逐条执行,需注意的是循环体里面不可以使用分号。
在if语句体里面不可以随意使用回车键,因为它会被当作语句分隔符,如果一条语句过长需要换行需用下划线("_")作为换行连接符。 也可以通过定义语句体或者定义变量解决。比如以上语句也可以修改如下:

(1) 使用连接符号换行

if([sys!a1]>=20210301)
    select count(1) from live_table _
        where date_no>=[sys!a1] _
        -> [Sheet1!A1048576].end[xlup].offset[1,0]
;

(2) 使用定义语句体和引用语句体换行

if([sys!a1]>=20210301)
    ref(直播播放次数) -> [Sheet1!A1048576].end[xlup].offset[1,0]
;

def 直播播放次数 as(
    select count(1) from live_table 
        where date_no>=[sys!a1] 
);

需注意的是,仅有if条件判断没有else if,也没有else。每次做出判断都要以if开头,以分号结尾,形成一个独立的语句体。

11. for循环

编辑器支持for循环,举例如下:

&x=0;
&y=0;
for(v,1,100,2)
	&x=&x+&v
	&y=&y+&v+1
;
------输出100以内所有奇数的和-------;
[a1].value=&x;
------输出100以内所有偶数的和-------;
[a2].value=&y;

for循环以for开头,以分号结尾,是一个独立的语句体。for循环有四个参数,每个参数的解释如下:
for(变量名,起始循环值,结束循环值,步长)
此处变量名的定义无需&符号,虽然变量名定义在for循环体里面,但它仍可作用于全局。
第四个参数"步长"如果省略则默认为1。
for循环体里面可以有多条语句,多条语句用回车键分割,需注意的是循环体里面不可以使用分号。
在for循环体里面不可以随意使用回车键,因为它会被当作语句分隔符,如果一条语句过长可通过定义语句体和引用语句体解决或者使用换行连接符("_")换行。

for循环定义的变量名可在def定义的语句体里面引用。

举例如下:

def 每日直播用户数 as (
    select count(distinct user_id) from live_table 
    where date_no = &v
);

for(v,20210301,20210331,1)
    [Sheet1!A1048576].End[xlup].offset[1,0].value = &v
    ref(每日直播用户数)->[Sheet1!B1048576].End[xlup].offset[1,0]
;

以上语句用于统计2021年3月1日到31日每天的直播用户数,并从Sheet1工作表的A1单元格逐天向下写入。

12. while循环

编辑器支持while循环,举例如下:

----在第一列添加1到100的行号-------;
&x=1;
while(&x<=100)
	[Sheet1!&x,1].value=&x
	&x=&x+1
;

while循环以while开头,以分号结尾,是一个独立的语句体。

while循环体里面可以写多条语句,多条语句用回车键分割,需注意的是循环体里面不可以使用分号。

循环体里面的换行方式同上。

13. 单元格操作

(1) 移动单元格

  • [订购!D10].Offset[1,2];
    表示从” 订购”工作表的D10单元格向下偏移一个单元格,向左偏移两个单元格。第一个参数表示向下偏移单元格个数,第二个参数表示向左偏移单元格个数。

  • [订购!D10].End[XlUp];
    表示从“订购”工作表的D10单元格向上查找到第一个非连续(空或非空)单元格,如果没有非连续单元格则定位到D1。

  • [订购!D10].End[XlDown];
    表示从“ 订购”工作表的D10单元格向下查找到第一个非连续(空或非空)单元格,如果没有非连续元格则定位到D列的最后一个单元格。

  • [订购!D10].End[XlToLeft];
    表示从“ 订购”工作表的D10单元格向左查找到第一个非连续(空或非空)单元格,如果没有非连续单元格则定位到A10。

  • [订购!D10].End[XlToRight];
    表示从“ 订购”工作表的D10单元格向右查找到第一个非连续(空或非空)单元格,如果没有非连续单元格则定位到第10行的最后一个单元格。

需特别说明的是以上移动单元格操作只能在给单元格赋值的时候可以用,引用单元格的时候不可以这样操作。比如以下两个语句是正确的用法:

--直接给单元格赋值;
[a1048576].end[xlup].offset[2,0].value = 10;

--通过SQL语句给单元格赋值;
SELECT * FROM onlive_table LIMIT 100 ->> [a1048576].end[xlup].offset[2,0];

如下通过移动单元格的方式在SQL语句中引用单元格却不可以:

SELECT * FROM [sys!A1].offset[1,0];

(2) AutoFit()

  • [订购!A:Z].AutoFit();
    自动调整” 订购”工作表A到Z列的列宽。
  • [订购!1:100].AutoFit();
    自动调整” 订购”工作表第1到100行的行高。

(3) 赋值单元格

[订购!A1].Value = 2021-02-01;
表示给名为“订购”的工作表的A1单元格赋值为“2021-02-01”,此处“.Value”不可省略。

(4) 清空单元格

[订购!A:B].Clear();
中括号内部表示Excel单元格的引用位置“.Clear()”表示清空该位置的所有内容及格式。以分号结尾,表示该条语句结束。

[订购!A:B].ClearContents();
表示清空单元格的内容。

[订购!A:B].ClearFormats();
表示清空单元格的格式。

(5) 激活工作表
[订购].Activate();
激活名为“订购”的工作表。

14. 单元格转字符串(RNGTOSTR函数)

RNGTOSTR函数可以非常快速地将单元格的值转为拼接字符串。其语法如下:
rngtostr(连续的单元格区域,分隔符,是否横向连接)
第一个参数不可省略,后面两个参数可以省略。第二个参数默认为空,第三个参数是布尔型默认为true

举例,以下是Sheet1工作表A1:B3单元格区域的数据:

A B
1 A A
2 B B
3 C C

'rngtostr([Sheet1!A1:B3],'\,',true)' ,最后一个参数为true,表示横向连接,得到的结果为
'A','A','B','B','C','C'

'rngtostr([Sheet1!A1:B3],'\,',false)' 最后一个参数为false,表示纵向连接,得到的结果为
'A','B','C','A','B','C'

当分隔符中包含逗号时,需在逗号前添加反斜线作为转义字符,除了逗号其它任何符号都不需要使用转义符。

rngtostr函数最常用在SQL语句的IN后面举例如下:

SELECT * FROM SALES_TABLE 
WHERE PRODUCT_CODE IN ('rngtostr([sys!A1:A100],'\,')')
->> Sheet1!A1

15. 引用VBA

当我们需要对数据或者表格做比较复杂的处理时,使用SQL语句很难实现,此时调用VBA就会变得灵活方便。

有了VBA的加入,会让编辑器的开发能力变得很强大。但是多数情况下,我们不需要调用VBA。因为编辑器的其它功能加上Excel的模板及公式即可满足大部分开发需求,而这些技能的掌握难度也会小很多。

为了便于理解,现在举一个具体的例子进行讲解。

比如数据库中有一个表sales_table,它有三个字段car_brand(品牌),price(单价),sale_date(销售日期),如下:

品牌 单价 销售日期
Ford 30 2021/1/18
Hummer 35 2021/1/27
BMW 51 2021/1/8

现在需要统计在一定时间段内每种汽车品牌的销售额占比,占比用百分比形式展现,显示两位小数。所限制时间段的起始日期和结束日期引用sys的工作表的B1和B2单元格的值。如下:

A B
1 起始日期 20210301
2 结束日期 20210315
3

我们发现Excel单元格的日期格式是整数型,而数据库中为日期型,用SQL将整数型转为日期型比较麻烦,因此此处用VBA来处理。分组统计每种产品的销售额比较简单,但是统计每种产品的销售额占总体的比例比较麻烦,因此此处我们也用VBA处理。设置比例显示格式也用VBA实现。

(1) 定义VBA语句体

定义VBA语句体的方法和定义其它语句体相同,都是用def开头进行定义。

def dupwithVBA as(
    Function transDate(dt As String) As String
        transDate = Left(dt, 4) & "-" & Mid(dt, 5, 2) & "-" & Right(dt, 2)
    End Function
    
    Sub ComputePercent(shtName As String, beginCellAdd As String)
	    Dim initCell As Range, sht As Worksheet, lastCell As Range
	    Set sht = Worksheets(shtName)
	    Set initCell = Worksheets(shtName).Range(beginCellAdd)
	    Set lastCell = sht.Cells(1048576, initCell.Column).End(xlUp)
	    Dim totalIncome As Double
	    totalIncome = WorksheetFunction.Sum(sht.Range(initCell.Offset(0, 1), lastCell).Offset(0, 1))
	    Dim arow As Long
	    For arow = initCell.Row + 1 To lastCell.Row
	        sht.Cells(arow, initCell.Column + 2) = _
	            sht.Cells(arow, initCell.Column + 1) / totalIncome
	    Next arow
	    sht.Range(initCell.Offset(0, 2), lastCell).Offset(0, 2).NumberFormatLocal = "0.00%"
	End Sub
);

上面定义的语句体名称为dupwithVBA,它定义了一个函数transDate和一个方法ComputePercent。transDate函数用于将整数型日期转为yyyy-MM-dd格式日期。ComputePercent方法可传入工作表名称和单元格地址两个参数用于计算百分比并设置显示格式。

(2) 引用VBA语句体

定义VBA语句体的目的是为了引用。引用方式如下:

select car_brand,sum(price) as income from sales_table
where sale_date>='refvba(dupwithVBA.transDate,[sys!b1])' 
and sale_date<='refvba(dupwithVBA.transDate,[sys!b2])'
group by car_brand
order by income desc ->> Sheet1!A1;

refvba(dupwithVBA.ComputePercent,Sheet1,A1);

上述代码用分号隔开分成两个片段。第一段是一个SQL语句,里面使用refvba函数引用了上面定义的VBA代码。refvba函数的语法如下:

refvba(查询文件名称.定义的语句体名称.VBA过程名称或VBA函数名称,参数1,参数2,参数3,参数4,参数5)

查询文件名称如省略表示当前查询文件,refvba引用的VBA过程或函数可以支持0-5个参数。当引用的是VBA函数时,会有返回值,返回值可直接拼接到SQL语句中。

第二段代码只用了一个refvba函数,用于执行定义的VBA语句。

16. 运行VBA

对于当前活动工作簿中已有的VBA函数或过程可以通过RUN()函数直接引用。如有返回值也可以直接拼接到SQL语句中。引用已有的VBA函数或过程的语法如下:

RUN(VBA过程名称或函数名称,参数1,参数2,参数3,参数4,参数5);

第一个参数为VBA过程名称或函数名称,可支持0-5个参数。

功能按钮

1. 保存(S)

点击“保存”按钮或者按快捷键Ctrl+S可保存将当前编辑的查询语句。 编辑的查询语句保存在SqlCel安装根目录下的 SqlCelAddIn.dll.config 文件中。SQL编辑器打开的时候会自动读取这个文件中的SQL语句。

复制文件 SqlCelAddIn.dll.config 可以将SQL语句保存到一个新的位置。需要使用的时候再复制到安装根目录下。

2. 新建(N)

点击“新建”按钮或者按住Ctrl+N可弹出新建查询对话框,输入新的查询名称即可创建一个空白的查询。

3. 查找(F)

点击“查找”按钮或者按住Ctrl+F可弹出查找对话框。该功能向下查找字符。

4. 替换(H)

点击“替换”按钮或者按住Ctrl+H可弹出替换对话框。该功能替换整个文档中的字符。

5. 字体

用于设置编辑器字体的大小。

6. 解析SQL

因为编辑器中的查询语句可引用Excel单元格的值,可以用ref()函数引用语句体,因此在SqlCel的SQL编辑器中编写的SQL语句往往不能在其它编辑器中使用。而“解析SQL”功能可以将编辑器中的SQL语句解析成常规的可执行的SQL语句。

选中一段SQL语句,然后点击“解析SQL”按钮,即可解析选中的语句。如果没有选中任何语句,则解析当前文档中的所有语句。

7. 停止

因为SQL编辑器可以一次性提交很多SQL语句。但是有时候我们由于种种原因不想让SQL语句继续执行下去了。此时可以点击“停止”按钮停止执行提交的SQL语句。

需要注意的是“停止”按钮并不能立刻停止所有SQL语句,它会继续将当前执行的SQL语句执行完毕,之后停止后续的所有SQL语句。

8. 开始查询(Q)

点击“开始查询”或者按住Ctrl+Q或Ctrl+Enter即可执行选中的SQL语句,如果没有选中任何SQL语句或者选中的字符数少于5个,则会从上到下顺序执行当前文档中的所有语句。

9. 删除查询

双击左侧查询语句的名称将弹出是否删除查询对话框,点击“是”按钮将删除所选的查询。

10. 自动录入

双击左上角的表名或字段名可自动将对应的表命或字段名录入编辑器。

11. 智能提示

智能提示功能可智能显示关键字和数据库中的表和字段对象。通过tab键或回车键可快速录入选中的文本。

SQL查询

play

SQL查询偏向于对当前选中表的查询操作,其提交的SQL语句中需包含当前查询的表名或用THISTABLE替代这个表名。

选中一个待查询的表,在其后的G列对应的单元格输入查询语句,然后点击该SQL语句左边的“SQL查询”或G1单元格的“查询当前表”,则将该SQL语句提交给对应数据库。如下所示:

sqlquery

如果提交的是SELECT、SHOW等有执行结果的SQL语句,则将该结果展现在一个新的工作表中。否则会提示“n行受影响”。如果SQL语句中对查询结果指定了具体的存放位置, 则会将执行结果存放在指定的位置。