为了账号安全,请及时绑定邮箱和手机立即绑定

104_Power Query 数据库条件查询


1、应用场景

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

2、举个栗子

A、数据库内容

104_Power Query 数据库条件查询创建测试表: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内容

104_Power Query 数据库条件查询
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
    结果

分步图解

104_Power Query 数据库条件查询分步1:清洗得到sku

104_Power Query 数据库条件查询分步2:计数-1得到索引

104_Power Query 数据库条件查询

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

104_Power Query 数据库条件查询

分步4:构建ip

104_Power Query 数据库条件查询

分步5:构建数据库名称

104_Power Query 数据库条件查询

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

104_Power Query 数据库条件查询

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

104_Power Query 数据库条件查询

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

104_Power Query 数据库条件查询

分步9:组合所有sql语句

104_Power Query 数据库条件查询

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

4、说明

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

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

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

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消