select a.id from sport a
join
(
select property from
(
select id,property from sport d where name ='小明' and class='高三(1)'union allselect id,property from sport where name ='大军' and class='高三(7)'
)as bgroup by b.property having count(*)>1 )c
on a.property = c.property and (a.name ='小明' and a.class='高三(1)'
or a.name ='大军' and a.class='高三(7)')
join
(
select property from
(
select id,property from sport d where name ='小明' and class='高三(1)'union allselect id,property from sport where name ='大军' and class='高三(7)'
)as bgroup by b.property having count(*)>1 )c
on a.property = c.property and (a.name ='小明' and a.class='高三(1)'
or a.name ='大军' and a.class='高三(7)')
insert into sport
select '小明', '标枪', '高三(1)'
union all
select '小明','跳高','高三(1)'
union all
select '大军', '标枪', '高三(1)'
union all
select '大军', '铅球', '高三(1)'
union all
select '小明', '标枪', '高三(7)'
union all
select '小明', '跳高' ,'高三(7)'
union all
select '大军', '标枪', '高三(7)'
union all
select '大军', '铅球', '高三(7)'--高三(1)班的 小明 和
--高三(7)班的 大军
select * from sport
select a.id from sport a
join
(
select property from
(
select id,property from sport d where name ='小明' and class='高三(1)'union allselect id,property from sport where name ='大军' and class='高三(7)'
)as bgroup by b.property having count(*)>1 )c
on a.property = c.property and (a.name ='小明' and a.class='高三(1)'
or a.name ='大军' and a.class='高三(7)')
-------------------------
1
7
select 108, 48.9, 2006, 1
union all
select 108, 18.3, 2006, 1
union all
select 108, 140, 2006, 1
union all
select 110, 10, 2006, 1
union all
select 110, 1470, 2006, 1
union all
select 108, 42.5, 2006, 2
union all
select 102, 70, 2006, 2
union all
select 108, 45, 2006, 4select * from jj--第一种方法
select 员工编号,
[1月] = (select sum(case when 月=1 then 奖金 else 0 end) from jj where 员工编号 = a.员工编号),
[2月] = (select sum(case when 月=2 then 奖金 else 0 end) from jj where 员工编号 = a.员工编号),
[3月] = (select sum(case when 月=3 then 奖金 else 0 end) from jj where 员工编号 = a.员工编号),
[4月] = (select sum(case when 月=4 then 奖金 else 0 end) from jj where 员工编号 = a.员工编号)
from jj a
group by 员工编号 --第二种方法declare @sql nvarchar(4000)
set @sql = 'select 员工编号'select @sql = @sql + ',sum (case convert(nvarchar(1),[月]) when '''+ convert(nvarchar(1),月) +''' then 奖金 else 0 end ) as [' + convert(nvarchar(1),月) + '月]'
from jj group by 月select @sql = @sql + 'from jj group by 员工编号'exec(@sql)这样的行列转换的题太多啦,楼主应该先在网上找找,,到处都是.