代码之家 › 专栏 › 技术社区 › Malfist

如何使此查询返回0而不是空?

tsql sql-server

Malfist · 技术社区 · 15 年前

我有这个问题:

SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID
    FROM tblTransaction
    GROUP BY tblTransaction.TenantID

但它有一个问题;还有其他的特耐蒂没有交易,我也想要这些。

例如,transaction表有3行用于bob,2行用于john,没有一行用于jane。我要它把鲍勃和约翰的钱还给简,再把0还给简。(如果没有其他方法,可能为空)

我该怎么做?

表格如下:

Tenants  
  ID  
  Other Data  
Transactions  
  ID  
  TenantID (fk to Tenants)
  Other Data

5 回复 | 直到 15 年前

Malfist 15 年前

(您没有声明您的SQL引擎,所以我将链接到MySQL文档)。

这几乎就是 COALESCE() 功能是用来的。您可以向它提供一个列表,它将返回列表中的第一个非空值。您将在查询中使用此选项,如下所示:

SELECT COALESCE((SUM(tr.AmountPaid) - SUM(tr.AmountCharged)), 0) AS TenantBalance, te.ID
FROM tblTenant AS te
    LEFT JOIN tblTransaction AS tr ON (tr.TenantID = te.ID)
GROUP BY te.ID;

这样,如果 SUM() 结果将为空,替换为零。

编辑 :我使用左联接和 集合() 我认为这是你最初失踪的关键。如果只从Transactions表中选择,则无法获取有关事物的信息不在桌子上。但是,通过使用租户表中的左联接,您应该为每个现有租户获得一行。

John Sansom 15 年前

下面是对问题的完整介绍。还包括函数isNull,以确保为没有事务的租户返回零余额(而不是空余额)。

create table tblTenant
(
    ID int identity(1,1) primary key not null,
    Name varchar(100)
);

create table tblTransaction
(
    ID  int identity(1,1) primary key not null,
    tblTenantID int,
    AmountPaid  money,
    AmountCharged money
);

insert into tblTenant(Name)
select 'bob' union all select 'Jane' union all select 'john';

insert into tblTransaction(tblTenantID,AmountPaid, AmountCharged)
select 1,5.00,10.00
union all
select 1,10.00,10.00
union all
select 1,10.00,10.00
union all
select 2,10.00,15.00
union all 
select 2,15.00,15.00


select * from tblTenant
select * from tblTransaction

SELECT 
    tenant.ID, 
    tenant.Name,
    isnull(SUM(Trans.AmountPaid) - SUM(Trans.AmountCharged),0) AS Balance 
FROM tblTenant tenant
    LEFT JOIN tblTransaction Trans ON 
        tenant.ID = Trans.tblTenantID
GROUP BY tenant.ID, tenant.Name;

drop table tblTenant;
drop table tblTransaction;

Malfist 15 年前

Select Tenants.ID, ISNULL((SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)), 0) AS TenantBalance
From Tenants 
Left Outer Join Transactions Tenants.ID = Transactions.TenantID
Group By Tenents.ID

我没有检查它的语法,但它已经足够接近了。

Joseph 15 年前

SELECT (SUM(ISNULL(tblTransaction.AmountPaid, 0)) 
        - SUM(ISNULL(tblTransaction.AmountCharged, 0))) AS TenantBalance
       , tblTransaction.TenantID
        FROM tblTransaction
        GROUP BY tblTransaction.TenantID

我只是添加了这个,因为如果您打算考虑其中一个部分为空,您需要单独执行isNull

-1

billmaya 15 年前

实际上,我找到了一个答案:

SELECT tenant.ID, ISNULL(SUM(trans.AmountPaid) - SUM(trans.AmountCharged),0) AS Balance FROM tblTenant tenant
LEFT JOIN tblTransaction trans
ON tenant.ID = trans.TenantID
GROUP BY tenant.ID