如果在链接表中没有匹配的值,显示零值
问题描述:
根据我的下面的查询,如果在所选日期之间没有“inc”表中没有数据,我如何得到值为“0”的最后一行。如果在链接表中没有匹配的值,显示零值
我的查询:
select
Calls = count(*)
, Cust = i.Cust_id
, Contract = c.con_id
, Serv_Time = sum(Serv_Time)
from inc as i
inner join contract as c
on i.item_id = c.item_id
and i.inc_date >= c.[start]
and i.inc_date <= c.[end]
where c.[start]>='20160101'
group by i.Cust_id, c.con_id
order by i.Cust_Id, c.con_id
预期输出:
+-------+---------+------------+-----------+
| Calls | Cust | Contract | Serv_Time |
+-------+---------+------------+-----------+
| 1 | Amir | MS2016 | 300 |
| 2 | John | HP2016 | 180 |
| 1 | Kerlee | OR2016 | 40 |
| 1 | Nick | CIS2016 | 120 |
| 2 | samanta | EMC2016 | 200 |
| 0 | Amir | MS2016-New | 0 |
+-------+---------+------------+-----------+
表结构:
create table inc
(
inc_id int
, cust_id varchar(16)
, item_id varchar(16)
, serv_time int
, inc_date date
);
insert into inc values
(1,'john','HP', 40 ,'17-Apr-2015')
,(2,'John','HP', 60 ,'10-Jan-2016')
,(3,'Nick','Cisco', 120 ,'11-Jan-2016')
,(4,'samanta','EMC', 180 ,'12-Jan-2016')
,(5,'Kerlee','Oracle', 40 ,'13-Jan-2016')
,(6,'Amir','Microsoft', 300 ,'14-Jan-2016')
,(7,'John','HP', 120 ,'15-Jan-2016')
,(8,'samanta','EMC', 20 ,'16-Jan-2016')
,(9,'Kerlee','Oracle', 10 ,'02-Feb-2017');
create table contract (
item_id varchar(16)
, con_id varchar(16)
, [Start] date
, [End] date
);
insert into contract values
('Dell','DE2015','20150101','20151231')
,('HP','HP2015','20150101','20151231')
,('Cisco','CIS2016','20160101','20161231')
,('EMC','EMC2016','20160101','20161231')
,('HP','HP2016','20160101','20161231')
,('Oracle','OR2016','20160101','20161231')
,('Microsoft','MS2016','20160101','20161231')
,('Microsoft','MS2016-New','20160101','20161231')
,('Microsoft','MS2017','20170101','20171231');
答
使用LEFT OUTER JOIN
和功能ISNULL()
设置NULL
为零。它不会是一个问题得到零值,如果你能生成行(左外连接)
选择 ISNULL((COUNT(*)),0)作为呼叫 ,用户值= i.Cust_id ,合同= C .con_id , ISNULL((总和(Serv_Time)),0)AS Serv_Time 从INC,因为我 上i.item_id = c.item_id 和i.inc_date> = C左外连接合同为c 。[开始] and i.inc_date ='20160101' group by i.Cust_id,c.con_id order by i.Cust_Id,c.con_id –