select
ifnull(a.sectionnumber,
'total') as'款号/日期' ,
sum(if(b.makedate like '2010-10-01%',
a.num,
0 )) as '1',
sum(if(b.makedate like '2010-10-02%',
a.num,
0 )) as '2',
sum(if(b.makedate like '2010-10-03%',
a.num,
0 )) as '3',
sum(if(b.makedate like '2010-10-04%',
a.num,
0 )) as '4',
sum(if(b.makedate like '2010-10-05%',
a.num,
0 )) as '5',
sum(if(b.makedate like '2010-10-06%',
a.num,
0 )) as '6',
sum(if(b.makedate like '2010-10-07%',
a.num,
0 )) as '7',
sum(if(b.makedate like '2010-10-08%',
a.num,
0 )) as '8',
sum(if(b.makedate like '2010-10-09%',
a.num,
0 )) as '9',
sum(if(b.makedate like '2010-10-10%',
a.num,
0 )) as '10',
sum(if(b.makedate like '2010-10-11%',
a.num,
0 )) as '11',
sum(if(b.makedate like '2010-10-12%',
a.num,
0 )) as '12',
sum(if(b.makedate like '2010-10-13%',
a.num,
0 )) as '13',
sum(if(b.makedate like '2010-10-14%',
a.num,
0 )) as '14',
sum(if(b.makedate like '2010-10-15%',
a.num,
0 )) as '15',
sum(if(b.makedate like '2010-10-16%',
a.num,
0 )) as '16',
sum(if(b.makedate like '2010-10-17%',
a.num,
0 )) as '17',
sum(if(b.makedate like '2010-10-18%',
a.num,
0 )) as '18',
sum(if(b.makedate like '2010-10-19%',
a.num,
0 )) as '19',
sum(if(b.makedate like '2010-10-20%',
a.num,
0 )) as '20',
sum(if(b.makedate like '2010-10-21%',
a.num,
0 )) as '21',
sum(if(b.makedate like '2010-10-22%',
a.num,
0 )) as '22',
sum(if(b.makedate like '2010-10-23%',
a.num,
0 )) as '23',
sum(if(b.makedate like '2010-10-24%',
a.num,
0 )) as '24',
sum(if(b.makedate like '2010-10-25%',
a.num,
0 )) as '25',
sum(if(b.makedate like '2010-10-26%',
a.num,
0 )) as '26',
sum(if(b.makedate like '2010-10-27%',
a.num,
0 )) as '27',
sum(if(b.makedate like '2010-10-28%',
a.num,
0 )) as '28',
sum(if(b.makedate like '2010-10-29%',
a.num,
0 )) as '29',
sum(if(b.makedate like '2010-10-30%',
a.num,
0 )) as '30',
sum(if(b.makedate like '2010-10-31%',
a.num,
0 )) as '31',
sum(a.num) as total
FROM
inventory_b a
right join
inventory b
on a.pk_inventory=b.pk_inventory
and b.pk_warehouse in(
select
x.pk_warehouse
from
warehouse x
where
x.orgcode in(
select
o.code
from
organization o
where
o.areaid like '1113%'
)
)
and b.makedate like '2010-10%'
group by
a.sectionnumber
order by
a.sectionnumber asc
效率很慢,要怎么优化
ifnull(a.sectionnumber,
'total') as'款号/日期' ,
sum(if(b.makedate like '2010-10-01%',
a.num,
0 )) as '1',
sum(if(b.makedate like '2010-10-02%',
a.num,
0 )) as '2',
sum(if(b.makedate like '2010-10-03%',
a.num,
0 )) as '3',
sum(if(b.makedate like '2010-10-04%',
a.num,
0 )) as '4',
sum(if(b.makedate like '2010-10-05%',
a.num,
0 )) as '5',
sum(if(b.makedate like '2010-10-06%',
a.num,
0 )) as '6',
sum(if(b.makedate like '2010-10-07%',
a.num,
0 )) as '7',
sum(if(b.makedate like '2010-10-08%',
a.num,
0 )) as '8',
sum(if(b.makedate like '2010-10-09%',
a.num,
0 )) as '9',
sum(if(b.makedate like '2010-10-10%',
a.num,
0 )) as '10',
sum(if(b.makedate like '2010-10-11%',
a.num,
0 )) as '11',
sum(if(b.makedate like '2010-10-12%',
a.num,
0 )) as '12',
sum(if(b.makedate like '2010-10-13%',
a.num,
0 )) as '13',
sum(if(b.makedate like '2010-10-14%',
a.num,
0 )) as '14',
sum(if(b.makedate like '2010-10-15%',
a.num,
0 )) as '15',
sum(if(b.makedate like '2010-10-16%',
a.num,
0 )) as '16',
sum(if(b.makedate like '2010-10-17%',
a.num,
0 )) as '17',
sum(if(b.makedate like '2010-10-18%',
a.num,
0 )) as '18',
sum(if(b.makedate like '2010-10-19%',
a.num,
0 )) as '19',
sum(if(b.makedate like '2010-10-20%',
a.num,
0 )) as '20',
sum(if(b.makedate like '2010-10-21%',
a.num,
0 )) as '21',
sum(if(b.makedate like '2010-10-22%',
a.num,
0 )) as '22',
sum(if(b.makedate like '2010-10-23%',
a.num,
0 )) as '23',
sum(if(b.makedate like '2010-10-24%',
a.num,
0 )) as '24',
sum(if(b.makedate like '2010-10-25%',
a.num,
0 )) as '25',
sum(if(b.makedate like '2010-10-26%',
a.num,
0 )) as '26',
sum(if(b.makedate like '2010-10-27%',
a.num,
0 )) as '27',
sum(if(b.makedate like '2010-10-28%',
a.num,
0 )) as '28',
sum(if(b.makedate like '2010-10-29%',
a.num,
0 )) as '29',
sum(if(b.makedate like '2010-10-30%',
a.num,
0 )) as '30',
sum(if(b.makedate like '2010-10-31%',
a.num,
0 )) as '31',
sum(a.num) as total
FROM
inventory_b a
right join
inventory b
on a.pk_inventory=b.pk_inventory
and b.pk_warehouse in(
select
x.pk_warehouse
from
warehouse x
where
x.orgcode in(
select
o.code
from
organization o
where
o.areaid like '1113%'
)
)
and b.makedate like '2010-10%'
group by
a.sectionnumber
order by
a.sectionnumber asc
效率很慢,要怎么优化
可以试试把IN改成JOIN,然后在organization的code 字段上加索引。