set @d1 = '2010-01-01';
set @d2 = '2010-01-31';
select concat(t.name,' หมู่ ',a0.moopart) as tmb_moo
,(select a1.pdx from vn_stat a1 where a1.vstdate between @d1 and @d2 and a1.moopart = a0.moopart and a1.aid = a0.aid and (a1.pdx<>'' or a1.pdx is not null) group by a1.pdx order by count(*) desc, a1.pdx limit 1) as no1
,(select a2.pdx from vn_stat a2 where a2.vstdate between @d1 and @d2 and a2.moopart = a0.moopart and a2.aid = a0.aid and (a2.pdx<>'' or a2.pdx is not null) group by a2.pdx order by count(*) desc, a2.pdx limit 1,1) as no2
,(select a3.pdx from vn_stat a3 where a3.vstdate between @d1 and @d2 and a3.moopart = a0.moopart and a3.aid = a0.aid and (a3.pdx<>'' or a3.pdx is not null) group by a3.pdx order by count(*) desc, a3.pdx limit 2,1) as no3
,(select a4.pdx from vn_stat a4 where a4.vstdate between @d1 and @d2 and a4.moopart = a0.moopart and a4.aid = a0.aid and (a4.pdx<>'' or a4.pdx is not null) group by a4.pdx order by count(*) desc, a4.pdx limit 3,1) as no4
,(select a5.pdx from vn_stat a5 where a5.vstdate between @d1 and @d2 and a5.moopart = a0.moopart and a5.aid = a0.aid and (a5.pdx<>'' or a5.pdx is not null) group by a5.pdx order by count(*) desc, a5.pdx limit 4,1) as no5
,(select a6.pdx from vn_stat a6 where a6.vstdate between @d1 and @d2 and a6.moopart = a0.moopart and a6.aid = a0.aid and (a6.pdx<>'' or a6.pdx is not null) group by a6.pdx order by count(*) desc, a6.pdx limit 5,1) as no6
,(select a7.pdx from vn_stat a7 where a7.vstdate between @d1 and @d2 and a7.moopart = a0.moopart and a7.aid = a0.aid and (a7.pdx<>'' or a7.pdx is not null) group by a7.pdx order by count(*) desc, a7.pdx limit 6,1) as no7
,(select a8.pdx from vn_stat a8 where a8.vstdate between @d1 and @d2 and a8.moopart = a0.moopart and a8.aid = a0.aid and (a8.pdx<>'' or a8.pdx is not null) group by a8.pdx order by count(*) desc, a8.pdx limit 7,1) as no8
,(select a9.pdx from vn_stat a9 where a9.vstdate between @d1 and @d2 and a9.moopart = a0.moopart and a9.aid = a0.aid and (a9.pdx<>'' or a9.pdx is not null) group by a9.pdx order by count(*) desc, a9.pdx limit 8,1) as no9
,(select a10.pdx from vn_stat a10 where a10.vstdate between @d1 and @d2 and a10.moopart = a0.moopart and a10.aid = a0.aid and (a10.pdx<>'' or a10.pdx is not null) group by a10.pdx order by count(*) desc, a10.pdx limit 9,1) as no10
from vn_stat a0
left join thaiaddress t on a0.aid = t.addressid
where a0.aid = '430801' and a0.moopart in ('1','2','3','4','5','6')
and a0.vstdate between @d1 and @d2
group by a0.moopart;
แบบนี้ พอไหวไมครับ
แสดงกระทู้
