如何从三个表中创建查询以进行详细的客户端语句

Question

我想从三个不同的建筑物表中获取特定客户端的帐户详细说明，并在一个报告中呈现。我使用存储的过程

 customers table
 id_customer    name 
 -------------------------------
 1              tom
 2              sam
orders table
no   customer_id  amount    date
------------------------------------------
1    2            150       1-1-2018
2    1            45        1-1-2018
3    2            25        3-1-2018

receipt table
no   id_customer   amount   date 
-----------------------------------------
1    1             75       1-1-2018
2    2             100      2-1-2018

我希望结果像这样

  Operation type  reference_no   description   Debit  Credit Balance  date
 ---------------------------------------------------------------------------
  order           1              .....         150     0     150    1-1-2018
  receipt         2              cash          0       100   50     2-1-2018
  order           3              .....         25      0     75     3-1-2018

customer_id = 2

Answer 1

您可以执行以下

1. 创建视图以存储以下(type,id,debit,credit,date,description)

2. 运行查询，该查询将使用sum of previous records更新每一行并添加当前行的debit - credit

视图代码

create view result as
   select type = 'order',
          id,
          id_customer,
          debit = amount,
          credit = 0,
          [date] = order_date,
          [description]='....'
   from orders
   union all
   select type ='receipt',
          id,
          id_customer,
          debit = 0,
          credit = amount,
          [date] = receipt_date,
          [description] ='cash'
    from receipts

查询

declare @id_customer int = 2
;with initial as(
  select *
  from result
  where id_customer= @id_customer
),report as(
  select r.id,[balance]=isnull((select sum(b.debit-b.credit)
               from initial b
               where b.[date]<r.[date]) + r.debit - r.credit ,r.debit-r.credit)
  from initial r
)
 select [Operation type] = type,
        reference_no = r.id,
        [description],
        [Debit] = debit,
        [Credit] = credit,
        [Balance] = b.balance
 from result r
 inner join report b on b.id = r.id
 where r.id_customer = @id_customer
 order by r.[date]

结果

在这里有效的演示

Operation type  reference_no    description Debit   Credit  Balance date
order                   1              ....       150      0    150     2018-01-01
receipt                 2              cash        0       100  50      2018-01-02
order                   3              ....        25      0     75     2018-01-03

希望这对您有帮助

Answer 2

这是一个解决方案：

表的声明

declare @customers table(id_customer int, name varchar(10))
insert into @customers values (1,'tom'),(2,'sam')
declare @orders table([no] int, customer_id int, amount int, [date] date)
insert into @orders values (1,2,150,'1-1-2018'),(2,1,45,'1-1-2018'),(3,2,25,'3-1-2018')
declare @receipt table([no] int, id_customer int, amount int, [date] date)
insert into @receipt values (1,1,75,'1-1-2018'),(2,2,100,'2-1-2018')

实际查询：

select ROW_NUMBER() over (order by (select null)) as ReferenceNo, OperationType, [Description], Debit, Credit,
       SUM(Debit) over (order by [date] rows between unbounded preceding and current row)-SUM(Credit) over (order by [date] rows between unbounded preceding and current row) as Balance,
       [Date]
from (
    select O.OperationType,
           case when O.OperationType = 'receipt' then 'Cash' else '...' end [Description],
           case when O.OperationType = 'receipt' then 0 else O.amount end [Debit],
           case when O.OperationType = 'order' then 0 else O.amount end [Credit],
           O.[date]
    from @customers as C join (
        select *, 'order' as OperationType from @orders
        union all
        select *, 'receipt' from @receipt) as O
    on C.id_customer = O.customer_id
    where O.customer_id = 2
) as a

Answer 3

  Create table #customers (id_customer int,name varchar(10))
Insert into #customers values (1,'tom'),(2,'sam')
Create table #orders(no# int,customer_id int,amount int,dtupdt date)
Insert into #orders values(1,2,150,'1-1-2018'),(2,1,45,'1-1-2018'),(3,2,25,'3-1-2018')
Create table #receipt (no# int,id_customer int,amount int,dtupdt date)
Insert into #receipt values (1,1,75,'1-1-2018'),(2,2,100,'2-1-2018')
Select customer_id,row_number()over(order by dtupdt) as RefNo ,ordertype,
Case when ordertype='Receipt' then 'Cash' else '' end as Description,
credit,debit,
sum((case when credit=0 then debit when debit=0 then -credit else 0 end))over (order by dtupdt) as balance,
dtupdt
 from (
select *, case when ordertype='Orders' then tbl.amount else 0 end as debit, 
case when ordertype='Receipt' then tbl.amount else 0 end as Credit from #customers cst
inner join
(
select 'Orders' as ordertype,* from #orders where customer_id=2
union 
select 'Receipt',* from #receipt
where id_customer=2
)tbl
on cst.id_customer=tbl.customer_id
)tbl1
order by dtupdt