qlik sense desktop中,有2个临时表X16,X17,再用这2个表组成X该怎么写
0
LIB CONNECT TO 'Microsoft_SQL_Server_10.3.5.106';
[#X17]:
select a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END LB,SUM(B.hsje)HSJE,SUM(B.ml)ML
--INTO #XS17
from dw_jzt_new.dbo.dw_dwzl a(nolock),dw_jzt_new.dbo.dw_xsmx b(nolock)
left join dw_jzt_new.dbo.dw_spzx d(nolock) on b.fdbs = d.fdbs and b.spid0 = d.spid and d.type_bh ='母婴品种',spkfk2 c(nolock)
where a.fdbs = b.fdbs and a.dwbh = b.dwbh
and b.fdbs = c.fdbs and b.spid = c.jztid and b.spid0 = c.spid
and b.rq between '2017-01-01' and '2017-02-15'
and a.danwbh not like '[JKN]%'
GROUP BY a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END ;
NoConcatenate [#X16]:
select a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END LB,SUM(B.hsje)HSJE,SUM(B.ml)ML
--INTO #XS16
from dw_jzt_new.dbo.dw_dwzl a(nolock),dw_jzt_new.dbo.dw_xsmx b(nolock)
left join dw_jzt_new.dbo.dw_spzx d(nolock) on b.fdbs = d.fdbs and b.spid0 = d.spid and d.type_bh ='母婴品种',spkfk2 c(nolock)
where a.fdbs = b.fdbs and a.dwbh = b.dwbh
and b.fdbs = c.fdbs and b.spid = c.jztid and b.spid0 = c.spid
and b.rq between '2016-01-01' and '2016-02-15'
and a.danwbh not like '[JKN]%'
GROUP BY a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END ;
[#X]:
load
fdbs,
dwbh,
LB ,
HSJE as HSJE17,
HSJE as HSJE16
Resident [#X16] ; left join
Load
fdbs,
dwbh,
LB ,
HSJE as HSJE17,
HSJE as HSJE16
Resident [#X17] ;
[#X17]:
select a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END LB,SUM(B.hsje)HSJE,SUM(B.ml)ML
--INTO #XS17
from dw_jzt_new.dbo.dw_dwzl a(nolock),dw_jzt_new.dbo.dw_xsmx b(nolock)
left join dw_jzt_new.dbo.dw_spzx d(nolock) on b.fdbs = d.fdbs and b.spid0 = d.spid and d.type_bh ='母婴品种',spkfk2 c(nolock)
where a.fdbs = b.fdbs and a.dwbh = b.dwbh
and b.fdbs = c.fdbs and b.spid = c.jztid and b.spid0 = c.spid
and b.rq between '2017-01-01' and '2017-02-15'
and a.danwbh not like '[JKN]%'
GROUP BY a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END ;
NoConcatenate [#X16]:
select a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END LB,SUM(B.hsje)HSJE,SUM(B.ml)ML
--INTO #XS16
from dw_jzt_new.dbo.dw_dwzl a(nolock),dw_jzt_new.dbo.dw_xsmx b(nolock)
left join dw_jzt_new.dbo.dw_spzx d(nolock) on b.fdbs = d.fdbs and b.spid0 = d.spid and d.type_bh ='母婴品种',spkfk2 c(nolock)
where a.fdbs = b.fdbs and a.dwbh = b.dwbh
and b.fdbs = c.fdbs and b.spid = c.jztid and b.spid0 = c.spid
and b.rq between '2016-01-01' and '2016-02-15'
and a.danwbh not like '[JKN]%'
GROUP BY a.fdbs ,a.dwbh , case when ISNULL(d.spid,'')!='' then 'M'
when c.spbh like 'z%' then 'Z'
WHEN C.spbh LIKE '[JP]%' THEN 'JP'
WHEN C.spbh LIKE 'DY[^Y]%' THEN 'DY'
ELSE 'X' END ;
[#X]:
load
fdbs,
dwbh,
LB ,
HSJE as HSJE17,
HSJE as HSJE16
Resident [#X16] ; left join
Load
fdbs,
dwbh,
LB ,
HSJE as HSJE17,
HSJE as HSJE16
Resident [#X17] ;
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
0 个回复