当前月和上月数据单行显示

Question

下表的数据类型如下:

ItemName varchar, Date日期，Amt varchar，状态varchar

ItemName        Date        Amt     
    ABC       1/02/2012     500     
    ABC       8/03/2012     250     

预期的结果

ItemName    LastMonthAmt    CurrentMonthAmt
ABC          500             250

请帮我写这个问题的查询。

提前感谢。

我做了如下尝试:

declare @T table
(
  id varchar(2),
  [Date] datetime,
  [Amt] varchar(5),
  [Status] varchar(5)
)
insert into @T
select '01',   '1/02/12',  '125',   'LM' union all
--select '01',   '1/02/12',  '200',   'Exit' union all
select '01',   '2/02/12',  '250',   'CM' union all
--select '01',   '2/02/12',  '150',  'CM' union all
select '02',   '1/02/12',  '300',   'LM' union all
--select '02',   '1/02/12',  '350',   'CM' union all
--select '02',   '2/02/12',  '220',   'LM' union all
select '02',   '2/02/12',  '140',  'CM'
select id,
       --convert(varchar(8), [Date], 1) as [Date],
       [Amt] as [CM],
      [Amt] as [LM]
from
  (
    select distinct id,
           [Amt],
           row_number() over(partition by id order by [Date] asc) as rn1,
           row_number() over(partition by id order by [Date] desc) as rn2
    from @T
  ) as T
where T.rn1 = 1 or
      T.rn2 = 1
group by id, [Amt]

Answer 1

您可以使用与CASE表达式类似的聚合函数:

select itemname,
  sum(case when month(date) = month(getdate()) -1 then amt else 0 end) LastMonthAmt,
  sum(case when month(date) = month(getdate()) then amt else 0 end) CurrentMonthAmt
from yourtable
group by itemname;

参见SQL Fiddle with Demo