?? 附件代理領銷存情況查詢.sql
字號:
/***********************************************
統計代理附件每種類型的進貨
@fall 標記是否查找萬能ALL,所有的機型（萬能機型和適用機型）BFJ只查詢有適用機型，WLJ只查詢適用萬能機型的
@fpjzl all,所有的配件資料當對配件資料進行選擇后，就不為ALL
@act
1:查看總表
2:查看明細表
**************************************************/
--select f.fid,r.fmon,r.fnum,r.fdate, from tfjrc r
declare @ffdate datetime
declare @fedate datetime
declare @fpjzl char(5)--配件資料all,所有的配件資料
declare @fadr char(4)--配件貨源地
declare @fname char(5)--配件廠商
declare @fpjlx char(2)--配件類型--附件類型 ALL為所有附件類型
declare @fjx char(5)--標記查找的機型'ALL為所有'
declare @fall char(3)--標記是否查找萬能ALL,所有的機型（萬能機型和適用機型）BFJ只查詢有適用機型，WLJ只查詢適用萬能機型的
declare @act int
declare @lx char(2)
DECLARE @GT CHAR(4)
declare @temp table
(
flx char(2),
fid char(12),
fpjzl char(5),
fmon money,
fnum int,
fdate datetime,
fgt char(4)
)
declare @temppjzl table
(
fid char(12)
)
declare @tempcount table
(
fgt char(4),
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(15),
fjx char(20),
fnum int
)
declare @tempwx table
(
fgt char(4),
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(16),
fjx char(20),
fnum int,
fxc int,
fth int,
fwxnum int
)
set @ffdate='2001-01-01'
set @fedate='2005-12-30'
set @fpjzl='000'
set @fadr='ALL'
set @fname='ALL'
set @fpjlx='AL'
set @fjx='ALL'
set @fall='ALL'
set @act=3
set @lx='AL'
SET @GT='ALL'
IF @GT='ALL'
SET @GT='%%'
if @lx='AL'
set @lx='%%'
if @fadr='ALL'
set @fadr='%%'
if @fname='ALL'
set @fname='%%'
if @fpjlx='AL'
set @fpjlx='%%'
if @fjx='ALL'
set @fjx='%%'
--得到配件資料
if @fpjzl='ALL'
begin
insert @temp
select (
case substring(fid,1,2)
when 'FL' then 'FL'
when 'FX' then 'FS'
when 'FT' then 'FT'
end
) as flx,fid,fpjzl,fmon,fnum,fdate,fgt
from tglfj
where fdate>=@ffdate and fdate<=@fedate and fgt like ltrim(rtrim(@gt))
end
if @fpjzl<>'ALL'
begin
if @fall='ALL'--當不是查找所有的附件類型 資料時，但是附件的適用機型又是所有的情況
begin
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
end
if @fall='BFJ'--只查部分適用機型
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
and fjx like ltrim(rtrim(@fjx))
if @fall='WLJ'--只查部分適用機型
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
and fjx is null
insert @temp
select (
case substring(fid,1,2)
when 'FL' then 'FL'
when 'FX' then 'FS'
when 'FT' then 'FT'
end
) as flx,fid,fpjzl,fmon,fnum,fdate,fgt
from tglfj
where fdate>=@ffdate and fdate<=@fedate and fgt like ltrim(rtrim(@gt)) and fpjzl in (select fid from @temppjzl)
end
insert into @tempcount
select a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
from @temp
group by fgt,flx,fpjzl
order by fgt,flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid --and a.flx like @lx
order by a.fgt,a.flx,b.ffjlx,b.ffjcs,b.fjx
--得到未銷的數據
insert into @tempwx
select a.fgt,'FW' as flx,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,a.fnum,isnull(b.fxcnum,0)as fxcnum,isnull(b.fthnum,0) as fthnum,isnull(b.fwxnum,a.fnum) as fwxnum
from(
select top 10000 *
from @tempcount a
where a.flx='FL'
)a left outer join
(
select top 10000 a.fgt,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,b.fnum as fxcnum,(a.fnum-b.fnum-c.fnum) as fwxnum,c.fnum as fthnum
from
(
select top 10000 *
from @tempcount a
where a.flx='FL'
)a,
(
select top 10000 *
from @tempcount a
where a.flx='FS'
)b,
(
select top 10000 *
from @tempcount a
where a.flx='FT'
)c
where a.fpjzl=b.fpjzl and a.fgt=b.fgt and a.fpjzl=c.fpjzl and c.fgt=a.fgt and b.fpjzl=c.fpjzl and b.fgt=c.fgt
)b
on a.fpjzl=b.fpjzl and a.fgt=b.fgt
order by a.fgt,a.fpjzl,a.ffjcs,a.fjx
--select * from @tempcount
--select * from @temp
--select * from @tempwx
if @act=1
begin
--統計出每種型號的數據量
select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
(case a.flx
when 'FL' then '領貨'
when 'FS' then '銷售'
when 'FT' then '退貨'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount
from
(
select top 10000 a.*
from
(
select top 10000 b.fgt,b.flx,ltrim(rtrim(b.ffjlx)) as ffjlx,ltrim(rtrim(b.fjx)) as fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.fgt,b.flx,b.ffjlx,b.fjx
order by b.fgt,b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a
union
select top 10000 a.*
from
(
select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.fgt,a.flx,a.ffjlx,a.fjx
order by a.fgt,a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)a
)a,tgt
where a.flx like @lx and tgt.fid=a.fgt
order by a.fgt,a.flx
end
if @act=2
begin
select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
(case a.flx
when 'FL' then '領貨'
when 'FS' then '銷售'
when 'FT' then '退貨'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum
from(
select top 10000 a.*
from (
SELECT top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum as fnum
from
(
select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.fgt,a.flx,a.ffjlx,a.fjx
--order by a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)A,@TEMPWX B
WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx and a.fgt=b.fgt
--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
)a
union
select top 10000 a.*
from (
--統計出總數據報表
select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
from
(
select top 1000 b.fgt,b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.fgt,b.flx,b.ffjlx,b.fjx
--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a,
(
select top 10000 a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
from @temp
group by fgt,flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
)b
where a.fgt=b.fgt and a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
--order by a.flx,a.ffjlx,a.fjx,b.ffjcs
)a
)a,tgt
where a.flx like @lx and tgt.fid=a.fgt
order by a.fgt,a.flx,a.ffjlx,a.fjx,a.ffjcs
end
if @act=3
begin
select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
(case a.flx
when 'FL' then '領貨'
when 'FS' then '銷售'
when 'FT' then '退貨'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,a.fdate,a.fnumb
from
(
SELECT TOP 10000 a.* ,null as fdate,null as fnumb
FROM
(
SELECT top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum as fnum
from
(
select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.fgt,a.flx,a.ffjlx,a.fjx
--order by a.fgt,a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)A,@TEMPWX B
WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx and a.fgt=b.fgt
--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
)a
union
SELECT TOP 10000 *
FROM
(
select a.fgt,a.flx,a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,b.fdate,b.fnumb
from
(
select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
from
(
select top 1000 b.fgt,b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.fgt,b.flx,b.ffjlx,b.fjx
--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a,
(
select top 10000 a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 fgt, flx,fpjzl,sum(fnum) as fnum
from @temp
group by fgt,flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
)b
where a.fgt=b.fgt and a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
order by a.flx,a.ffjlx,a.fjx,b.ffjcs
)a,
(
select top 10000 a.*,b.fdate,b.fnum as fnumb
from
(
select a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
from @temp
group by fgt,flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
)a,@temp b
where a.fgt=b.fgt and a.flx=b.flx and a.fpjzl=b.fpjzl
order by a.flx,a.ffjlx,a.ffjcs,a.fjx
)b
where a.fgt=b.fgt and a.flx=b.flx and a.ffjlx=b.ffjlx and a.ffjcs=b.ffjcs and a.fjx=b.fjx
--order by a.flx,a.ffjlx,a.fjx,a.ffjcs,b.fdate,b.fhyd
)A
)a,tgt
where a.flx like @lx and tgt.fid=a.fgt
order by a.fgt,a.flx,a.ffjlx,a.fjx,a.ffjcs,a.fdate
end
--select * from @temp
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -