急,急,这条SQL语句怎么写?(300)

雨

雨季飘零

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #1

有如下数据库:字段: CellName Amount EnterDate数据: Cell1987 5.67 2009-03-11 Cell1987 6.90 2009-03-11 Cell1987 9.35 2009-03-12 Cell8766 7.90 2009-03-12 .......要得到如下结果CellName 12号的Sum(Amount) 11号的Sum(Amount) 12号的Sum(Amount)-11号的Sum(Amount)这条语句该如何写啊?恳请大家指教

 

雨

雨季飘零

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #2

没人回答,郁闷

 

C

Corn3

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #3

select CellName, sum(case datepart(day, EnterDate) when 12 then Amount else 0 end) as Sum12, sum(case datepart(day, EnterDate) when 11 then Amount else 0 end) as Sum11, (case datepart(day, EnterDate) when 12 then Amount else 0 end) - sum(case datepart(day, EnterDate) when 11 then Amount else 0 end) as DeltaSum from Table1 group by CellName

 

雨

雨季飘零

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #4

哥们，你这搞的也太复杂了吧, 结果例子:Cell1987 11.77(2009-03-11的合计) 9.35(2009-03-12的合计) 2.42(11.77-9.35)就要这种效果

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #5

select cellname ,sum( case when enterdate is '2009-03-11' then Amount else 0 end ) as 11号的Sum,sum( case when enterdate is '2009-03-12' then Amount else 0 end ) as 12号的Sum,( case when enterdate is '2009-03-12' then Amount else 0 end )- sum( case when enterdate is '2009-03-11' then Amount else 0 end ) as 12号减去11号

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #6

刚才的错了~~

 

雨

雨季飘零

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #7

不对啊, 我的表名是BSC, FROM BSC是写在最后吗,执行时提示服务器: 消息 170，级别 15，状态 1，行 1第 1 行: '2009-03-11' 附近有语法错误。

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #8

select cellname ,sum( case when enterdate = '2009-03-11' then Amount else 0 end ) as Sum11,sum( case when enterdate = '2009-03-12' then Amount else 0 end ) as Sum12FROM BSCgroup by cellname

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #9

最终版本select cellname ,sum( case when enterdate = '2009-03-11' then Amount else 0 end ) as Sum11,sum( case when enterdate = '2009-03-12' then Amount else 0 end ) as Sum12,sum( case when enterdate = '2009-03-11' then Amount else 0 end ) -sum( case when enterdate = '2009-03-12' then Amount else 0 end ) as Sum1112FROM BSCgroup by cellname

 

雨

雨季飘零

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-18

• #10

de410 正解,多谢哥们，结帖,散分