select a.storeid,a.storename,sum(b.salestotal),SUM(b.salescount) from store a left join sales b on a.storeid=b.storeid and b.billdate='2014-01-05' group by a.storeid,a.storename
create table store ( storeid varchar(50), storename varchar(50) ) insert into store select '100001','a1' insert into store select '100002','a2' insert into store select '100003','a3' insert into store select '100004','a4'create table sales ( storeid varchar(50), salestotal int, salescount int, billdate datetime ) insert into sales select '100001',1000,100,'2014-01-01' insert into sales select '100002',1001,101,'2014-01-02' insert into sales select '100003',1002,102,'2014-01-03' insert into sales select '100004',1003,103,'2014-01-04'select a.storeid,a.storename,sum(b.salestotal),SUM(b.salescount) from store a left join sales b on a.storeid=b.storeid and b.billdate='2014-01-05' group by a.storeid,a.storenamedrop table store drop table sales/*---------结果---------- storeid storename 无列名 无列名 ------------------------- 100001 a1 NULL NULL 100002 a2 NULL NULL 100003 a3 NULL NULL 100004 a4 NULL NULL ------------------------*/
create table store
(
storeid varchar(50),
storename varchar(50)
)
insert into store select '100001','a1'
insert into store select '100002','a2'
insert into store select '100003','a3'
insert into store select '100004','a4'create table sales
(
storeid varchar(50),
salestotal int,
salescount int,
billdate datetime
)
insert into sales select '100001',1000,100,'2014-01-01'
insert into sales select '100002',1001,101,'2014-01-02'
insert into sales select '100003',1002,102,'2014-01-03'
insert into sales select '100004',1003,103,'2014-01-04'select a.storeid,a.storename,sum(b.salestotal),SUM(b.salescount)
from store a left join sales b on a.storeid=b.storeid and b.billdate='2014-01-05'
group by a.storeid,a.storenamedrop table store
drop table sales/*---------结果----------
storeid storename 无列名 无列名
-------------------------
100001 a1 NULL NULL
100002 a2 NULL NULL
100003 a3 NULL NULL
100004 a4 NULL NULL
------------------------*/