Select t1.id,t1.name,
mtype0=count(case t2.mtype when 0 then t2.mtype else 0 end),
mtype1=count(case t2.mtype when 1 then t2.mtype else 0 end),
mtype2=count(case t2.mtype when 2 then t2.mtype else 0 end)
from 表1 as t1 Left join 表2 as t2 on t1.id=t1.id
where t1.mdate between @startdate and @enddate
mtype0=count(case t2.mtype when 0 then t2.mtype else 0 end),
mtype1=count(case t2.mtype when 1 then t2.mtype else 0 end),
mtype2=count(case t2.mtype when 2 then t2.mtype else 0 end)
from 表1 as t1 Left join 表2 as t2 on t1.id=t1.id
where t1.mdate between @startdate and @enddate
Select t1.id,t1.name,
mtype0=count(case t2.mtype when 0 then t2.mtype else 0 end),
mtype1=count(case t2.mtype when 1 then t2.mtype else 0 end),
mtype2=count(case t2.mtype when 2 then t2.mtype else 0 end)
from 表1 as t1 Left join 表2 as t2 on t1.id=t1.id
where t1.mdate between @startdate and @enddate
group by t1.id,t1.name
表2无数据,表1数据也要显示出来---》表1 Left join 表2
mtype=0的个数 mtype0=count(case t2.mtype when 0 then t2.mtype else 0 end)
................
select a.id,
a.name,
sum(case when mtype=0 then 1 else 0 end) as t0,
sum(case when mtype=1 then 1 else 0 end) as t1,
sum(case when mtype=2 then 1 else 0 end) as t2
from table1 a left join
( select id,mtype
from table2
where mdate between @begintime and @endtime
) b
on a.id=b.id
group by a.id,a.name