ID Name Type Score
1 a 语文 80
2 a 数学 50
3 a 英语 70
4 b 语文 90
5 b 数学 68
6 b 英语 85
7 c 语文 79
8 c 数学 88
9 c 英语 76Name 语文 数学 英语
a 80 50 70
b 90 68 85
c 79 88 76---------------------------------------
ID Name Type Score
1 a 语文 80
2 b 语文 90
3 c 语文 79
4 a 数学 50
5 b 数学 68
6 c 数学 88
7 a 英语 70
8 b 英语 85
9 c 英语 76怎么样查询第一个表的数据变成第二和第三个表的样式?
第三个表牵涉到临时表,能否讲详细点
1 a 语文 80
2 a 数学 50
3 a 英语 70
4 b 语文 90
5 b 数学 68
6 b 英语 85
7 c 语文 79
8 c 数学 88
9 c 英语 76Name 语文 数学 英语
a 80 50 70
b 90 68 85
c 79 88 76---------------------------------------
ID Name Type Score
1 a 语文 80
2 b 语文 90
3 c 语文 79
4 a 数学 50
5 b 数学 68
6 c 数学 88
7 a 英语 70
8 b 英语 85
9 c 英语 76怎么样查询第一个表的数据变成第二和第三个表的样式?
第三个表牵涉到临时表,能否讲详细点
解决方案 »
- comboboxColumn 屏蔽click 时下拉
- c#中窗体删除了为什么还能显示
- vs2005,开发windows程序,所有的windows组件都不能放到窗体上,有谁遇到过此类问题
- (最高难度)C#调用c++ DLL(返回结构数组指针) ,顶者有分!
- 请问在DataGrid中添加一列显未图标,并且带超链接,参数在DataGrid中取?
- 实时工控绘图用什么方式比较好?
- 关于WWF的一个问题,怀疑是一个严重的BUG
- 高手帮忙c#转JAVASCRIPT
- 如何使DATAGRID中选定的行滚动到可见范围中
- 关于ListBox的分页显示问题??
- vs 2008 打开项目出现的问题
- 連接access數據庫插入一條語句時出現"準則運算式的資料類型不符合。"
set nocount on
create table test(ID varchar(20),Name varchar(20),Type varchar(20),Score int)
insert into test select '1','a','语文','80'
insert into test select '2','a','数学','50'
insert into test select '3','a','英语','70'
insert into test select '4','b','语文','90'
insert into test select '5','b','数学','68'
insert into test select '6','b','英语','85'
insert into test select '7','c','语文','79'
insert into test select '8','c','数学','88'
insert into test select '9','c','英语','76'
go
--测试
declare @sql varchar(8000)
set @sql='select name'
select @sql=@sql+',max(case when type='''+type+''' then score else 0 end) as '+type from
(select distinct type from test)a
set @sql=@sql+' from test group by name'
exec(@sql)--删除测试环境
drop table test
set nocount off
select [name],
sum(case when type='语文' then score else '' end) as 语文,
sum(case when type='数学' then score else '' end) as 数学,
sum(case when type='英语' then score else '' end) as 英语
from table1
group by [name]
--------------------
第二个表我没有看明白和第一个表有什么区别
1:
select name ,(select Type from tb where Type='语文') as '语文',(select Type from tb where Type='数学') as '数学',(select Type from tb where Type='英语') as '英语' from tb 2:
select * from tb where Type='语文'
union
select * from tb where Type='数学'
union
select * from tb where Type='英语'
select name ,(select score from tb where Type='语文') as '语文',(select score from tb where Type='数学') as '数学',(select score from tb where Type='英语') as '英语' from tb
insert into @t select 'a','语文',80
union all select 'a','数学',50
union all select 'a','英文',70
union all select 'b','语文',90
union all select 'b','数学',68
union all select 'b','英文',85
union all select 'c','语文',79
union all select 'c','数学',88
union all select 'c','英文',76
select * from @tselect * from @t
PIVOT
(MAX(score)
for [type] in ([语文],[数学],[英文])
)as pt
insert into @t select 'a','语文',80
union all select 'a','数学',50
union all select 'a','英文',70
union all select 'b','语文',90
union all select 'b','数学',68
union all select 'b','英文',85
union all select 'c','语文',79
union all select 'c','数学',88
union all select 'c','英文',76
select * from @tselect * from @t
PIVOT
(MAX(score)
for [type] in ([语文],[数学],[英文])
)as pt
MAX(CASE type WHEN '语文'THEN SCORE END) AS '语文',
MAX(CASE type WHEN '数学' THEN SCORE END) AS '数学',
MAX(CASE type WHEN '英语' THEN SCORE END) AS '英语'
FROM 表
GROUP BY type试试看吧,印象中好象是这样的。ORACLE的话好象是用DECODE:
DECODE(TYPE, '语文', SCORE) AS 语文
不能这样链接,报错
消息 512,级别 16,状态 1,第 1 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
第一个表的数据是
a 语文
a 数学
a 英语
第三个表 是
a 语文
b 语文
c 语文
select a.name ,(select score from tb b where Type='语文' and a.name = b.name) as '语文',
(select score from tb b where Type='数学' and a.name = b.name) as '数学',
(select score from tb b where Type='英语' and a.name = b.name) as '英语'
from tb a group by a.name2.
select * from tb where Type='语文'
union all
select * from tb where Type='数学'
union all
select * from tb where Type='英语'
1.
SELECT Name, SUM(CASE Type WHEN '语文' THEN Score ELSE 0 END) 语文,
SUM(CASE Type WHEN '数学' THEN Score ELSE 0 END) 数学,
SUM(CASE Type WHEN '英语' THEN Score ELSE 0 END) 英语
FROM biao
GROUP BY Name
2.
SELECT *
FROM biao
ORDER BY Type, Name
谢谢各位大虾啊!
1.
select Ename,
sum(case when Etype='语文' then score else '' end) as 语文,
sum(case when Etype='数学' then score else '' end) as 数学,
sum(case when Etype='英语' then score else '' end) as 英语
from Exam
group by Ename
set nocount on
create table test(ID varchar(20),Name varchar(20),Type varchar(20),Score int)
insert into test select '1','a','语文','80'
insert into test select '2','a','数学','50'
insert into test select '3','a','英语','70'
insert into test select '4','b','语文','90'
insert into test select '5','b','数学','68'
insert into test select '6','b','英语','85'
insert into test select '7','c','语文','79'
insert into test select '8','c','数学','88'
insert into test select '9','c','英语','76'
go
--测试
declare @sql varchar(8000)
set @sql='select name'
select @sql=@sql+',max(case when type='''+type+''' then score else 0 end) as '+type from
(select distinct type from test)a
set @sql=@sql+' from test group by name'
exec(@sql)
drop table test
set nocount off此方法更加灵活
declare @t table(Ename varchar(50),Etype varchar(50),score int)
insert into @t select 'a','语文',80
union all select 'a','数学',50
union all select 'a','英文',70
union all select 'b','语文',90
union all select 'b','数学',68
union all select 'b','英文',85
union all select 'c','语文',79
union all select 'c','数学',88
union all select 'c','英文',76
select * from @tselect * from @t
PIVOT
(MAX(score)
for [type] in ([语文],[数学],[英文])
)as pt
2.
select * from Exam where EType='语文'
union all
select * from Exam where EType='数学'
union all
select * from Exam where EType='英语'
SELECT *
FROM Exam
ORDER BY EType, Ename asc