sql 统计汇总

qq_33079425 2017-03-03 01:34:54

不好意思，需求考虑不够完善，现进行修正，麻烦各位指点指点，不胜感激。要是能用存储过程来实现就在好不过了。谢谢。
CREATE TABLE #T1(XM VARCHAR(20),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE)
INSERT INTO #T1 VALUES
('张三','99999',100,'通过','2016/10/20'),
('罗六','10000',50,'通过','2016/10/25'),
('张三','10001',100,'通过','2016/11/01'),
('张三','10002',100,'拒绝',' 2016/11/07'),
('张三','10003',200,'通过','2016/11/26'),
('李四','10004',300,'通过','2016/11/03'),
('李四','10005',100,'通过','2016/11/03'),
('李四','10006',300,'放弃','2016/11/15'),
('李四','10007',200,'拒绝','2016/11/25'),
('王五','10008',300,'通过','2016/11/15'),
('王五','10009',200,'通过','2016/11/25'),
('王五','10010',100,'通过','2016/12/15')

CREATE TABLE #T2(XM VARCHAR(20),BH VARCHAR(200),RQ DATE,JE INT)
INSERT INTO #T2 VALUES
('罗六','10000','2016/10/27',10),
('罗六','10000','2016/11/01',20),
('张三','10001','2016/11/03',100),
('张三','10003','2016/11/26',100),
('李四','10004','2016/11/24',100),
('李四','10005','2016/11/24',100),
('李四','10004','2016/11/26',100),
('王五','10008','2016/11/15',150),
('王五','10008','2016/11/19',150),
('王五','10009','2016/12/03',50),
('王五','10009','2016/12/07',50)

...全文

337 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2017-03-09

打赏
举报

试试这个吧


CREATE TABLE #T1(XM VARCHAR(20),ZB VARCHAR(200),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE)
INSERT INTO #T1 VALUES
('张三','1组','99999',100,'通过','2016/10/20'),
('罗六','4组','10000',50,'通过','2016/10/25'),
('田七','4组','88888',100,'通过','2016/11/02'),
('张三','1组','10001',100,'通过','2016/11/01'),
('张三','1组','10002',100,'拒绝',' 2016/11/07'),
('张三','1组','10003',200,'通过','2016/11/26'),
('李四','2组','10004',300,'通过','2016/11/03'),
('李四','2组','10005',100,'通过','2016/11/03'),
('李四','2组','10006',300,'放弃','2016/11/15'),        
('李四','2组','10007',200,'拒绝','2016/11/25'),
('王五','3组','10008',300,'通过','2016/11/15'),
('王五','3组','10009',200,'通过','2016/11/25'),
('王五','3组','10010',100,'通过','2016/12/15')
 
CREATE TABLE #T2(XM VARCHAR(20),ZB VARCHAR(200),BH VARCHAR(200),RQ DATE,JE INT)
INSERT INTO #T2 VALUES
('罗六','4组','10000','2016/10/27',10),
('罗六','4组','10000','2016/11/01',20),
('张三','1组','10001','2016/11/03',100),
('张三','1组','10003','2016/11/26',100),
('李四','2组','10004','2016/11/24',100),
('李四','2组','10005','2016/11/24',100),
('李四','2组','10004','2016/11/26',100),
('王五','3组','10008','2016/11/15',150),
('王五','3组','10008','2016/11/19',150),
('王五','3组','10009','2016/12/03',50),
('王五','3组','10009','2016/12/07',50)

;WITH tempa AS (
SELECT xm,zt,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T1 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY xm,ZT
),tempb AS (
SELECT xm,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T2 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY XM
),result AS (
SELECT  COALESCE(tempb.XM ,tempa.XM ) AS XM,
        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.笔数
                 ELSE 0
            END) AS 通过笔数 ,
        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.金额
                 ELSE 0
            END) AS 通过金额 ,
        SUM(CASE WHEN tempa.ZT = '拒绝'
                      OR tempa.ZT = '放弃' THEN tempa.笔数
                 ELSE 0
            END) AS [拒绝/放弃笔数] ,
        SUM(CASE WHEN tempa.ZT = '拒绝'
                      OR tempa.ZT = '放弃' THEN tempa.金额
                 ELSE 0
            END) AS [拒绝/放弃金额] ,
        MAX(tempb.笔数) AS 放款笔数 ,
        MAX(tempb.金额) AS 放款金额 ,
        ( ( SELECT  SUM(JE)
            FROM    #T1
            WHERE   ZT = '通过'
                    AND XM = tempb.XM
            GROUP BY XM
          ) - ( SELECT  SUM(JE)
                FROM    #T2
                WHERE   XM = tempb.XM
                GROUP BY XM
              ) ) AS 待放款金额 ,
        DENSE_RANK() OVER ( ORDER BY MAX(tempb.金额) DESC ) AS 放款排名
FROM    tempb
        FUll JOIN tempa ON tempa.XM = tempb.XM
GROUP BY tempb.XM,tempa.XM
)
SELECT * FROM (
SELECT * FROM result
UNION
SELECT  '合计' AS xm ,
        SUM(通过笔数) AS 通过笔数 ,
        SUM(通过金额) AS 通过金额 ,
        SUM([拒绝/放弃笔数]) AS [拒绝/放弃笔数] ,
        SUM([拒绝/放弃金额]) AS [拒绝/放弃金额] ,
        SUM([放款笔数]) AS [放款笔数] ,
        SUM(放款金额) AS 放款金额 ,
SUM(待放款金额) AS 待放款金额 ,
        9999
FROM    result
)t
ORDER BY t.放款排名

DROP TABLE #T1,#T2

qq_33079425 2017-03-08

打赏
举报

引用 5 楼 RINK_1 的回复:

SELECT CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '合计' ELSE ISNULL(A.业务员,B.业务员) END 业务员, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过笔数) ELSE MAX(A.通过笔数) END AS 通过笔数 , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过金额) ELSE MAX(A.通过金额) END AS 通过金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃笔数]) ELSE MAX(A.[拒单/放弃笔数]) END AS [拒单/放弃笔数], CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃金额]) ELSE MAX(A.[拒单/放弃金额]) END AS [拒单/放弃金额] , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款笔数) ELSE MAX(B.放款笔数) END AS 放款笔数, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款金额) ELSE MAX(B.放款金额) END AS 放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.总审批金额-B.总放款金额) ELSE MAX(A.总审批金额-B.总放款金额) END AS 待放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '' ELSE CAST(DENSE_RANK() OVER (ORDER BY MAX(B.放款金额) DESC) AS VARCHAR) END AS 放款排名 FROM (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 通过笔数, SUM(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 通过金额, COUNT(DISTINCT(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS [拒单/放弃笔数], SUM(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS [拒单/放弃金额], SUM(CASE WHEN ZT='通过' THEN JE ELSE 0 END) AS 总审批金额 FROM #T1 A GROUP BY XM) AS A FULL JOIN (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 放款笔数, SUM(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 放款金额, SUM(JE) AS 总放款金额 FROM #T2 GROUP BY XM) AS B ON A.业务员=B.业务员 GROUP BY ISNULL(A.业务员,B.业务员) WITH ROLLUP ORDER BY GROUPING(ISNULL(A.业务员,B.业务员)),放款排名

DROP TABLE #T1,#T2 CREATE TABLE #T1(XM VARCHAR(20),ZB VARCHAR(200),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE) INSERT INTO #T1 VALUES ('张三','1组','99999',100,'通过','2016/10/20'), ('罗六','4组','10000',50,'通过','2016/10/25'), ('田七','4组','88888',100,'通过','2016/11/02'), ('张三','1组','10001',100,'通过','2016/11/01'), ('张三','1组','10002',100,'拒绝',' 2016/11/07'), ('张三','1组','10003',200,'通过','2016/11/26'), ('李四','2组','10004',300,'通过','2016/11/03'), ('李四','2组','10005',100,'通过','2016/11/03'), ('李四','2组','10006',300,'放弃','2016/11/15'), ('李四','2组','10007',200,'拒绝','2016/11/25'), ('王五','3组','10008',300,'通过','2016/11/15'), ('王五','3组','10009',200,'通过','2016/11/25'), ('王五','3组','10010',100,'通过','2016/12/15') CREATE TABLE #T2(XM VARCHAR(20),ZB VARCHAR(200),BH VARCHAR(200),RQ DATE,JE INT) INSERT INTO #T2 VALUES ('罗六','4组','10000','2016/10/27',10), ('罗六','4组','10000','2016/11/01',20), ('张三','1组','10001','2016/11/03',100), ('张三','1组','10003','2016/11/26',100), ('李四','2组','10004','2016/11/24',100), ('李四','2组','10005','2016/11/24',100), ('李四','2组','10004','2016/11/26',100), ('王五','3组','10008','2016/11/15',150), ('王五','3组','10008','2016/11/19',150), ('王五','3组','10009','2016/12/03',50), ('王五','3组','10009','2016/12/07',50)

qq_33079425 2017-03-07

打赏
举报

试了田七出来，但是罗六又显示出不来了。

二月十六 2017-03-07

打赏
举报

你试试这个




;WITH tempa AS (
SELECT xm,zt,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T1 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY xm,ZT
),tempb AS (
SELECT xm,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T2 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY XM
),result AS (
SELECT  tempa.XM ,
        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.笔数
                 ELSE 0
            END) AS 通过笔数 ,
        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.金额
                 ELSE 0
            END) AS 通过金额 ,
        SUM(CASE WHEN tempa.ZT = '拒绝'
                      OR tempa.ZT = '放弃' THEN tempa.笔数
                 ELSE 0
            END) AS [拒绝/放弃笔数] ,
        SUM(CASE WHEN tempa.ZT = '拒绝'
                      OR tempa.ZT = '放弃' THEN tempa.金额
                 ELSE 0
            END) AS [拒绝/放弃金额] ,
        ISNULL(MAX(tempb.笔数),0) AS 放款笔数 ,
        ISNULL(MAX(tempb.金额),0) AS 放款金额 ,
        ISNULL(( ( SELECT  SUM(JE)
            FROM    #T1
            WHERE   ZT = '通过'
                    AND XM = tempa.XM
            GROUP BY XM
          ) - ( SELECT  SUM(JE)
                FROM    #T2
                WHERE   XM = tempa.XM
                GROUP BY XM
              ) ),0) AS 待放款金额 ,
        DENSE_RANK() OVER ( ORDER BY MAX(tempb.金额) DESC ) AS 放款排名
FROM    tempa
        LEFT JOIN tempb ON tempa.XM = tempb.XM
GROUP BY tempa.XM
)
SELECT * FROM (
SELECT * FROM result
UNION
SELECT  '合计' AS xm ,
        SUM(通过笔数) AS 通过笔数 ,
        SUM(通过金额) AS 通过金额 ,
        SUM([拒绝/放弃笔数]) AS [拒绝/放弃笔数] ,
        SUM([拒绝/放弃金额]) AS [拒绝/放弃金额] ,
        SUM([放款笔数]) AS [放款笔数] ,
        SUM(放款金额) AS 放款金额 ,
SUM(待放款金额) AS 待放款金额 ,
        9999
FROM    result
)t
ORDER BY t.放款排名

qq_33079425 2017-03-07

打赏
举报

请大师帮忙指点一下。 DROP TABLE #T1,#T2 CREATE TABLE #T1(XM VARCHAR(20),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE) INSERT INTO #T1 VALUES ('张三','99999',100,'通过','2016/10/20'), ('罗六','10000',50,'通过','2016/10/25'), ('田七','88888',100,'通过','2016/11/02'), --新增加1笔记录，这笔记录就无法查询的出来 ('张三','10001',100,'通过','2016/11/01'), ('张三','10002',100,'拒绝',' 2016/11/07'), ('张三','10003',200,'通过','2016/11/26'), ('李四','10004',300,'通过','2016/11/03'), ('李四','10005',100,'通过','2016/11/03'), ('李四','10006',300,'放弃','2016/11/15'), ('李四','10007',200,'拒绝','2016/11/25'), ('王五','10008',300,'通过','2016/11/15'), ('王五','10009',200,'通过','2016/11/25'), ('王五','10010',100,'通过','2016/12/15') --注：#T1表有数据，#T2表不一定有；但是#T2有数据，#T1就一定会有 CREATE TABLE #T2(XM VARCHAR(20),BH VARCHAR(200),RQ DATE,JE INT) INSERT INTO #T2 VALUES ('罗六','10000','2016/10/27',10), ('罗六','10000','2016/11/01',20), ('张三','10001','2016/11/03',100), ('张三','10003','2016/11/26',100), ('李四','10004','2016/11/24',100), ('李四','10005','2016/11/24',100), ('李四','10004','2016/11/26',100), ('王五','10008','2016/11/15',150), ('王五','10008','2016/11/19',150), ('王五','10009','2016/12/03',50), ('王五','10009','2016/12/07',50) ;WITH tempa AS ( SELECT xm,zt,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T1 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY xm,ZT ),tempb AS ( SELECT xm,COUNT(DISTINCT BH) AS 笔数,SUM(JE) AS 金额 FROM #T2 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY XM ),result AS ( SELECT tempb.XM , SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.笔数 ELSE 0 END) AS 通过笔数 , SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.金额 ELSE 0 END) AS 通过金额 , SUM(CASE WHEN tempa.ZT = '拒绝' OR tempa.ZT = '放弃' THEN tempa.笔数 ELSE 0 END) AS [拒绝/放弃笔数] , SUM(CASE WHEN tempa.ZT = '拒绝' OR tempa.ZT = '放弃' THEN tempa.金额 ELSE 0 END) AS [拒绝/放弃金额] , MAX(tempb.笔数) AS 放款笔数 , MAX(tempb.金额) AS 放款金额 , ( ( SELECT SUM(JE) FROM #T1 WHERE ZT = '通过' AND XM = tempb.XM GROUP BY XM ) - ( SELECT SUM(JE) FROM #T2 WHERE XM = tempb.XM GROUP BY XM ) ) AS 待放款金额 , DENSE_RANK() OVER ( ORDER BY MAX(tempb.金额) DESC ) AS 放款排名 FROM tempb LEFT JOIN tempa ON tempa.XM = tempb.XM GROUP BY tempb.XM ) SELECT * FROM ( SELECT * FROM result UNION SELECT '合计' AS xm , SUM(通过笔数) AS 通过笔数 , SUM(通过金额) AS 通过金额 , SUM([拒绝/放弃笔数]) AS [拒绝/放弃笔数] , SUM([拒绝/放弃金额]) AS [拒绝/放弃金额] , SUM([放款笔数]) AS [放款笔数] , SUM(放款金额) AS 放款金额 , SUM(待放款金额) AS 待放款金额 , 9999 FROM result )t ORDER BY t.放款排名

qq_33079425 2017-03-03

打赏
举报

谢谢两位大师的帮助，初步测试没有问题，感激不尽。

二月十六 2017-03-03

打赏
举报

语句：

CREATE TABLE #T1(XM VARCHAR(20),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE)

INSERT INTO #T1 VALUES

('张三','99999',100,'通过','2016/10/20'),

('罗六','10000',50,'通过','2016/10/25'),

('张三','10001',100,'通过','2016/11/01'),

('张三','10002',100,'拒绝',' 2016/11/07'),

('张三','10003',200,'通过','2016/11/26'),

('李四','10004',300,'通过','2016/11/03'),

('李四','10005',100,'通过','2016/11/03'),

('李四','10006',300,'放弃','2016/11/15'),        

('李四','10007',200,'拒绝','2016/11/25'),

('王五','10008',300,'通过','2016/11/15'),

('王五','10009',200,'通过','2016/11/25'),

('王五','10010',100,'通过','2016/12/15')

 

CREATE TABLE #T2(XM VARCHAR(20),BH VARCHAR(200),RQ DATE,JE INT)

INSERT INTO #T2 VALUES

('罗六','10000','2016/10/27',10),

('罗六','10000','2016/11/01',20),

('张三','10001','2016/11/03',100),

('张三','10003','2016/11/26',100),

('李四','10004','2016/11/24',100),

('李四','10005','2016/11/24',100),

('李四','10004','2016/11/26',100),

('王五','10008','2016/11/15',150),

('王五','10008','2016/11/19',150),

('王五','10009','2016/12/03',50),

('王五','10009','2016/12/07',50)

;WITH tempa AS (

SELECT xm,zt,COUNT(1) AS 笔数,SUM(JE) AS 金额 FROM #T1 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY xm,ZT

),tempb AS (

SELECT xm,COUNT(1) AS 笔数,SUM(JE) AS 金额 FROM #T2 WHERE rq BETWEEN '2016-11-01' AND '2016-11-30' GROUP BY XM

),result AS (

SELECT  tempb.XM ,

        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.笔数

                 ELSE 0

            END) AS 通过笔数 ,

        SUM(CASE WHEN tempa.ZT = '通过' THEN tempa.金额

                 ELSE 0

            END) AS 通过金额 ,

        SUM(CASE WHEN tempa.ZT = '拒绝'

                      OR tempa.ZT = '放弃' THEN tempa.笔数

                 ELSE 0

            END) AS [拒绝/放弃笔数] ,

        SUM(CASE WHEN tempa.ZT = '拒绝'

                      OR tempa.ZT = '放弃' THEN tempa.金额

                 ELSE 0

            END) AS [拒绝/放弃金额] ,

        MAX(tempb.笔数) AS 放款笔数 ,

        MAX(tempb.金额) AS 放款金额 ,

        ( ( SELECT  SUM(JE)

            FROM    #T1

            WHERE   ZT = '通过'

                    AND XM = tempb.XM

            GROUP BY XM

          ) - ( SELECT  SUM(JE)

                FROM    #T2

                WHERE   XM = tempb.XM

                GROUP BY XM

              ) ) AS 待放款金额 ,

        DENSE_RANK() OVER ( ORDER BY MAX(tempb.金额) DESC ) AS 放款排名

FROM    tempb

        LEFT JOIN tempa ON tempa.XM = tempb.XM

GROUP BY tempb.XM

)

SELECT * FROM (

SELECT * FROM result

UNION

SELECT  '合计' AS xm ,

        SUM(通过笔数) AS 通过笔数 ,

        SUM(通过金额) AS 通过金额 ,

        SUM([拒绝/放弃笔数]) AS [拒绝/放弃笔数] ,

        SUM([拒绝/放弃金额]) AS [拒绝/放弃金额] ,

        SUM([放款笔数]) AS [放款笔数] ,

        SUM(放款金额) AS 放款金额 ,

		SUM(待放款金额) AS 待放款金额 ,

        9999

FROM    result

)t

ORDER BY t.放款排名



DROP TABLE #T1,#T2

结果：

qq_33079425 2017-03-03

打赏
举报

引用 5 楼 RINK_1 的回复:

SELECT CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '合计' ELSE ISNULL(A.业务员,B.业务员) END 业务员, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过笔数) ELSE MAX(A.通过笔数) END AS 通过笔数 , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过金额) ELSE MAX(A.通过金额) END AS 通过金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃笔数]) ELSE MAX(A.[拒单/放弃笔数]) END AS [拒单/放弃笔数], CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃金额]) ELSE MAX(A.[拒单/放弃金额]) END AS [拒单/放弃金额] , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款笔数) ELSE MAX(B.放款笔数) END AS 放款笔数, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款金额) ELSE MAX(B.放款金额) END AS 放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.总审批金额-B.总放款金额) ELSE MAX(A.总审批金额-B.总放款金额) END AS 待放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '' ELSE CAST(DENSE_RANK() OVER (ORDER BY MAX(B.放款金额) DESC) AS VARCHAR) END AS 放款排名 FROM (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 通过笔数, SUM(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 通过金额, COUNT(DISTINCT(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS [拒单/放弃笔数], SUM(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS [拒单/放弃金额], SUM(CASE WHEN ZT='通过' THEN JE ELSE 0 END) AS 总审批金额 FROM #T1 A GROUP BY XM) AS A FULL JOIN (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 放款笔数, SUM(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 放款金额, SUM(JE) AS 总放款金额 FROM #T2 GROUP BY XM) AS B ON A.业务员=B.业务员 GROUP BY ISNULL(A.业务员,B.业务员) WITH ROLLUP ORDER BY GROUPING(ISNULL(A.业务员,B.业务员)),放款排名

结果是实现，但是按时间段查，或不按时间段查(直接查全部)，切换起来好像不是很方便啊。

qq_33079425 2017-03-03

打赏
举报

引用 6 楼 sinat_28984567 的回复:

[quote=引用 4 楼 qq_33079425 的回复:] 1、待放款金额=总的审批金额-总的放款金额（待放款金额是指某个业务员所有审批状态为”通过“的金额在减去所有已经放款的金额） 2、也就是罗六总共 2016/10/25 通过 50 在减去总放款30 待放款金额为 20 3、同时加入时间过滤之后，罗六的放款笔数与放款金额也要显示出来，这个时间段有可能业务表没有数据。

1、如果加上时间过滤的话是这个时间段的待放款金额=这个时间段的总的审批金额-这个时间段的总的放款金额这样吗？ 2、如果不加时间这个没问题，就是50-30=20. 3、加上时间后，显示的是这个时间段里面罗六放款的笔数和金额吧？待放款金额呢？这个时间段总审批金额 0 - 这个时间段总放款金额 20 = -20？答：待放款额，不分时间段，是某个业务员总的审批通过金额-总的放款金额 [/quote]

寡亾 2017-03-03

打赏
举报

http://blog.csdn.net/timefile/article/details/6573808 这个挺适合新手，也能实现你的需求，不懒的话动手敲敲，前楼回答的也可以直接用了

二月十六 2017-03-03

打赏
举报

引用 4 楼 qq_33079425 的回复:

1、待放款金额=总的审批金额-总的放款金额（待放款金额是指某个业务员所有审批状态为”通过“的金额在减去所有已经放款的金额） 2、也就是罗六总共 2016/10/25 通过 50 在减去总放款30 待放款金额为 20 3、同时加入时间过滤之后，罗六的放款笔数与放款金额也要显示出来，这个时间段有可能业务表没有数据。

1、如果加上时间过滤的话是这个时间段的待放款金额=这个时间段的总的审批金额-这个时间段的总的放款金额这样吗？ 2、如果不加时间这个没问题，就是50-30=20. 3、加上时间后，显示的是这个时间段里面罗六放款的笔数和金额吧？待放款金额呢？这个时间段总审批金额 0 - 这个时间段总放款金额 20 = -20？

RINK_1 2017-03-03

打赏
举报

SELECT CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '合计' ELSE ISNULL(A.业务员,B.业务员) END 业务员, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过笔数) ELSE MAX(A.通过笔数) END AS 通过笔数 , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.通过金额) ELSE MAX(A.通过金额) END AS 通过金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃笔数]) ELSE MAX(A.[拒单/放弃笔数]) END AS [拒单/放弃笔数], CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.[拒单/放弃金额]) ELSE MAX(A.[拒单/放弃金额]) END AS [拒单/放弃金额] , CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款笔数) ELSE MAX(B.放款笔数) END AS 放款笔数, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(B.放款金额) ELSE MAX(B.放款金额) END AS 放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN SUM(A.总审批金额-B.总放款金额) ELSE MAX(A.总审批金额-B.总放款金额) END AS 待放款金额, CASE WHEN GROUPING(ISNULL(A.业务员,B.业务员))=1 THEN '' ELSE CAST(DENSE_RANK() OVER (ORDER BY MAX(B.放款金额) DESC) AS VARCHAR) END AS 放款排名 FROM (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 通过笔数, SUM(CASE WHEN ZT='通过' AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 通过金额, COUNT(DISTINCT(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS [拒单/放弃笔数], SUM(CASE WHEN ZT IN ('拒绝','放弃') AND CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS [拒单/放弃金额], SUM(CASE WHEN ZT='通过' THEN JE ELSE 0 END) AS 总审批金额 FROM #T1 A GROUP BY XM) AS A FULL JOIN (SELECT XM AS 业务员, COUNT(DISTINCT(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN BH ELSE NULL END)) AS 放款笔数, SUM(CASE WHEN CAST(RQ AS SMALLDATETIME)>='2016-11-01' AND CAST(RQ AS SMALLDATETIME)<='2016-11-30' THEN JE ELSE 0 END) AS 放款金额, SUM(JE) AS 总放款金额 FROM #T2 GROUP BY XM) AS B ON A.业务员=B.业务员 GROUP BY ISNULL(A.业务员,B.业务员) WITH ROLLUP ORDER BY GROUPING(ISNULL(A.业务员,B.业务员)),放款排名

qq_33079425 2017-03-03

打赏
举报

1、待放款金额=总的审批金额-总的放款金额（待放款金额是指某个业务员所有审批状态为”通过“的金额在减去所有已经放款的金额） 2、也就是罗六总共 2016/10/25 通过 50 在减去总放款30 待放款金额为 20 3、同时加入时间过滤之后，罗六的放款笔数与放款金额也要显示出来，这个时间段有可能业务表没有数据。

二月十六 2017-03-03

打赏
举报

引用 2 楼 qq_33079425 的回复:

厉害啊。好像还有点问题，如果按日期过滤的话。现在问题主要出在罗六，如果按时间段：'2016/11/1' 至 '2016/11/30' 查询的话。注：待放款金额=总的审批金额-总的放款金额（不分时间段的）

罗六这个按时间段：'2016/11/1' 至 '2016/11/30' 查询的话待放款是负的？因为业务表这个时间段没有，然后放款表里却有。相减就是负的了

qq_33079425 2017-03-03

打赏
举报

厉害啊。好像还有点问题，如果按日期过滤的话。

现在问题主要出在罗六，如果按时间段：'2016/11/1' 至 '2016/11/30' 查询的话。
注：待放款金额=总的审批金额-总的放款金额（不分时间段的）

二月十六 2017-03-03

打赏
举报

语句：

CREATE TABLE #T1(XM VARCHAR(20),BH VARCHAR(200),JE INT,ZT VARCHAR(200),RQ DATE)

INSERT INTO #T1 VALUES

('张三','99999',100,'通过','2016/10/20'),

('罗六','10000',50,'通过','2016/10/25'),

('张三','10001',100,'通过','2016/11/01'),

('张三','10002',100,'拒绝',' 2016/11/07'),

('张三','10003',200,'通过','2016/11/26'),

('李四','10004',300,'通过','2016/11/03'),

('李四','10005',100,'通过','2016/11/03'),

('李四','10006',300,'放弃','2016/11/15'),        

('李四','10007',200,'拒绝','2016/11/25'),

('王五','10008',300,'通过','2016/11/15'),

('王五','10009',200,'通过','2016/11/25'),

('王五','10010',100,'通过','2016/12/15')

 

CREATE TABLE #T2(XM VARCHAR(20),BH VARCHAR(200),RQ DATE,JE INT)

INSERT INTO #T2 VALUES

('罗六','10000','2016/10/27',10),

('罗六','10000','2016/11/01',20),

('张三','10001','2016/11/03',100),

('张三','10003','2016/11/26',100),

('李四','10004','2016/11/24',100),

('李四','10005','2016/11/24',100),

('李四','10004','2016/11/26',100),

('王五','10008','2016/11/15',150),

('王五','10008','2016/11/19',150),

('王五','10009','2016/12/03',50),

('王五','10009','2016/12/07',50)

;WITH tempa AS (

SELECT xm,zt,COUNT(1) AS 笔数,SUM(JE) AS 金额 FROM #T1 GROUP BY xm,ZT

),tempb AS (

SELECT xm,COUNT(1) AS 笔数,SUM(JE) AS 金额 FROM #T2 GROUP BY XM

),result AS (

SELECT  tempa.xm ,

        SUM(CASE WHEN zt = '通过' THEN tempa.笔数

                 ELSE 0

            END) AS 通过笔数 ,

        SUM(CASE WHEN zt = '通过' THEN tempa.金额

                 ELSE 0

            END) AS 通过金额 ,

        SUM(CASE WHEN zt = '拒绝'

                      OR zt = '放弃' THEN tempa.笔数

                 ELSE 0

            END) AS [拒绝/放弃笔数] ,

        SUM(CASE WHEN zt = '拒绝'

                      OR zt = '放弃' THEN tempa.金额

                 ELSE 0

            END) AS [拒绝/放弃金额] ,

        MAX(tempb.笔数) AS 放款笔数 ,

        MAX(tempb.金额) AS 放款金额 ,

        ( SUM(CASE WHEN zt = '通过' THEN tempa.金额

                   ELSE 0

              END) - MAX(tempb.金额) ) AS 待放款金额 ,

        ROW_NUMBER() OVER ( ORDER BY MAX(tempb.金额) DESC ) AS 放款排名

FROM    tempa

        LEFT JOIN tempb ON tempa.xm = tempb.xm

GROUP BY tempa.xm

)

SELECT * FROM (

SELECT * FROM result

UNION

SELECT  '合计' AS xm ,

        SUM(通过笔数) AS 通过笔数 ,

        SUM(通过金额) AS 通过金额 ,

        SUM([拒绝/放弃笔数]) AS [拒绝/放弃笔数] ,

        SUM([拒绝/放弃金额]) AS [拒绝/放弃金额] ,

        SUM([放款笔数]) AS [放款笔数] ,

        SUM(放款金额) AS 放款金额 ,

		SUM(待放款金额) AS 待放款金额 ,

        9999

FROM    result

)t

ORDER BY t.放款排名



DROP TABLE #T1,#T2