外理多值查询,同时相关业务的复杂处理

原创 2007年09月15日 11:50:00

-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
/*
0:'2007-08-06 00:00:00'
1:b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
2:ShopCode > '005' and StorageCode > '005.01' and PlaceCode > '2001' and BrandCode > '01.01' and ModelCode > '01.01.1110'
3:b.TheDate>'2007-08-06 00:00:00' and b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
4:b.TheDate>'2007-08-06 00:00:00' and b.ShopCode > '005' and a.StorageCode > '005.01' and a.StorPlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
5:b.TheDate>'2007-08-06 00:00:00' and b.ShopInCode > '005' and a.RedeployInStorage > '005.01' and a.RedeployInPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
6:b.TheDate>'2007-08-06 00:00:00' and c.ShopCode > '005' and b.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
7:b.TheDate>'2007-08-06 00:00:00' and b.ShopOutCode > '005' and a.RedeployOutStorage > '005.01' and a.RedeployOutPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
8:b.TheDate>'2007-08-06 00:00:00' and c.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
9:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
10:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopCode > '005' and a.StorageCode > '005.01' and a.StorPlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
11:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopInCode > '005' and a.RedeployInStorage > '005.01' and a.RedeployInPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
12:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and c.ShopCode > '005' and b.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
13:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and b.ShopOutCode > '005' and a.RedeployOutStorage > '005.01' and a.RedeployOutPlace > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
14:(b.TheDate > @StartDate and b.TheDate <= '2007-08-06 00:00:00') and c.ShopCode > '005' and a.StorageCode > '005.01' and a.PlaceCode > '2001' and z.BrandCode > '01.01' and z.ModelCode > '01.01.1110'
-------------------------
TheDate>'2007-08-06 00:00:00' and ShopCode > '005' and StorageCode > '005.01' and PlaceCode > '2001' and BrandCode > '01.01' and ModelCode > '01.01.1110'


b.TheDate;b.TheDate;b.TheDate;b.TheDate;b.TheDate;b.TheDate

b.ShopCode;b.ShopCode;b.ShopInCode;c.ShopCode;b.ShopOutCode;c.ShopCode

a.StorageCode;a.StorageCode;a.RedeployInStorage;b.StorageCode;a.RedeployOutStorage;a.StorageCode

a.PlaceCode;a.StorPlaceCode;a.RedeployInPlace;a.PlaceCode;a.RedeployOutPlace;a.PlaceCode

z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode;z.BrandCode

z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode;z.ModelCode
-------------------------------------------------------------
c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode
b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode 9
------------------------------------------------------------
select max(TheDate) from tbCourseStorStorage --?会不会出现2007-08-20 19:9:00  2007-08-20-10:00
*/
/*-------------------------------------------*/
|功能描述:门店营业日报表
|参数说明:
|  编写人:Kern
|编写时间:2007-08-19
|    备注:
|  修改人:szj 2007-08-24
|  增加  :仓库,库位,品牌,型号过滤,
/*-------------------------------------------*/

declare @StartDate datetime
set @StartDate=isnull((select max(TheDate) from tbCourseStorStorage where TheDate<{0}), '1900-1-1')


 select aa.*
 into #aa
                from
 (
  --结存
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0 InNumber, 0 OutNumber, 0 CheckNumber, 0 SaleNumber, 0 SaleMoney
  from tbCourseStorStorage a left outer join vDatumStorageShop b on a.StorageCode=b.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where TheDate=@StartDate and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {1}
  
 ----期初
  union all

  --销售开单
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,-a.Number, 0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbSelfSaleOutItem a left outer join tbSelfSaleOut b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {9}
  union all
 
  --联盟开单
  select b.ShopCode,a.StorageCode,a.StorPlaceCode,z.BrandCode,z.ModelCode,-a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbSelfLeagueSaleItem a left outer join tbSelfLeagueSale b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {10}
  union all
 
  --调拨入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, a.Number, 0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {11}
  union all
  
  --配送入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {11}
  union all


  --外购入库单
  select c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorStockInItem a
  left outer join tbStorStockIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'ZGRKCSH' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {14}
  union all

  --其它入库单
  select c.ShopCode,a.StorageCode, a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorOtherInItem a
  left outer join tbStorOtherIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'DKHX' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {14}
  union all


 
  --盘点
  select c.ShopCode,b.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorCheckItem a
  left outer join tbStorCheck b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on b.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and c.ShopCode in {{ShopGroup}} and b.StorageCode in {{StorageGroup}} {12}
  union all
 
  --调拨出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, -a.Number,0 StockInNumber,0 OtherInNumber,0, 0, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {13}
  union all
  
  --配送出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, -a.Number,0 StockInNumber,0 OtherInNumber, 0, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {13}
             )aa

 select aa.*
                into #bb
 from
 (

 --本日调入
 
  --调拨入库
  select b.ShopInCode ShopCode,a.RedeployInStorage StorageCode,a.RedeployInPlace Placecode,z.BrandCode,z.ModelCode, 0 Number,0 StockInNumber,0 OtherInNumber, a.Number InNumber, 0 OutNumber, 0 CheckNumber, 0 SaleNumber, 0 SaleMoney
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {5}
  union all
 
  --配送入库
  select b.ShopInCode,a.RedeployInStorage,a.RedeployInPlace,z.BrandCode,z.ModelCode, 0,0,0, a.Number, 0, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.UseState,0x0010)=1 and b.ShopInCode in {{ShopGroup}} and a.RedeployInStorage in {{StorageGroup}} {5}
  union all
        --2007-08-24 增加外购入库StockInNumber,其它入库数量OtherInNumber
  --外购入库单
  select c.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,0, a.Number StockInNumber,0, 0, 0, 0, 0, 0
  from tbStorStockInItem a
  left outer join tbStorStockIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'ZGRKCSH' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {8}
  union all

  --其它入库单
  select c.ShopCode,a.StorageCode, a.PlaceCode,z.BrandCode,z.ModelCode,0,0,a.Number OtherInNumber, 0, 0, 0, 0, 0
  from tbStorOtherInItem a
  left outer join tbStorOtherIn b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on a.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.AssistantCode<>'DKHX' and c.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {8}
  union all
 
 --本日调出
 
  --调拨出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, 0,0,0, 0, a.Number, 0, 0, 0
  from tbStorRedeployItem a
  left outer join tbStorRedeploy b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {7}
  union all
  
  --配送出库
  select b.ShopOutCode,a.RedeployOutStorage,a.RedeployOutPlace,z.BrandCode,z.ModelCode, 0,0,0 , 0, a.Number, 0, 0, 0
  from tbStorRedeployLeagueItem a
  left outer join tbStorRedeployLeague b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopOutCode in {{ShopGroup}} and a.RedeployOutStorage in {{StorageGroup}} {7}
  union all
 
 --本日盈亏数量
 
  --盘点
  select c.ShopCode,b.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode, 0,0,0, 0, 0, a.Number, 0, 0
  from tbStorCheckItem a
  left outer join tbStorCheck b on a.BillNo=b.BillNo
  left outer join vDatumStorageShop c on b.StorageCode=c.TheCode
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and c.ShopCode in {{ShopGroup}} and b.StorageCode in {{StorageGroup}} {6}
  union all
 
  --本日销售数量金额
  
  --销售开单
  select b.ShopCode,a.StorageCode,a.PlaceCode,z.BrandCode,z.ModelCode,0,0,0 , 0, 0, 0, a.Number, a.TotalMoney
  from tbSelfSaleOutItem a left outer join tbSelfSaleOut b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {3}
  union all
 
  --联盟开单
  select b.ShopCode,a.StorageCode,a.StorPlaceCode,z.BrandCode,z.ModelCode, 0,0,0, 0, 0, 0, a.Number, a.TotalMoney
  from tbSelfLeagueSaleItem a left outer join tbSelfLeagueSale b on a.BillNo=b.BillNo
    left outer join vDatumProduct z on a.ProductSortCode=z.ProductSort and a.ProductCode=z.TheCode
  where dbo.GetByteToBoolean(b.TheState,0x0001)=1 and b.ShopCode in {{ShopGroup}} and a.StorageCode in {{StorageGroup}} {4}
 )aa

 

select aaa.ShopCode,bbb.TheName ShopName,aaa.StorageCode,eee.TheName StorageName,aaa.PlaceCode,fff.StorPlaceName PlaceName,
aaa.BrandCode,ccc.TheName BrandName,aaa.ModelCode,ddd.TheName ModelName,aaa.Number BeginNumber,aaa.StockInNumber,aaa.OtherInNumber,aaa.InNumber,aaa.OutNumber,aaa.CheckNumber,aaa.SaleNumber,
aaa.SaleMoney,aaa.EndNumber
from
(
 select aa.ShopCode,aa.StorageCode,aa.PlaceCode,aa.BrandCode,aa.ModelCode,sum(isnull(Number, 0)) as Number,sum(isnull(StockInNumber, 0)) as StockInNumber,sum(isnull(OtherInNumber, 0)) as OtherInNumber,sum(isnull(InNumber, 0)) as InNumber,
  sum(isnull(OutNumber, 0)) as OutNumber,sum(isnull(CheckNumber, 0)) as CheckNumber,sum(isnull(SaleNumber, 0)) as SaleNumber,
  sum(isnull(SaleMoney, 0)) as SaleMoney, 
               (sum(isnull(Number, 0)) +sum(isnull(StockInNumber,0))+sum(isnull(OtherInNumber,0))+ sum(isnull(InNumber, 0))+ - sum(isnull(OutNumber, 0)) + sum(isnull(CheckNumber, 0)) - sum(isnull(SaleNumber, 0))) EndNumber
 from
 (
          select #aa.* from #aa
          union all
          select #bb.* from #bb
 ) aa group by aa.ShopCode,aa.StorageCode,aa.PlaceCode,aa.BrandCode,aa.ModelCode
) aaa left outer join tbDatumShop bbb on aaa.ShopCode=bbb.TheCode
left outer join (select TheCode,TheName from tbDatumProductBrand) ccc on aaa.BrandCode=ccc.TheCode
left outer join (select TheCode,TheName from tbDatumProductModel) ddd on aaa.ModelCode=ddd.TheCode
left outer join (select TheCode,TheName from tbDatumStorage) eee on aaa.StorageCode=eee.TheCode
left outer join tbDatumStorageItem fff on aaa.StorageCode=fff.StorageCode and aaa.PlaceCode=fff.StorPlaceCode
Order by aaa.ShopCode,aaa.StorageCode,aaa.PlaceCode,aaa.BrandCode,aaa.ModelCode
--删除临时表
drop table #aa
drop table #bb 

mybatis查询有关联关系并且有相同字段的两张表问题解决

mybatis查询有关联关系并且有相同字段的两张表问题解决
  • fqf_520
  • fqf_520
  • 2016年01月18日 13:23
  • 4147

MyBatis关联查询,表字段相同,resultMap映射问题的解决办法

问题描述:在使用mybatis进行多表联合查询时,如果两张表中的字段名称形同,会出现无法正常映射的问题。 问题解决办法:在查询时,给重复的字段 起别名,然后在resultMap中使用别名进行映射。 给...
  • zhaofuwu
  • zhaofuwu
  • 2016年08月23日 15:29
  • 6870

hibernate使用原生sql查询Hibernate原生SQL多表查询字段名重复问题以及解决方法

注:实际业务中可能比较复杂不得不用原生sql执行时可能会遇到该问题,为了描述问题,本例采用简单的举例 表1: tbtask: 具有如下列:taskId,name,groupId 表2: tb...
  • qq_14946973
  • qq_14946973
  • 2016年12月28日 13:36
  • 1354

T-SQL---多值模糊查询的处理

多值模糊查询的处理 所谓多值模糊查询,就是应用程序中传递过来多个参数,对这些参数做拆分,拆分之后,对拆分结果的key值分别做模糊查询处理 对于精确匹配时,不管是单个Key值还是多个Key值...
  • hesi9555
  • hesi9555
  • 2017年04月20日 09:31
  • 167

[Elasticsearch] 邻近匹配 (二) - 多值字段,邻近程度与相关度

多值字段(Multivalue Fields) 在多值字段上使用短语匹配会产生古怪的行为: PUT /my_index/groups/1 { "names": [ "John Ab...
  • questiontoomuch
  • questiontoomuch
  • 2015年09月16日 14:52
  • 312

Windchill多值 _模糊查询Part.

  • 2013年04月29日 17:58
  • 78KB
  • 下载

dplyr分组后返回多值的处理

lapply函数使用之前需要对原始数据拆分为list的结构(使用split函数),而拆分的过程当数据量稍大时速度很慢 by_species % mutate(Sample=sample(LETTE...
  • xwydq
  • xwydq
  • 2015年04月15日 16:48
  • 831

ks_indexer同步HBase数据到Solr多值错误处理

ks_indexer1
  • i5secs
  • i5secs
  • 2014年09月05日 17:23
  • 2044

【JavaEE】servlet处理多值问题示例

前台页面:
  • Jiojio_
  • Jiojio_
  • 2017年04月12日 15:15
  • 175

Pentaho BI Server处理多值参数report及JVM设定

在实际使用中,一个报表可能需要让用户指定一些参数来筛选更加具体的report。这在Report designer中基本不是问题。当你想把report发布到Pentaho BI Server中情况可能会...
  • Athenaer
  • Athenaer
  • 2012年10月18日 16:28
  • 2313
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:外理多值查询,同时相关业务的复杂处理
举报原因:
原因补充:

(最多只允许输入30个字)