表A:
A2(产品名称) A3(日期) A4(销售金额)
a 2006-06 200
a 2006-07 300
a 2006-08 400
a 2007-06 300
a 2007-07 400
a 2007-08 500
我想得到的结果是2006-06和2007-06 a1的销售额:
a1 200 300
------解决方案--------------------
Select
A2,
SUM(Case A3 When '2006-06 ' Then A4 Else 0 End) As [2006-06],
SUM(Case A3 When '2007-06 ' Then A4 Else 0 End) As [2007-06]
From A
Group By A2
------解决方案--------------------
select A2,case A3 when '2006-06 ' then A4 end,case A3 when '2007-06 ' then A4 end from 表A where A2= 'a1 '
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
A2 varchar(10),
A3 varchar(10),
A4 int
)
insert into tb(A2,A3,A4) values( 'a ', '2006-06 ',200)
insert into tb(A2,A3,A4) values( 'a ', '2006-07 ',300)
insert into tb(A2,A3,A4) values( 'a ', '2006-08 ',400)
insert into tb(A2,A3,A4) values( 'a ', '2007-06 ',300)
insert into tb(A2,A3,A4) values( 'a ', '2007-07 ',400)
insert into tb(A2,A3,A4) values( 'a ', '2007-08 ',500)
select a2,
max(case when a3 = '2006-06 ' then a4 else 0 end) as '2006-06 ',
max(case when a3 = '2006-07 ' then a4 else 0 end) as '2006-07 '
from tb
group by a2
drop table tb
/*result
a2 2006-06 2006-07
---------- ----------- -----------
a 200 300
（所影响的行数为 1 行）
*/