USE [CESHI]
GO
/****** 创建表A ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](50) NULL,
    [DEP_FIND] [decimal](18, 0) NULL,
    [USER_FIND] [decimal](18, 0) NULL,
    [LEVE] [int] NULL,
    [CREATE_DEP_CODE] [varchar](50) NULL,
    [CREATE_DEP_ID] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
USE [CESHI]
GO
 
/****** Object:  Table [dbo].[Table_2]    Script Date: 2016/3/19 16:48:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Table_2](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](50) NULL,
    [CODE] [varchar](50) NULL,
    [PID] [bigint] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
USE [CESHI]
GO
 
 
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门1','11010',0)
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门2','11011',1)
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门3','110111',2)
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门4','11012',1)
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门5','110112',2)
INSERT INTO [dbo].[Table_2]([NAME],[CODE],[PID])
     VALUES ('部门6','1101111',3)
 
 
 
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值1',1000,100,1,'11011',1)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值2',1000,100,2,'11011',2)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值3',1000,100,2,'11011',2)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值4',1000,100,3,'110111',3)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值5',1000,100,3,'110111',3)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值6',1000,100,3,'11012',4)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值7',1000,100,3,'11012',4)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值8',1000,100,3,'110112',5)
INSERT INTO [dbo].[Table_1]([NAME],[DEP_FIND],[USER_FIND],[LEVE],[CREATE_DEP_CODE],[CREATE_DEP_ID])
     VALUES('值9',1000,100,3,'1101111',6)
GO
 

SELECT B.ID as DEP_ID,
       B.[PID] as DEP_ID_P,
    A.[LEVE],
    COUNT(A.ID) as 数量,
    SUM(A.[DEP_FIND]) as 单位罚款,
    SUM(A.[USER_FIND]) as 个人罚款   
FROM Table_1 A
    inner join  Table_2 B on substring(A.[CREATE_DEP_CODE],1, len(B.[CODE]))=B.[CODE]
GROUP BY B.ID,A.[LEVE],B.[PID]
 
/*
两个问题
如何在LEVE和罚款的上级增加一个分类
就是伸展开来个人罚款和单位罚款有一个父级，可以将他俩缩进
等级同理增加一个父级可以将等级的分类缩进后是一个集合（是所有等级加起来一个的和）
*/
 
/*
substring(A.[CREATE_DEP_CODE],1, len(B.[CODE]))=B.[CODE]
这部分为什么这样写 是因为父部门要得到子部门和部门下面所有的数量，
如果只得到本部的数量然后SUM不到 一起
是因为报表上的递归功能
 
熟悉 SSRS 的朋友也可以帮忙解决一下  递归列的时候 SUM 不能累加的问题
*/