sql - 查找同一个表中两个字段之间的差异

Question

我有一个存储每月账单信息的表。

CREATE TABLE [dbo].[billing_history](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[reading_date] [date] NOT NULL,
[reading] [numeric](18, 0) NOT NULL,
[consumer_id] [int] NOT NULL)

consumer_id 是引用消费者详细信息表的外键。

我想要的是从上个月的读数中减去每个客户当前的读数。这将生成当前账单。任何想法。

Answer 1

您可以使用与此类似的东西，您可以在其中替换要返回的月/年的值:

select b1.consumer_id,
  sum(b1.reading - isnull(b2.reading, 0)) Total
from billing_history b1
left join billing_history b2
  on b1.consumer_id = b2.consumer_id
  and month(b2.reading_date) =12
  and year(b2.reading_date) = 2012
where month(b1.reading_date) = 1
  and year(b1.reading_date) = 2013
group by b1.consumer_id;

参见 SQL Fiddle with Demo .

如果你不想传递 month 和 year 的值来搜索，你只想要当前/上个月，那么你可以使用一些东西使用 CTE 与此类似:

;with cur as
(
  select consumer_id,
    reading,
    month(getdate()) curMonth,
    year(getdate()) curYear,
    case when month(getdate()) = 1 then 12 else month(getdate()) -1 end preMonth,
    case when month(getdate()) = 1 then year(getdate())-1 else year(getdate()) end preYear
  from billing_history
  where month(reading_date) = month(getdate())
    and year(reading_date) = year(getdate())
)
select c.consumer_id, 
  sum(c.reading - isnull(pre.reading, 0)) TotalReading
from cur c
left join billing_history pre
  on c.consumer_id = pre.consumer_id
  and month(pre.reading_date) = c.preMonth
  and year(pre.reading_date) = c.preYear
group by c.consumer_id

参见 SQL Fiddle with Demo

此版本获取要使用的当前/前一个月和前一年的值。如果你不熟悉 CTE 语法，这也可以写成:

select c.consumer_id, 
  sum(c.reading - isnull(pre.reading, 0)) TotalReading
from
(
  select consumer_id,
    reading,
    month(getdate()) curMonth,
    year(getdate()) curYear,
    case when month(getdate()) = 1 then 12 else month(getdate()) -1 end preMonth,
    case when month(getdate()) = 1 then year(getdate())-1 else year(getdate()) end preYear
  from billing_history
  where month(reading_date) = month(getdate())
    and year(reading_date) = year(getdate())
) c
left join billing_history pre
  on c.consumer_id = pre.consumer_id
  and month(pre.reading_date) = c.preMonth
  and year(pre.reading_date) = c.preYear
group by c.consumer_id;

参见 SQL Fiddle with Demo .

正如您在我的查询中看到的那样，我在 consumer_id 上使用了聚合函数 SUM() 和 GROUP BY。我这样做是为了防止每位顾客阅读不止一本。如果您知道每个月只有一次阅读，那么您可以删除汇总。