3.如下有表 T1ID Year Total
T1 2006 100
T1 2007 60
T2 2006 50
T2 2007 90请使用oracle 实现如下效果:
ID Year(2006) Year(2007)
T1 100 60
T2 50 90
4.以下的表
20050901 胜
20050901 负
20050901 负
20050901 负
200509010 胜
200509010 胜
200509010 负请用Sql语句实现以下效果
胜 负
20050901 1 3
20050910 2 1请会的兄弟朋友们回答下。。最好解释下。。别这么乱。。我是菜鸟级的
T1 2006 100
T1 2007 60
T2 2006 50
T2 2007 90请使用oracle 实现如下效果:
ID Year(2006) Year(2007)
T1 100 60
T2 50 90
4.以下的表
20050901 胜
20050901 负
20050901 负
20050901 负
200509010 胜
200509010 胜
200509010 负请用Sql语句实现以下效果
胜 负
20050901 1 3
20050910 2 1请会的兄弟朋友们回答下。。最好解释下。。别这么乱。。我是菜鸟级的
SELECT ID, [胜],[负]
FROM table
PIVOT
(
count (result)
FOR result IN
( [胜], [负]
)
) AS pvt
ORDER BY id;
-->oracle不会
-->4
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[结果] varchar(2))
insert [tb]
select 20050901,'胜' union all
select 20050901,'负' union all
select 20050901,'负' union all
select 20050901,'负' union all
select 200509010,'胜' union all
select 200509010,'胜' union all
select 200509010,'负'select [id],sum(case [结果] when '胜' then 1 else 0 end) as 胜,
sum(case [结果] when '负' then 1 else 0 end) as 负
from [tb]
group by [id]
/*
id 胜 负
----------- ----------- -----------
20050901 1 3
200509010 2 1(2 行受影响)
*/
insert into t1 values('T1', '2006', 100)
insert into t1 values('T1', '2006', 60)
insert into t1 values('T2', '2007', 50)
insert into t1 values('T2', '2007', 90)select ID,
sum(case when year='2006' then Total end) as 'year(2006)',
sum(case when year='2007' then Total end) as 'year(2007)'
from t1
group by ID
---------------------------------
---------------------------------create table t2(dt nvarchar(32), nvarchar(32))
insert into t2 values('20050901', '胜')
insert into t2 values('20050901', '负')
insert into t2 values('20050901', '负')
insert into t2 values('20050901', '负')
insert into t2 values('200509010', '胜')
insert into t2 values('200509010', '胜')
insert into t2 values('200509010', '负')select * from t2
select dt,
count(case when ='胜' then 1 end) as '胜',
count(case when ='负' then 1 end) as '负'
from t2
group by dt