有两张表,一个人员信息,一个是人员变动信息(一个人员对应多条变动信息)怎样一次性查出人员信息和人员的变动信息总条数
如:
ryxxb:
ryid name
1 张三
2 李四
bdxxb:
id ryid
1 1
2 2
3 1
3 1需要查询出的结果为:
ryid name countbd
1 张三 3
2 李四 1countbd为改人员的变动信息总条数
如:
ryxxb:
ryid name
1 张三
2 李四
bdxxb:
id ryid
1 1
2 2
3 1
3 1需要查询出的结果为:
ryid name countbd
1 张三 3
2 李四 1countbd为改人员的变动信息总条数
解决方案 »
- Linq TO SQL group by max 问题
- 网站发布问题
- 如何在asp.net中引用外部的iframe源?我的iframe源是htm文件,引用进来后不能添加控件了。
- 菜鸟中的问题 请大家回答
- 50分!!!大家帮我一下呀。这个文件用什么打开
- 怎么实现用户自定义布局,用户可以选择不同的布局和风格
- 存储过程 select in 再发一次
- 为什么DataGrid不能翻页,点击< 或 >后毫无反应
- EF 出现 基础提供程序在 Open 上失败
- 高分求救!关于Session的问题
- 超高分求解!FineReport报表如何与asp.net配置
- 帮忙看一下这是什么错误..急...在线等..好用马上给分
如:
ryxxb:
ryid name
1 张三
2 李四
bdxxb:
id ryid
1 1
2 2
3 1
4 1 需要查询出的结果为:
ryid name countbd
1 张三 3
2 李四 1 countbd为改人员的变动信息总条数
看看
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
SELECT r.ryid,r.[name],COUNT(b.ryid) AS countbd FROM ryxxb AS r INNER JOIN bdxxb AS b ON r.ryid=b.ryid GROUP BY r.ryid,r.[name]
from ryxxb a inner join
bdxxb b
group by a.ryid ,a.name
from ryxxb a inner join
bdxxb b on a.ryid=b.ryid
group by a.ryid ,a.name ]
用这个. 刚那个没写全!
from ryxxb
declare @t table( ryid int ,[name] varchar(20))insert into @t values(1 , '张三')
insert into @t values(2 , '李四')
insert into @t values(3 , '12')
declare @t1 table( [id] int ,ryid int)insert into @t1 values(1 , 1 )
insert into @t1 values(2 , 2 )
insert into @t1 values(3 , 1 )
insert into @t1 values(3 , 1 )select a.ryid,a.[name] , isnull(b.countbd,0) as countbd from @t a
left outer join (select ryid,Count([id]) as countbd from @t1 group by ryid)b on b.ryid = a.ryid
SQL codeselect ryxxb.ryid,ryname,countbd=(selectcount(*)from bdxxbwhere ryxxb.ryid=ryid)from ryxxb
[/Quote]
反駁,一個sum group by 就可以了,跟行轉列沒關係.
insert into ryxxb select
1 ,'张三' union all select
2 ,'李四'
create table bdxxb(id int, ryid int)
insert into bdxxb select
1 ,1 union all select
2 ,2 union all select
3 ,1 union all select
3 ,1 select ryxxb.ryid,ryxxb.name,countbd=(select count(*) from bdxxb where ryxxb.ryid=ryid)
from ryxxb
ryid name countbd
---------- ---------- -----------
1 张三 3
2 李四 1(2 行受影响)drop table ryxxb,bdxxb
from ryxxb 这个就可以了