如何使用SQL Server计算借方和贷方 [英] How to calculate Debit and credit using sql server

问题描述

我 大家早上好.

我对计算借方和贷方存有疑问.
我有两个桌子
表1(凭证):VoucherNo，VoucherName，VoucherDate，借方.
表2(收据):收据编号，收据名称，信用额.

我需要像下面这样放掉

I Hi, Good Morning everyone.

I have a doubt for calculating debit and credit.
I have two table
Table 1 (Voucher): VoucherNo,VoucherName,VoucherDate, Debit.
Table 2 (Receipt):ReceiptNo,ReceiptName,Credit.

I need out put like below

VoucherNo  VoucherName Debit  Credit
001         Purcharse  1000      0
02           sales       0       2500

等等

这是我的查询

and so on

This is my query

SELECT VoucherNo,VoucherName,Debit, NULL as Credit
FROM tbl_Voucher
UNION ALL
SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
FROM tbl_Receipt

但我没有得到正确的输出

帮助我

but i don''t get proper output

Help me

推荐答案

假设一个表是主"表，则联接会更好.例如，如果所有记录都将存在于tbl_Receipt中，则执行以下操作:

例如:

A join would be better assuming that one table is the "main" table. For example, if all records will exist in tbl_Receipt then do something like this:

For example:

SELECT r.VoucherNo, r.Credit, v.Debit
FROM tbl_Receipt r
LEFT JOIN tbl_Voucher v ON r.ID = v.ID

或者，您可以将现有的SQL包装到SELECT语句中:

Or, you could wrap your existing SQL into a SELECT statement:

SELECT x.VoucerhNo, x.VoucherName, SUM(x.Debit), SUM(x.Credit)
FROM (
  SELECT VoucherNo,VoucherName,Debit, NULL as Credit
  FROM tbl_Voucher
  UNION ALL
  SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
  FROM tbl_Receipt
) x 
GROUP BY x.VoucherNo, x.VoucherName

加入连接可能是更好的方法.

The join will likely be a better approach.

这篇关于如何使用SQL Server计算借方和贷方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！