首页 新闻 赞助 找找看

SQL语句问题 和 SSRS 问题

0
悬赏园豆:15 [已关闭问题] 关闭于 2016-03-25 20:26

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 不能累加的问题
*/

SSRS 报表   子集数量 不能附加到父级上

多罗贝勒的主页 多罗贝勒 | 初学一级 | 园豆:16
提问于:2016-03-19 17:39
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册