◆交叉表问题请教◆

hs1983 2008-10-20 09:13:58
请教过如下问题！

表名：Table1；字段ID（int 递增），YHID（int 用户ID），CPID（int 产品ID），MKID（int 产品模块ID）,FWSJ(datetime 模块访问时间)。每个产品ID(CPID)都包括多个产品模块ID(MKID),CPID和MKID数量不定.

ID YHID CPID MKID FWSJ
1 1001 1 101 2007-07-18 16:44:51.000
2 1006 3 301 2007-07-24 22:12:16.000
3 1001 1 103 2007-08-08 12:32:43.000
4 1003 1 103 2007-08-09 14:48:52.000
5 1003 2 201 2007-08-09 15:22:28.000
6 1005 1 102 2007-01-03 15:23:35.000

表名：Table2，ID (int 递增)，MKID(int 产品模块ID)，MKMC(varchar(50) 产品模块名称)

ID MKID MKMC
1 101 模块1
2 102 模块2
3 103 模块3
4 201 模块4
5 301 模块5

1.求各模块访问总次数：

模块1 模块2 模块3 模块4 模块5
1 1 2 1 1

2.求每个用户各模块访问次数：
用户ID 模块1 模块2 模块3 模块4 模块5
1001 1 0 1 0 0
1003 0 0 1 1 0
1005 0 1 0 0 0
1006 0 0 0 0 1
...全文
116 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
wer123q 2008-10-20
  • 打赏
  • 举报
 回复 
Create table #T1([ID] int,[YHID] int,[CPID] int,[MKID] int,[FWSJ] Datetime)

Insert #T1

select 1,1001,1,101,'2007-07-18 16:44:51.000' union all

select 2,1006,3,301,'2007-07-24 22:12:16.000' union all

select 3,1001,1,103,'2007-08-08 12:32:43.000' union all

select 4,1003,1,103,'2007-08-09 14:48:52.000' union all

select 5,1003,2,201,'2007-08-09 15:22:28.000' union all

select 6,1005,1,102,'2007-01-03 15:23:35.000'

Go

Create table #T2([ID] int,[MKID] int,[MKMC] nvarchar(3))

Insert #T2

select 1,101,N'模块1' union all

select 2,102,N'模块2' union all

select 3,103,N'模块3' union all

select 4,201,N'模块4' union all

select 5,301,N'模块5'

Go

------1--------

declare

	@str varchar(1000)

	set @str=''

	select @str=@str+','+'max(case mkmc when '''+mkmc+''' then su else 0 end)'''+mkmc+''''

	from (select mkmc from #t2)aa

	set @str = substring(@str,2,len(@str))

	select @str = 'select '+ @str +' from  (select mkmc,su=(select count(1) from #T1 where MKID = #t2.MKID) from #T2)a'

	exec (@str)



------------2-------------

declare

	@str varchar(1000)

	set @str = 'select yhid '

	select @str = @str +','+ 'max(case mkmc when '''+mkmc+''' then 1 else 0  end)'''+mkmc+''''

	from (select mkmc from #t2)aa

	select @str=@str+' from (select yhid,mkmc from #t1 inner join #t2 

on

#t1.MKID=#t2.MKID)ab group by yhid'

exec(@str)
lonlyhawk 2008-10-20
  • 打赏
  • 举报
 回复 
--生成测试数据           

select 1 [ID],1001 YHID,1 CPID,101 MKID,'2007-07-18 16:44:51.000 ' FWSJ into #t1 union 

select 2,1006,3,301,'2007-07-24 22:12:16.000' union 

select 3,1001,1,103,'2007-08-08 12:32:43.000' union 

select 4,1003,1,103,'2007-08-09 14:48:52.000' union 

select 5,1003,2,201,'2007-08-09 15:22:28.000' union 

select 6,1005,1,102,'2007-01-03 15:23:35.000' 

    

select 1 [id],101 MKID,'模块1' MKMC into #t2 union

select 2,102,'模块2' union

select 3,103,'模块3' union 

select 4,201,'模块4' union 

select 5,301,'模块5' 





--交叉查询

declare @sql varchar(1000)

select @sql='select YHID=isnull(cast(YHID as varchar(10)),''合计''), '

select @sql=@sql+'sum(case when MKMC='''+MKMC +''' then 1 else 0 end ) '+MKMC+',' from #t2

select @sql=left(@sql,len(@sql)-1) + ' from #t1 a left join #t2 b on a.MKID=b.MKID  group by YHID with rollup'

exec(@sql)



--查询结果

/*

1001	1	0	1	0	0

1003	0	0	1	1	0

1005	0	1	0	0	0

1006	0	0	0	0	1

合计	1	1	2	1	1

*/
hs1983 2008-10-20
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 roy_88 的回复:]
SQL code--> -->ifnotobject_id('Tempdb..#T1')isnulldroptable#T1GoCreatetable#T1([ID]int,[YHID]int,[CPID]int,[MKID]int,[FWSJ]Datetime)Insert#T1select1,1001,1,101,'2007-07-18 16:44:51.000'unionallselect2,1006,3,301,'2007-07-24 22:12:16.000'unionallselect3,1001,1,103,'2007-08-08 12:32:43.000'unionallselect4,1003,1,103,'2007-08-09 14:48:52.000'unionallselect5,1003,2,201,'2007-08-09 15:22:28.000'union…
[/Quote]
厉害!MS-SQL Server版牛人就是多。我试一下，没有问题就结贴！
csdyyr 2008-10-20
  • 打赏
  • 举报
 回复 




CREATE TABLE TA(ID INT , YHID INT,  CPID INT,  MKID INT ,   FWSJ DATETIME)

INSERT TA

SELECT 1,  1001,  1,  101,  '2007-07-18 16:44:51.000' UNION ALL 

SELECT 2,  1006,  3,  301,  '2007-07-24 22:12:16.000' UNION ALL 

SELECT 3,  1001,  1,  103,  '2007-08-08 12:32:43.000' UNION ALL 

SELECT 4,  1003,  1,  103,  '2007-08-09 14:48:52.000' UNION ALL 

SELECT 5,  1003,  2,  201,  '2007-08-09 15:22:28.000' UNION ALL 

SELECT 6,  1005,  1,  102,  '2007-01-03 15:23:35.000'



CREATE TABLE TB(ID INT,  MKID INT,  MKMC NVARCHAR(10))

INSERT TB

SELECT 1,  101,  '模块1' UNION ALL 

SELECT 2,  102,  '模块2' UNION ALL 

SELECT 3,  103,  '模块3' UNION ALL 

SELECT 4,  201,  '模块4' UNION ALL 

SELECT 5,  301,  '模块5'



DECLARE @SQL VARCHAR(8000)

SET @SQL=''

SELECT @SQL=@SQL+','+' SUM(CASE WHEN MKID='''+RTRIM(MKID)+''' THEN 1 ELSE 0 END) AS '''+MKMC+''''

FROM TB



SET @SQL='SELECT YHID'+@SQL+' FROM TA GROUP BY YHID '



--PRINT @SQL

EXEC(@SQL)



DROP TABLE TA

DROP TABLE TB

/*

YHID        模块1         模块2         模块3         模块4         模块5         

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

1001        1           0           1           0           0

1003        0           0           1           1           0

1005        0           1           0           0           0

1006        0           0           0           0           1

*/
csdyyr 2008-10-20
  • 打赏
  • 举报
 回复 


CREATE TABLE TA(ID INT , YHID INT,  CPID INT,  MKID INT ,   FWSJ DATETIME)

INSERT TA

SELECT 1,  1001,  1,  101,  '2007-07-18 16:44:51.000' UNION ALL 

SELECT 2,  1006,  3,  301,  '2007-07-24 22:12:16.000' UNION ALL 

SELECT 3,  1001,  1,  103,  '2007-08-08 12:32:43.000' UNION ALL 

SELECT 4,  1003,  1,  103,  '2007-08-09 14:48:52.000' UNION ALL 

SELECT 5,  1003,  2,  201,  '2007-08-09 15:22:28.000' UNION ALL 

SELECT 6,  1005,  1,  102,  '2007-01-03 15:23:35.000'





CREATE TABLE TB(ID INT,  MKID INT,  MKMC NVARCHAR(10))

INSERT TB

SELECT 1,  101,  '模块1' UNION ALL 

SELECT 2,  102,  '模块2' UNION ALL 

SELECT 3,  103,  '模块3' UNION ALL 

SELECT 4,  201,  '模块4' UNION ALL 

SELECT 5,  301,  '模块5'



DECLARE @SQL VARCHAR(8000)

SET @SQL=''

SELECT @SQL=@SQL+','+' SUM(CASE WHEN MKID='''+RTRIM(MKID)+''' THEN 1 ELSE 0 END) AS '''+MKMC+''''

FROM TB



SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+' FROM TA '



--PRINT @SQL

EXEC(@SQL)



DROP TABLE TA

DROP TABLE TB

/*

模块1         模块2         模块3         模块4         模块5         

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

1           1           2           1           1



*/
中国风 2008-10-20
  • 打赏
  • 举报
 回复 
--> --> 

 

if not object_id('Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([ID] int,[YHID] int,[CPID] int,[MKID] int,[FWSJ] Datetime)

Insert #T1

select 1,1001,1,101,'2007-07-18 16:44:51.000' union all

select 2,1006,3,301,'2007-07-24 22:12:16.000' union all

select 3,1001,1,103,'2007-08-08 12:32:43.000' union all

select 4,1003,1,103,'2007-08-09 14:48:52.000' union all

select 5,1003,2,201,'2007-08-09 15:22:28.000' union all

select 6,1005,1,102,'2007-01-03 15:23:35.000'

Go

--> --> 

 

if not object_id('Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([ID] int,[MKID] int,[MKMC] nvarchar(3))

Insert #T2

select 1,101,N'模块1' union all

select 2,102,N'模块2' union all

select 3,103,N'模块3' union all

select 4,201,N'模块4' union all

select 5,301,N'模块5'

Go

--1

declare @s nvarchar(4000)

set @s=''

select @s=@s+','+quotename([MKMC])+'=sum(case when [MKID]='+rtrim([MKID])+' then 1 else 0 end)' from #T2

set @s=stuff(@s,1,1,'')

exec('select '+@s+' from #T1')





go

declare @s nvarchar(4000)

set @s='select [YHID]'

select @s=@s+','+quotename([MKMC])+'=sum(case when [MKID]='+rtrim([MKID])+' then 1 else 0 end)' from #T2

exec(@s+' from #T1 group by [YHID]')







(6 個資料列受到影響)



(5 個資料列受到影響)

模块1         模块2         模块3         模块4         模块5

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

1           1           2           1           1



(1 個資料列受到影響)



YHID        模块1         模块2         模块3         模块4         模块5

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

1001        1           0           1           0           0

1003        0           0           1           1           0

1005        0           1           0           0           0

1006        0           0           0           0           1



(4 個資料列受到影響)
hs1983 2008-10-20
  • 打赏
  • 举报
 回复
declare @sql varchar(8000)
set @sql ='select YHID,'
select @sql = @sql +'sum(case when MKID=''' + ltrim(MKID) + ''' then 1 else 0 end) ''' + ltrim(MKID)+ ''','
from (select distinct MKID from Table1) as a
set @sql = left(@sql,len(@sql)-1)+'from Table1 group by YHID '
exec (@sql)

以上代码现可实现：
YHID 101 102 103 201 301
1001 1 0 1 0 0
1003 0 0 1 1 0
1005 0 1 0 0 0
1006 0 0 0 0 1

如何关联Table2,实现中文表头:用户ID 模块1 模块2 模块3 模块4 模块5
水族杰纶 2008-10-20
  • 打赏
  • 举报
 回复 
普通行列转换

问题：假设有张学生成绩表(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 课程

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



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



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

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

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

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

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

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

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

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 + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

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

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

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

exec ('select m.* , n.平均分 , n.总分 from

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

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb    



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

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



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

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

姓名 课程 分数 

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

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

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

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

union all

select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

union all

select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



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

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

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

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

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

*/



select * from

(

select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

union all

select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

union all

select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

union all

select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

union all

select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
hs1983 2008-10-20
  • 打赏
  • 举报
 回复
declare @sql varchar(8000)
set @sql ='select YHID,'
select @sql = @sql +'sum(case when MKID=''' + ltrim(MKID) + ''' then 1 else 0 end) ''' + ltrim(MKID)+ ''','
from (select distinct MKID from Table1) as a
set @sql = left(@sql,len(@sql)-1)+'from Table1 group by YHID '
exec (@sql)

以上代码现可实现：
YHID 101 102 103 201 301
1001 1 0 1 0 0
1003 0 0 1 1 0
1005 0 1 0 0 0
1006 0 0 0 0 1

如何关联Table2,实现中文表头:用户ID 模块1 模块2 模块3 模块4 模块5
rucypli 2008-10-20
  • 打赏
  • 举报
 回复
经典行列转换
2万字长文说清自动驾驶仿真的8大问题
交流群 |进“传感器群/滑板底盘群”请加微信号：xsh041388交流群 |进“汽车基础软件群”请加微信号：...去年4月份的疫情期间，偶尔得到了一次跟某仿真公司创始人闲聊的机会，笔者便趁机向其请教了许多问题。此后，为...
Word、Excel操作技巧大全
在插入或删除图、表、公式时编号的维护就成为一个大问题，比如若在第二章的第一张图（图2－1）前插入一张图，则原来的图2－1变为2－2，2－2变为2－3，…，更糟糕的是，文档中还有很多对这些编号的引用，比如“流程图...
WEB开发文档2 总结
http://blog.donews.com/lvjiyong/archive/2006/06/29/931071.aspx怎样将后台生成的在内存中的图象显示到客户端Microsoft IE WebControls下载...显示数据下载中文名文件时保存文件名乱码问题关于用ASP.net绘图的问题...
神经网络自动布局技术,神经网络自动布局原理
◆同时运行原理图和PCB，在打开的原理图和PCB图间允许双向交叉查找元器件、引脚、网络 ◆既可以进行正向注释元器件标号（由原理图到PCB），也可以进行反向注释（由PCB到原理图），以保持电气原理图和PCB在设计上的...
MS-SQL Server 基础类 - SQL语句
请教一个树形表的SQL交叉表查询。 求SQL写法：在表中求出一字段有相等值的纪录． 为什么使用了DISTINCT后，还有重复项？? 请教datetime的数据类型只需其中之一的修改 用SQL语句如何查看表结构，如何得到sp_helpuser...
MS-SQL Server

34,593

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章