查询所有类型进货的交叉表查询.sql

来自「手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能」· SQL 代码 · 共 24 行

select c.fsjxh,b.fsjxhid,b.fjh,b.fkt,b.fsg,b.fcf,(fjh+fkt+fsg+fcf)as ftotal 
	from 
		(select a.fsjxhid,
			sum(case a.fhylxid when 'JH' then a.flxtotal else 0 end) as FJH,
			sum(case a.fhylxid when 'KT' then a.flxtotal else 0 end) as FKT,
			sum(case a.fhylxid when 'SG' then a.flxtotal else 0 end) as FSG,
			sum(case a.fhylxid when 'CF' then a.flxtotal else 0 end) as FCF

 			from  
				(SELECT top 1000  fsjxhid, fhylxid, SUM(fnum) AS flxtotal
					FROM 
						(SELECT TOP 1000 dbo.tsjxh.fid AS fsjxhid,dbo.thrc.fid,  dbo.thrc.fnum,dbo.thrc.fdate, dbo.thylx.fid AS fhylxid
							FROM dbo.tsjxh INNER JOIN
    								  dbo.tsjcs ON dbo.tsjxh.fsjcs = dbo.tsjcs.fid INNER JOIN
    								  dbo.thrc ON dbo.tsjxh.fid = dbo.thrc.fsjxh INNER JOIN
     								  dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid INNER JOIN
    								  dbo.thylx ON dbo.thrc.fhylx = dbo.thylx.fid INNER JOIN
   							          dbo.thyd ON dbo.thrc.fhyd = dbo.thyd.fid
								--设置查询的日期段也就是手机进货的日期，就能查出这一时段所有进货的手机了
							ORDER BY dbo.thrc.fid DESC) a
			GROUP BY fsjxhid, fhylxid
	ORDER BY fsjxhid) a 
GROUP BY fsjxhid)  b,vAllSjxh c
where b.fsjxhid=c.fid