104_Power Query 数据库条件查询

1、应用场景

底层数据在数据库（sql server数据库，其他数据库同理，下文不再说明。）中，Excel中有查询的字段，需要在数据库中查询相关信息；

2、举个栗子

A、数据库内容

创建测试表：kucunbiao

sql 代码，自己建个测试库，我测试库：kucun

use kucun

create table kucunbiao
(
统计日期	date
,sku		varchar(20)
,库存		int
)

insert into kucunbiao(统计日期,sku,库存)
VALUES
('2018/9/17','1001',	1)
,('2018/9/17','1002',	2)
,('2018/9/17','1003',	3)
,('2018/9/17','1004',	4)
,('2018/9/17','1005',	5)
,('2018/9/16','1001',	10)
,('2018/9/16','1002',	20)
,('2018/9/16','1003',	30)
,('2018/9/16','1004',	40)
,('2018/9/16','1005',	50)

select * from kucunbiao

B、Excel内容

Excel内容

需要从待查询的sku中，在数据库中查询，统计日期为：2019/9/16的库存信息，结果为：查询结果。

3、上pq语句

基本思路，用Sql.Database函数，四个参数分别为：数据库ip地址，数据库名称，sql语句，timeout时长。

构建四个参数。

let
    SKU明细 = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SKU"]}[Content],{{"SKU", type text}})[SKU],
    //取表中的sku
    计数 = List.Count(SKU明细)-1,
    //计数用在后续参数中
    日期 = Text.From(Date.From(DateTime.LocalNow())-#duration(1,0,0,0)),
    //日期查询为2018/9/16,因为是今天所以减去一天，转换为文本用于后续文本连接。
    ip = "127.0.0.1",
    //数据IP地址为本机，注意mysql等需要端口号，sql server 用的默认端口1433
    数据库 = "kucun",
    //测试数据库:kucun
    sql_0 = "select SKU  into #SKULS #(lf)from kucunbiao #(lf)where 1=2 #(lf) #(lf) ",
    //创建临时表
    sql_1 = Text.Combine(List.Transform({0..计数},(X)=> "insert into #SKULS (SKU) values('"& SKU明细{X} &"')"),"#(lf)")&"#(lf) #(lf)",
    //将【SKU明细】中sku写入临时表，sql_2构建完整的sql查询语句。
    sql_2 = "select A.SKU, A.库存
from
(
select SKU,sum(库存) as 库存
from kucunbiao
where 统计日期='"&日期&"' 
group by SKU
) as A
inner join 
(
select SKU  from #SKULS
) as B 
on
A.SKU=B.SKU 
drop table #SKULS",

    sql = sql_0
&sql_1
&sql_2,

    结果 = Sql.Database(ip, 数据库, [Query=sql, CommandTimeout=#duration(0, 0, 10, 0)])
in
    结果

分步图解

分步1：清洗得到sku

分步2：计数-1得到索引

分步3：构建查询日期条件

分步4：构建ip

分步5：构建数据库名称

分步6：构建临时表sql语句

分步7：构建插入临时表数据sql语句

分步8：构建内连接查询sql语句

分步9：组合所有sql语句

分步10：利用Sql.Database查询结果

4、说明

首次使用会有凭据信息填写。

此案例适合小量数据条件查询，大量数据还请直接使用数据库。

思维的重点就是构建，要什么给什么。