关于sql统计分组时如何将未统计到的列显示为0
先说一下执行的语句:
select m_date, m_type, count(1) num from mytest group by m_type, m_date
然后执行语句后得到的结果：
2013-09-01宝马1
2013-09-01大众4
2013-10-01宝马5
2013-10-01奔驰4
2013-10-01大众2
但是我想得到的结果是这样的
2013-09-01宝马1
2013-09-01大众4
2013-09-01奔驰0
2013-10-01宝马5
2013-10-01奔驰4
2013-10-01大众2
其中蓝色显示的是分组统计中不会出现的列，
我想问的是如何在仅使用SQL语句的情况下得到这样的结果？
同时又不会对查询效率有很大的影响
上述这样的问题困扰了我N长的时间都无法解决，期间问了不少人，都得不到满意的结果，最后有位高手写出了我想要的那种结果，但是效率实在是太低了！
因为做统计的时候非常需要这样的结果(在程序中实现因为有太多的变数，所以很不好做)
于是便到这里恳请各位高手可以帮忙解决一下，小弟万分感激！！！
最后附上模拟的建表和一些数据：
create TABLE mytest
(
m_id [int] IDENTITY(1,1) primary key,
m_type varchar(50),
m_date varchar(10)
)
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('宝马','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
[解决办法]
你是要这样的效果？
use tempdb
go
if object_id('mytest') is not null drop table mytest
go
create TABLE mytest
(
m_id [int] IDENTITY(1,1) primary key,
m_type varchar(50),
m_date varchar(10)
)
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('奔驰','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('宝马','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-10-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
insert into mytest(m_type, m_date) values ('宝马','2013-09-01');
insert into mytest(m_type, m_date) values ('大众','2013-09-01');
go
select
t2.m_date, t1.m_type, count(t3.m_type) num
from
(select m_type
from mytest
group by m_type) t1
inner join (
select m_date
from mytest
group by m_date
) t2 on 1=1
left join mytest t3 on t1.m_type=t3.m_type and t2.m_date=t3.m_date
group by t1.m_type, t2.m_date
/*
2013-09-01宝马1
2013-09-01奔驰0
2013-09-01大众4
2013-10-01宝马5
2013-10-01奔驰4
2013-10-01大众2
*/
