交叉表的做法

2011-08-16 04:07:36 145 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SqlServer如何生成动态交叉表查询 SqlServer如何生成动态交叉表查询 fuxc（原作）关键字交叉表 SQL 存储过程 VB+MS SqlServer，是我们目前开发数据库应用系统最常用的模式，翻翻以前的老帖子，有一些SqlServer的问题经常被提出来，但正确解答甚少，现把我对这些问题的见解贴出来，这次先讲讲动态交叉表的问题为了说明问题，我们用SqlServer自带的事例数据库(Nor 阅读详情

主表：rd
从表：rds

rd
id 部门日期收发标记（1表示收，0表示发）

rds
autoid id 存货编码单重

现做成的交叉表为：

收入发出
日期部门1 部门2 部门3 部门1 部门2 部门3
1 x x x x
2
3
4
5
6
7

x是表示每一天，所有存货在该部门的收入或发出的总重量

请高手比出语句，并且这遇到这种报表时，你们是如何分解的?

...全文

告别手动复制粘贴！用Python脚本+pywinauto实现PC微信消息自动发送（附完整源码）本文详细介绍了如何利用Python和pywinauto实现PC微信消息的自动化发送，告别繁琐的手动操作。从环境搭建、UI控件定位到构建健壮的发送流程，再到高级功能扩展和项目打包部署，全面解析了自动化办公的实现方案，显著提升工作效率。阅读详情

tgtcn

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

AcHerat 2011-08-19

打赏
举报

自己去照着8楼的做！

tgtcn 2011-08-19

打赏
举报

补充数据，以部门固定来讲，简单一点,应如何写，多谢了

tgtcn 2011-08-19

打赏
举报

rd
id 部门日期收发标记（1表示收，0表示发）
1 注塑部 2011-8-1 收
2 装配部 2011-8-1 收
3 包装部 2011-8-4 收
4 注塑部 2011-8-6 发
5 包装部 2011-8-1 发
6 注塑部 2011-8-6 发

rds
autoid id 存货编码单重
001 1 a 20
002 1 b 30
003 2 a 60
004 3 b 20
005 4 d 15
006 5 e 10

现做成的交叉表为：

收入发出
日期注塑部装配部包装部注塑部装配部包装部
2011-8-1 50 30 10
2011-8-4 20
2011-8-6 15

Lyongt 2011-08-16

打赏
举报

部门数量不确定的话需要动态构建



Declare @Dept Varchar(20), @Status Int, @Sql NVarchar(4000) 

Set @Sql = 'Select 日期,  '

Declare Cur_Dept Cursor for

	Select Distinct 部门, 收发标记  From rd

Open Cur_Dept;

Fetch Next From Cur_Dept Into @Dept, @Status;

While @@FETCH_STATUS = 0

Begin

	if @Status = 1 Set @Sql = @Sql + 'Case  When (T1.部门 = '''+@Dept+''' And 收发标记 = 1) Then 单重 Else Null End As '+@Dept+'收入,'

	Else Set @Sql = @Sql + 'Case  When (T1.部门 = '''+@Dept+''' And 收发标记 = 1) Then 单重 Else Null End As '+@Dept+'发出,'

	Fetch Next From Cur_Dept Into @Dept, @Status;	

End

Set @Sql = Left(@Sql, LEN(@Sql)-1) + ' From rd T1 Left Join rds T2 On T1.id = T2.id'



Exec(@Sql)

Close Cur_Dept;

Deallocate Cur_Dept;

Lyongt 2011-08-16

打赏
举报

如果不确定有几个部门，就需要动态创建列了，一条语句是完不成的。

Lyongt 2011-08-16

打赏
举报

我是问楼主总共有多少各部门是不是确定的，如果是确定的就一条Case语句即可

测试源码如下



Declare @rd Table (

	id			Int  ,

	部门		varchar(20),

	日期		DateTime,

	收发标记	int

)



InSert Into @rd 

Select 1, '部门1', '2011-08-01', 1

Union 

Select 2, '部门2', '2011-08-03', 1

Union

Select 3, '部门2', '2011-08-05', 0

Union

Select 4, '部门3', '2011-08-03', 0

Union

Select 5, '部门3', '2011-08-05', 1



Declare @rds Table(

	autoid		Int identity(1,1),

	id			Int,

	存货编码	varchar(20),

	单重		Decimal(8,2)

)



InSert Into @rds

Select 1, '1101', 28.05

Union

Select 2, '1102', 108.91

Union

Select 3, '1105', 89.77

Union

Select 4, '1103', 228

Union

Select 5, '1104', 93.5



Select 日期, Case  When (T1.部门 = '部门1' And 收发标记 = 1) Then 单重 Else Null End As '部门1收入',

	Case  When (T1.部门 = '部门2' And 收发标记 = 1) Then 单重 Else Null End As '部门2收入',

	Case  When (T1.部门 = '部门3' And 收发标记 = 1) Then 单重 Else Null End As '部门3收入',

	Case  When (T1.部门 = '部门1' And 收发标记 = 0) Then 单重 Else Null End As '部门1发出',

	Case  When (T1.部门 = '部门2' And 收发标记 = 0) Then 单重 Else Null End As '部门2发出',

	Case  When (T1.部门 = '部门3' And 收发标记 = 0) Then 单重 Else Null End As '部门3发出'

From @rd T1 Left Join @rds T2 On T1.id = T2.id

稻草_木偶 2011-08-16

打赏
举报

学习了~thx

快溜 2011-08-16

打赏
举报

你最好给点真实数据。问者模糊，答者更模糊。

tgtcn 2011-08-16

打赏
举报

一天里一个部门不一定有出，或者只有入的

Lyongt 2011-08-16

打赏
举报

部门固定的话用Case语句，不固定的话用动态SQL构建即可。

--小F-- 2011-08-16

打赏
举报

*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。



问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

---- ---- ---- ----

李四 74   84   94

张三 74   83   93

-------------------

*/



create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)

go



--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名



--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程

set @sql = '[' + @sql + ']'

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

--小F-- 2011-08-16

打赏
举报

其实就是动态的行列转换

AcHerat 2011-08-16

打赏
举报



declare @sql varchar(8000)

set @sql = 'select convert(varchar(10),a.日期,120) as 日期'

select @sql = @sql + 

           ',sum(case when a.部门='''++''' and a.收发标记=1 then b.单重 else 0 end) ['++'收入]'

          +',sum(case when a.部门='''++''' and a.收发标记=0 then b.单重 else 0 end) ['++'发出]'

from tb

group by 部门

order by 部门

select @sql = @sql + ' from rd a join rds b on a.id = b.id group by convert(varchar(10),a.日期,120)'

exec(@sql)

交叉表的做法

相关推荐