这种报表sql怎么写

nitaiyoucala 2017-05-10 05:08:02
建表语句如下：
CREATE TABLE [dbo].[table1](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[intention] [nvarchar](50) NULL,

	[MatchesMan] [nvarchar](50) NULL,

	[DeptName] [nvarchar](50) NULL

) ON [PRIMARY]



GO

SET IDENTITY_INSERT [dbo].[table1] ON 



GO

INSERT [dbo].[table1] ([ID], [intention], [MatchesMan], [DeptName]) VALUES (1, N'类型1', N'张三', N'一部')

GO

INSERT [dbo].[table1] ([ID], [intention], [MatchesMan], [DeptName]) VALUES (2, N'类型2', N'王五', N'一部')

GO

INSERT [dbo].[table1] ([ID], [intention], [MatchesMan], [DeptName]) VALUES (7, N'类型1', N'张三', N'一部')

GO

INSERT [dbo].[table1] ([ID], [intention], [MatchesMan], [DeptName]) VALUES (5, N'类型2', N'张三', N'一部')

GO

INSERT [dbo].[table1] ([ID], [intention], [MatchesMan], [DeptName]) VALUES (6, N'类型1', N'李四', N'二部')

GO

SET IDENTITY_INSERT [dbo].[table1] OFF

GO


要实现下面效果


下面是我写的一半，求修改


declare @sql nvarchar(4000)

set @sql = 'select MatchesMan 名称'

select @sql = @sql + ',sum(isnull(case intention when '''+RTRIM(intention)+''' then 1 end,0)) as ['+RTRIM(intention)+']'

from (select distinct intention from table1) as a  order by intention

select @sql = @sql+', sum(1) as 总计 from [table1] '

select @sql=@sql+' group by MatchesMan'

exec (@sql)
...全文
478 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
nitaiyoucala 2017-05-11
  • 打赏
  • 举报
 回复
引用 7 楼 sinat_28984567 的回复:
[quote=引用 6 楼 nitaiyoucala 的回复:] [quote=引用 4 楼 sinat_28984567 的回复:] 

DECLARE @sql NVARCHAR(4000)
SET @sql = 'select MatchesMan 名称'
SELECT  @sql = @sql + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql = @sql + ', sum(1) as 总计 from [table1] '
SELECT  @sql = @sql + ' group by MatchesMan'
DECLARE @sql1 NVARCHAR(4000)
SET @sql1 = 'select DeptName 名称'
SELECT  @sql1 = @sql1 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql1 = @sql1 + ', sum(1) as 总计 from [table1] '
SELECT  @sql1 = @sql1 + ' group by DeptName'
DECLARE @sql2 NVARCHAR(4000)
SET @sql2 = 'select ''总计'' 名称'
SELECT  @sql2 = @sql2 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql2 = @sql2 + ', sum(1) as 总计 from [table1] '
EXEC (@sql+' UNION '+ @sql1 + ' UNION '+@sql2 )
感觉还是有点bug。如果我名称里面的 部门名称是不是一部，二部这种 用UNION 去拼接会造成排序问题，部门会堆到一起了，而不是先是部门，再是部门下面的员工名称。[/quote] 可以再排序，或者看看5#那个用ROLLUP实现的 是否符合要求[/quote] 他那也不行。
二月十六 版主 2017-05-11
  • 打赏
  • 举报
 回复
引用 6 楼 nitaiyoucala 的回复:
[quote=引用 4 楼 sinat_28984567 的回复:] 

DECLARE @sql NVARCHAR(4000)
SET @sql = 'select MatchesMan 名称'
SELECT  @sql = @sql + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql = @sql + ', sum(1) as 总计 from [table1] '
SELECT  @sql = @sql + ' group by MatchesMan'
DECLARE @sql1 NVARCHAR(4000)
SET @sql1 = 'select DeptName 名称'
SELECT  @sql1 = @sql1 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql1 = @sql1 + ', sum(1) as 总计 from [table1] '
SELECT  @sql1 = @sql1 + ' group by DeptName'
DECLARE @sql2 NVARCHAR(4000)
SET @sql2 = 'select ''总计'' 名称'
SELECT  @sql2 = @sql2 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql2 = @sql2 + ', sum(1) as 总计 from [table1] '
EXEC (@sql+' UNION '+ @sql1 + ' UNION '+@sql2 )
感觉还是有点bug。如果我名称里面的 部门名称是不是一部，二部这种 用UNION 去拼接会造成排序问题，部门会堆到一起了，而不是先是部门，再是部门下面的员工名称。[/quote] 可以再排序，或者看看5#那个用ROLLUP实现的 是否符合要求
nitaiyoucala 2017-05-11
  • 打赏
  • 举报
 回复
引用 4 楼 sinat_28984567 的回复:

DECLARE @sql NVARCHAR(4000)
SET @sql = 'select MatchesMan 名称'
SELECT  @sql = @sql + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql = @sql + ', sum(1) as 总计 from [table1] '
SELECT  @sql = @sql + ' group by MatchesMan'
DECLARE @sql1 NVARCHAR(4000)
SET @sql1 = 'select DeptName 名称'
SELECT  @sql1 = @sql1 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql1 = @sql1 + ', sum(1) as 总计 from [table1] '
SELECT  @sql1 = @sql1 + ' group by DeptName'
DECLARE @sql2 NVARCHAR(4000)
SET @sql2 = 'select ''总计'' 名称'
SELECT  @sql2 = @sql2 + ',sum(isnull(case intention when ''' + RTRIM(intention)
        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'
FROM    ( SELECT DISTINCT
                    intention
          FROM      table1
        ) AS a
ORDER BY intention
SELECT  @sql2 = @sql2 + ', sum(1) as 总计 from [table1] '
EXEC (@sql+' UNION '+ @sql1 + ' UNION '+@sql2 )
感觉还是有点bug。如果我名称里面的 部门名称是不是一部，二部这种 用UNION 去拼接会造成排序问题，部门会堆到一起了，而不是先是部门，再是部门下面的员工名称。
nitaiyoucala 2017-05-11
  • 打赏
  • 举报
 回复
引用 9 楼 RINK_1 的回复:
DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+',','')+'CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END)/2 ELSE SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END) END AS '+INTENTION FROM (SELECT INTENTION FROM (SELECT INTENTION,MATCHESMAN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME FROM TABLE) AS A GROUP BY INTENTION) AS A SET @SQL='SELECT CASE WHEN GROUPING(MATCHESMAN)=1 THEN ''合计'' ELSE MATCHESMAN END AS 名称,' +@SQL+',CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(1)/2 ELSE SUM(1) END AS 合计 FROM (SELECT INTENTION,MATCHESMAN,DEPTNAME,1 AS RN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME,DEPTNAME,2 FROM TABLE) AS A GROUP BY MATCHESMAN WITH ROLLUP ORDER BY GROUPING(MATCHESMAN) DESC,MAX(DEPTNAME),MAX(RN) DESC' EXEC(@SQL)
大牛，谢谢了
RINK_1 2017-05-11
  • 打赏
  • 举报
 回复
DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+',','')+'CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END)/2 ELSE SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END) END AS '+INTENTION FROM (SELECT INTENTION FROM (SELECT INTENTION,MATCHESMAN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME FROM TABLE) AS A GROUP BY INTENTION) AS A SET @SQL='SELECT CASE WHEN GROUPING(MATCHESMAN)=1 THEN ''合计'' ELSE MATCHESMAN END AS 名称,' +@SQL+',CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(1)/2 ELSE SUM(1) END AS 合计 FROM (SELECT INTENTION,MATCHESMAN,DEPTNAME,1 AS RN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME,DEPTNAME,2 FROM TABLE) AS A GROUP BY MATCHESMAN WITH ROLLUP ORDER BY GROUPING(MATCHESMAN) DESC,MAX(DEPTNAME),MAX(RN) DESC' EXEC(@SQL)
RINK_1 2017-05-10
  • 打赏
  • 举报
 回复
DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+',','')+'CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END)/2 ELSE SUM(CASE WHEN INTENTION='''+INTENTION+''' THEN 1 ELSE 0 END) END AS '+INTENTION FROM (SELECT INTENTION FROM (SELECT INTENTION,MATCHESMAN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME FROM TABLE) AS A GROUP BY INTENTION) AS A SET @SQL='SELECT CASE WHEN GROUPING(MATCHESMAN)=1 THEN ''合计'' ELSE MATCHESMAN END AS 名称,' +@SQL+',CASE WHEN GROUPING(MATCHESMAN)=1 THEN SUM(1)/2 ELSE SUM(1) END AS 合计 FROM (SELECT INTENTION,MATCHESMAN FROM TABLE UNION ALL SELECT INTENTION,DEPTNAME FROM TABLE) AS A GROUP BY MATCHESMAN WITH ROLLUP' EXEC(@SQL)
二月十六 版主 2017-05-10
  • 打赏
  • 举报
 回复 


DECLARE @sql NVARCHAR(4000)

SET @sql = 'select MatchesMan 名称'

SELECT  @sql = @sql + ',sum(isnull(case intention when ''' + RTRIM(intention)

        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'

FROM    ( SELECT DISTINCT

                    intention

          FROM      table1

        ) AS a

ORDER BY intention

SELECT  @sql = @sql + ', sum(1) as 总计 from [table1] '

SELECT  @sql = @sql + ' group by MatchesMan'

DECLARE @sql1 NVARCHAR(4000)

SET @sql1 = 'select DeptName 名称'

SELECT  @sql1 = @sql1 + ',sum(isnull(case intention when ''' + RTRIM(intention)

        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'

FROM    ( SELECT DISTINCT

                    intention

          FROM      table1

        ) AS a

ORDER BY intention

SELECT  @sql1 = @sql1 + ', sum(1) as 总计 from [table1] '

SELECT  @sql1 = @sql1 + ' group by DeptName'

DECLARE @sql2 NVARCHAR(4000)

SET @sql2 = 'select ''总计'' 名称'

SELECT  @sql2 = @sql2 + ',sum(isnull(case intention when ''' + RTRIM(intention)

        + ''' then 1 end,0)) as [' + RTRIM(intention) + ']'

FROM    ( SELECT DISTINCT

                    intention

          FROM      table1

        ) AS a

ORDER BY intention

SELECT  @sql2 = @sql2 + ', sum(1) as 总计 from [table1] '

EXEC (@sql+' UNION '+ @sql1 + ' UNION '+@sql2 )


nitaiyoucala 2017-05-10
  • 打赏
  • 举报
 回复
引用 2 楼 sinat_28984567 的回复:
没看到楼主后边那个自己写的。。。。 直接用1#那个语句行吗？
intention 这个是动态的。。能否按照我这个改一下？
二月十六 版主 2017-05-10
  • 打赏
  • 举报
 回复
没看到楼主后边那个自己写的。。。。 直接用1#那个语句行吗？
二月十六 版主 2017-05-10
  • 打赏
  • 举报
 回复 
SELECT  MatchesMan AS 名称 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) AS 类型1 ,

        SUM(CASE WHEN intention = '类型2' THEN 1

                 ELSE 0

            END) AS 类型2 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) + SUM(CASE WHEN intention = '类型2' THEN 1

                            ELSE 0

                       END) AS 合计

FROM    [table1]

GROUP BY MatchesMan

UNION

SELECT  DeptName AS 名称 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) AS 类型1 ,

        SUM(CASE WHEN intention = '类型2' THEN 1

                 ELSE 0

            END) AS 类型2 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) + SUM(CASE WHEN intention = '类型2' THEN 1

                            ELSE 0

                       END) AS 合计

FROM    [table1]

GROUP BY DeptName

UNION

SELECT  '总计' AS 名称 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) AS 类型1 ,

        SUM(CASE WHEN intention = '类型2' THEN 1

                 ELSE 0

            END) AS 类型2 ,

        SUM(CASE WHEN intention = '类型1' THEN 1

                 ELSE 0

            END) + SUM(CASE WHEN intention = '类型2' THEN 1

                            ELSE 0

                       END) AS 合计

FROM    dbo.table1



报表设计中的几个SQL技巧
使用专业的报表工具设计报表模板时，数据准备是一个关键环节。数据准备的核心，实际上是数据库查询SQL语句的编写。 在一个典型的交叉分析报表中，需要显示每个大区、每个月度的销售业绩，如下图： 这个报表的数据集查询语句大致如下： select DATEPART(month,订购日期) 月份 , 销售大区 , SUM(订单金额) 销售额 from Demo_销售明细 where 订购日期 >...
sql报表总结
前言： 报表中最常使用where字句、group by 分组、having分组过滤、union、union all、子查询、with rollup、以及内置函数等。 Where 字句使用(简单讲下) 项目中使用Mybatis持久层框架，优点是SQL灵活，缺点对开发人员SQL编写要求比较高。 一般where字句建议<if test=””></if>这种动态...
mysql报表统计 优化_关于SQL调优与报表的性能优化
前一段时间做了某产品实时统计的报表，这周在对报表性能进行优化。其中逐步积累了一些优化经验，总结一下记录下来，欢迎大家一起探讨。本文仅限于探讨单个数据库的查询sql及统计报表调优，不涉及分布式数据库架构或垂直、水平扩展等知识。(本文中的统计数据库为Mysql)一、SQL调优：1、提升统计查询性能，首当其冲当然还是对查询SQL进行优化，说到SQL调优，不得不先说一下Mysql里SQL的执行顺序。网上查...
JimuReport积木报表 — SQL数据源报表带参展示
在上一节数据源报表说明了一下该如何使用，本章说明带参改怎样使用。 在线使用地址：http://www.jimureport.com/ 一、数据源编辑，查看 1、点击“编辑”按钮 2、修改sql语句（本章以id为例） 1） 拼写格式 ‘${}’ 2） 点击SQL解析，如下图所示 参数：即${}里面的 参数文本：即参数的描述 默认值：当前参数${}里面默认值 3）点击确定按钮即可添加成功； 3、页面访问传参 点击“保存”和“预览”按钮，即可对信息进行保存； 4、预览的时候，需要在浏览器中输入参数
Python+大数据-SQL进阶-报表项目
Python+大数据-SQL进阶-报表项目
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章