0.00.0100005050NY50000YN1000100-2019/06/06 11:54:02.561-2019/06/06 11:54:02.561H4sIAAAAAAAAAAMAAAAAAAAAAAA=NMallSqlserver192.168.50.32MSSQLNATIVENativeMall1433saEncrypted 2be98afc819c69e8ea300ff228dd38f99FORCE_IDENTIFIERS_TO_LOWERCASENFORCE_IDENTIFIERS_TO_UPPERCASENIS_CLUSTEREDNMSSQLUseIntegratedSecurityfalseMSSQL_DOUBLE_DECIMAL_SEPARATORNPORT_NUMBER1433PRESERVE_RESERVED_WORD_CASEYQUOTE_ALL_FIELDSNSUPPORTS_BOOLEAN_DATA_TYPEYSUPPORTS_TIMESTAMP_DATA_TYPEYUSE_POOLINGN表输入 2文本文件输出N表输入文本文件输出Y表输入 3文本文件输出Y文本文件输出TextFileOutputY1none;"NNYDOSNoneNYfileNNtxtNNNNNNYNN416144Y表输入TableInputY1noneMallSqlserverselect ROW_NUMBER() over (order by AConsigneePhone2 asc ) as MemberKey,
case when sex= ''then 1 else sex end as MemberGender,
Consignee as MemberNickName,
dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) as MemUsualPhone,--电话号码
case when AConsigneePhone1= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) end as MemPhone1,--
case when AConsigneePhone2= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) end as MemPhone2,--
case when AConsigneePhone3= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone3) end as MemPhone3,--
case when ADeliveryAddress= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',ADeliveryAddress) end as MemUsualAddress,
OriginType,
( select CONVERT(varchar(100), min(b.OrderTime), 20) from orders b where b.AConsigneePhone2=a.AConsigneePhone2
and b.OrderTime!='1800/01/01 00:00:00'
group by b.AConsigneePhone2
)
as RegisterTime,
case when b0.Level= 3 then a.RegionCode else null end as MemUsualAreaCode,
case when b1.Level= 2 then b.ParentCode else b.AreaCode end as MemUsualCityCode,
case when c1.Level= 1 then c.ParentCode else c.AreaCode end as MemUsualProvinceCode
,'true' as HasOrder
,convert(varchar,datepart(year,getdate())) as RegisterYear
,convert(varchar,datepart(month,getdate())) as RegisterMonth
,convert(varchar,datepart(day,getdate())) as RegisterDay
,BuyUserId as MemberId
,OrdersCode
,'Member' as order_join_field
from orders a
left join Areamap b0 on b0.MappingCode=a.RegionCode
left join AreaRegion b on b.AreaCode=a.RegionCode left join Areamap b1 on b1.MappingCode=b.ParentCode
left join AreaRegion c on c.AreaCode=b.ParentCode left join Areamap c1 on c1.MappingCode=c.ParentCode
where 1=1 and (a.AConsigneePhone1!= '')
order by AConsigneePhone2 asc
--offset ((${page}-1)*${pagesize}) rows fetch NEXT (${pagesize}) rows only
offset 0 rows fetch NEXT 10 rows only0NNN20816Y表输入 2TableInputY1noneMallSqlserverselect ROW_NUMBER() over (order by AConsigneePhone2 asc ) as MemberKey,
case when sex= ''then 1 else sex end as MemberGender,
Consignee as MemberNickName,
dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) as MemUsualPhone,--电话号码
case when AConsigneePhone1= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) end as MemPhone1,--
case when AConsigneePhone2= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) end as MemPhone2,--
case when AConsigneePhone3= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone3) end as MemPhone3,--
case when ADeliveryAddress= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',ADeliveryAddress) end as MemUsualAddress,
OriginType,
( select CONVERT(varchar(100), min(b.OrderTime), 20) from orders b where b.AConsigneePhone2=a.AConsigneePhone2
and b.OrderTime!='1800/01/01 00:00:00'
group by b.AConsigneePhone2
)
as RegisterTime,
case when b0.Level= 3 then a.RegionCode else null end as MemUsualAreaCode,
case when b1.Level= 2 then b.ParentCode else b.AreaCode end as MemUsualCityCode,
case when c1.Level= 1 then c.ParentCode else c.AreaCode end as MemUsualProvinceCode
,'true' as HasOrder
,convert(varchar,datepart(year,getdate())) as RegisterYear
,convert(varchar,datepart(month,getdate())) as RegisterMonth
,convert(varchar,datepart(day,getdate())) as RegisterDay
,BuyUserId as MemberId
,'Member' as order_join_field
from orders a
left join Areamap b0 on b0.MappingCode=a.RegionCode
left join AreaRegion b on b.AreaCode=a.RegionCode left join Areamap b1 on b1.MappingCode=b.ParentCode
left join AreaRegion c on c.AreaCode=b.ParentCode left join Areamap c1 on c1.MappingCode=c.ParentCode
where 1=1 and (a.AConsigneePhone2!= '')
order by AConsigneePhone2 asc
offset 0 rows fetch NEXT 10 rows only0NNN20896Y表输入 3TableInputY1noneMallSqlserverselect ROW_NUMBER() over (order by AConsigneePhone2 asc ) as MemberKey,
case when sex= ''then 1 else sex end as MemberGender,
Consignee as MemberNickName,
dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone3) as MemUsualPhone,--电话号码
case when AConsigneePhone1= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) end as MemPhone1,--
case when AConsigneePhone2= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) end as MemPhone2,--
case when AConsigneePhone3= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone3) end as MemPhone3,--
case when ADeliveryAddress= '' then ''
ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',ADeliveryAddress) end as MemUsualAddress,
OriginType,
( select CONVERT(varchar(100), min(b.OrderTime), 20) from orders b where b.AConsigneePhone2=a.AConsigneePhone2
and b.OrderTime!='1800/01/01 00:00:00'
group by b.AConsigneePhone2
)
as RegisterTime,
case when b0.Level= 3 then a.RegionCode else null end as MemUsualAreaCode,
case when b1.Level= 2 then b.ParentCode else b.AreaCode end as MemUsualCityCode,
case when c1.Level= 1 then c.ParentCode else c.AreaCode end as MemUsualProvinceCode
,'true' as HasOrder
,convert(varchar,datepart(year,getdate())) as RegisterYear
,convert(varchar,datepart(month,getdate())) as RegisterMonth
,convert(varchar,datepart(day,getdate())) as RegisterDay
,BuyUserId as MemberId
,'Member' as order_join_field
from orders a
left join Areamap b0 on b0.MappingCode=a.RegionCode
left join AreaRegion b on b.AreaCode=a.RegionCode left join Areamap b1 on b1.MappingCode=b.ParentCode
left join AreaRegion c on c.AreaCode=b.ParentCode left join Areamap c1 on c1.MappingCode=c.ParentCode
where 1=1 and (a.AConsigneePhone3!= '')
order by AConsigneePhone2 asc
offset 196728 rows fetch NEXT 10 rows only0NNN224176YN