前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >复杂SQL代码实例

复杂SQL代码实例

作者头像
用户1112962
发布2018-07-04 13:29:25
9180
发布2018-07-04 13:29:25
举报
文章被收录于专栏:互联网开发者交流社区
代码语言:javascript
复制
DECLARE @begin DATETIME,@end DATETIME,@shanghutype INT, @beginshanghuarea BIGINT ,@endshanghuarea bigint
SELECT @begin='1753-01-01',@end='9999-12-31',@shanghutype=1,@beginshanghuarea=-1,@endshanghuarea=9223372036854775807

IF OBJECT_ID('tempdb.dbo.#tmp', 'U') IS NOT NULL
DROP TABLE #tmp;
CREATE TABLE #tmp
(
SysNo	BIGINT,
Source	INT,
ji	DECIMAL(20,6),
dai DECIMAL(20,6)

)


IF @shanghutype=1
BEGIN

	;WITH    tmpj
			  AS ( SELECT   
							b.AdjustType ,
							a.Source,
							AdjustAmount = ISNULL(c.AdjustAmount, 0),
							(CASE WHEN A.Source=1 THEN A.VendorSysNo ELSE A.DistributorSysNo END ) AS SysNo
				   FROM     [BBCAccount].[dbo].[Account] a WITH ( NOLOCK )
							LEFT JOIN BBCFinance.dbo.AccountAdjustReceipt b WITH ( NOLOCK ) ON a.SysNo = b.AccountSysNo
																  AND b.Status = 2
							LEFT JOIN BBCFinance.dbo.AccountBalanceChange c WITH ( NOLOCK ) ON b.AccountSysNo = c.AccountSysNo

							LEFT JOIN BBCAccount.dbo.Distributor distributor WITH(NOLOCK)  ON a.DistributorSysNo=distributor.SysNo
							LEFT JOIN BBCAccount.dbo.Vendor vendor WITH(NOLOCK) ON A.VendorSysNo=vendor.SysNo
				WHERE (a.InDate BETWEEN  @begin  and @end )  
					AND a.SOURCE=@shanghutype  
					AND ( a.VendorSysNo  BETWEEN @beginshanghuarea AND  @endshanghuarea)
	)
	INSERT INTO #tmp(SysNo,Source,ji,dai)
	SELECT  distinct
		(CASE WHEN a1.Source=1 THEN a1.VendorSysNo ELSE a1.DistributorSysNo END ) AS SysNo,

				a1.Source,
				ji = ( SELECT   SUM(bb.AdjustAmount)
					   FROM     tmpj bb
					   WHERE    (bb.SysNo = a1.VendorSysNo OR bb.SysNo=a1.DistributorSysNo)
								AND bb.AdjustType = 1
					 ) ,
				dai = ( SELECT  SUM(bb.AdjustAmount)
						FROM    tmpj bb
						WHERE    (bb.SysNo = a1.VendorSysNo OR bb.SysNo=a1.DistributorSysNo)
								AND bb.AdjustType = -1
					  )
	
		FROM    [BBCAccount].[dbo].[Account] a1 WITH ( NOLOCK ) 
		WHERE (a1.InDate BETWEEN  @begin  and @end )  
				AND a1.SOURCE=@shanghutype  
				AND ( a1.VendorSysNo  BETWEEN @beginshanghuarea AND  @endshanghuarea)
				 
	
END
ELSE
BEGIN
	IF OBJECT_ID('tempdb.dbo.#tmp', 'U') IS NOT NULL
    DROP TABLE #tmp;
	;WITH    tmpj
			  AS ( SELECT   
							b.AdjustType ,
							a.Source,
							AdjustAmount = ISNULL(c.AdjustAmount, 0),
							(CASE WHEN A.Source=1 THEN A.VendorSysNo ELSE A.DistributorSysNo END ) AS SysNo
				   FROM     [BBCAccount].[dbo].[Account] a WITH ( NOLOCK )
							LEFT JOIN BBCFinance.dbo.AccountAdjustReceipt b WITH ( NOLOCK ) ON a.SysNo = b.AccountSysNo
																  AND b.Status = 2
							LEFT JOIN BBCFinance.dbo.AccountBalanceChange c WITH ( NOLOCK ) ON b.AccountSysNo = c.AccountSysNo

							LEFT JOIN BBCAccount.dbo.Distributor distributor WITH(NOLOCK)  ON a.DistributorSysNo=distributor.SysNo
							LEFT JOIN BBCAccount.dbo.Vendor vendor WITH(NOLOCK) ON A.VendorSysNo=vendor.SysNo
				WHERE (a.InDate BETWEEN  @begin  and @end )  
					AND a.SOURCE=@shanghutype  
					AND ( a.DistributorSysNo  BETWEEN @beginshanghuarea AND  @endshanghuarea)
	)
	INSERT INTO #tmp(SysNo,Source,ji,dai)
	SELECT  distinct
		(CASE WHEN a1.Source=1 THEN a1.VendorSysNo ELSE a1.DistributorSysNo END ) AS SysNo,
		
				a1.Source,
				ji = ( SELECT   SUM(bb.AdjustAmount)
					   FROM     tmpj bb
					   WHERE    (bb.SysNo = a1.VendorSysNo OR bb.SysNo=a1.DistributorSysNo)
								AND bb.AdjustType = 1
					 ) ,
				dai = ( SELECT  SUM(bb.AdjustAmount)
						FROM    tmpj bb
						WHERE    (bb.SysNo = a1.VendorSysNo OR bb.SysNo=a1.DistributorSysNo)
								AND bb.AdjustType = -1
					  )

		FROM    [BBCAccount].[dbo].[Account] a1 WITH ( NOLOCK ) 
		WHERE (a1.InDate BETWEEN  @begin  and @end )  
				AND a1.SOURCE=@shanghutype  
				AND ( a1.DistributorSysNo  BETWEEN @beginshanghuarea AND  @endshanghuarea)

END 

IF OBJECT_ID('tempdb.dbo.#tmp2', 'U') IS NOT NULL
    DROP TABLE #tmp2;
WITH    TMP
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY a.SysNo ORDER BY B.INDATE DESC ) AS RowNumber ,
                       -- a.SysNo ,
                        BU.Name ,
                        b.AdjustedBalance,
						(CASE WHEN A.Source=1 THEN A.VendorSysNo ELSE A.DistributorSysNo END ) AS SysNo
               FROM     [BBCAccount].[dbo].[Account] a WITH ( NOLOCK )
                        LEFT JOIN BBCAccount.dbo.BusinessType BU WITH ( NOLOCK ) ON a.BizTypeCode = BU.Code
                                                              AND BU.ParentCode IS NULL
                                                              AND BU.Type = 0
                                                              AND BU.IsSystem = 1
                        LEFT JOIN BBCFinance.dbo.AccountBalanceChange b WITH ( NOLOCK ) ON a.SysNo = b.AccountSysNo
                        LEFT JOIN BBCFinance.dbo.AccountAdjustReceipt c ON b.AccountSysNo = c.AccountSysNo
                                                              AND b.BizID = c.ChangeNo
						LEFT JOIN BBCAccount.dbo.Distributor distributor WITH(NOLOCK)  ON a.DistributorSysNo=distributor.SysNo
						LEFT JOIN BBCAccount.dbo.Vendor vendor WITH(NOLOCK) ON A.VendorSysNo=vendor.SysNo
               WHERE    c.Status = 2
                        AND BU.Type = 0
                        AND BU.IsSystem = 1
                        AND BU.ParentCode IS NULL
                        AND BU.Type = 0
                        AND BU.IsSystem = 1
             ),
        TMP1
          AS ( SELECT   SysNo ,
                        Name ,
                        AdjustedBalance
               FROM     TMP
               WHERE    RowNumber = 1
             )
    SELECT  SysNo ,
            Name ,
            amount = SUM(AdjustedBalance)
    INTO    #tmp2
    FROM    TMP1
    GROUP BY Name ,
            SysNo;


 IF OBJECT_ID('tempdb.dbo.#tmp', 'U') IS NOT NULL
DROP TABLE #ty;

;WITH tt
AS
(

SELECT  
t1.SysNo,total=SUM(t2.amount)
FROM    #tmp t1
        LEFT JOIN #tmp2 t2 ON t1.SysNo = t2.SysNo
--WHERE  
-- t1.SysNo = 2272  
GROUP BY  t1.SysNo

 )
 
SELECT  
t1.SysNo,t1.Source,t1.ji,t1.dai,t2.Name,t2.amount,tt.total 
INTO #ty
FROM    #tmp t1
        LEFT JOIN #tmp2 t2 ON t1.SysNo = t2.SysNo
LEFT JOIN tt 
	ON t1.SysNo=tt.SysNo
--WHERE   t1.SysNo = 2272 
 ORDER BY t1.SysNo;


 SELECT T.*, qmye=(ye+djye+kms+bzj+fxye) FROM ( SELECT SysNo,Source,ji AS jffse,dai AS dffse,
  ye=CASE WHEN Name='余额账户' THEN amount ELSE 0.00 END,
  djye=CASE WHEN Name='冻结余额账户' THEN amount ELSE 0.00 END,
  kms=CASE WHEN Name='跨贸税账户' THEN amount ELSE 0.00 END,
  bzj=CASE WHEN Name='保证金账户' THEN amount ELSE 0.00 END,
  fxye=CASE WHEN Name='供应商的分销余额' THEN amount ELSE 0.00 END
 FROM #ty)  AS T

--DECLARE @sql_col VARCHAR(8000)
--DECLARE @sql_str VARCHAR(8000)
--DECLARE @sql_ VARCHAR(MAX)
--SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(name) FROM #ty
--SET @sql_='
--select a.*,
--jie=(select top(1) ji  from #ty ty where ty.sysno=a.sysno),
--dai=(select top(1) dai  from #ty ty where ty.sysno=a.sysno)
--from 
--(
--select *
--from(
--		select totAL,Name,SysNo  from #ty
--	)as tw
--pivot( max(totAL) for Name in('+@sql_col+') )piv
--) a 
--'
--EXEC(@sql_)

--SELECT TOP(1) * FROM #ty  
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017-03-14 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档