select sid,cid,max(case when flag is null then 0 else flag end) as num, max(case when date is null then '2000-01-01' else date end) as date, max(case when mac is null then 0 else mac end) as mac, max(case when num1 is null then 0 else num1 end) as num1, max(case when num2 is null then 0 else num2 end) as num2 from event group by sid,cid
select a.sid,a.cid,max(num),max(data),max(mac),max(num1),max(num2) from tt a left join tt b on a.sid=b.cid group by a.sid,a.cid
select sid,cid,max(num),max(data),max(mac),max(num1),max(num2) from table1 group by sid,cid
select a.sid,a.cid,max(b.num),max(b.data),max(b.mac),max(b.num1),max(b.num2) from tt a left join tt b on a.sid=b.cid group by a.sid,a.cidorselect sid,cid,max(num),max(data),max(mac),max(num1),max(num2) from tt group by sid,cid
max(case when date is null then '2000-01-01' else date end) as date,
max(case when mac is null then 0 else mac end) as mac,
max(case when num1 is null then 0 else num1 end) as num1,
max(case when num2 is null then 0 else num2 end) as num2
from event
group by sid,cid
from tt a left join tt b
on a.sid=b.cid
group by a.sid,a.cid
from table1
group by sid,cid
from tt a left join tt b
on a.sid=b.cid
group by a.sid,a.cidorselect sid,cid,max(num),max(data),max(mac),max(num1),max(num2)
from tt group by sid,cid