baran.f.a
چهارشنبه 10 دی 1393, 12:08 عصر
سلام من می خوام گزارش گیری انجام بدم که تعداد دستگاه های ابطالی ، تعمیری و خام را برحسب پروژه در یک سطر نمایش نمایش بده من sunquery هایم را با استفاده از دستور union با هم تلفیق کردم آیا استفاده از این دستور اشتباهه ؟ چون وقتی نتیجه را نمایش می ده همه ی شمارش ها رو انجام نمی ده یعنی فقط برای مثال تعداد دستگاه های ابطالی مربوط به آن پروژه را نمایش می ده .
"select `status`,`psp_id` ,`Raw`,`Cancell`,`repair`
from(
select pos.status as status,pos.psp_id ,COUNT( pos.serial_code) as `Raw`,0 as `Cancell` ,0 AS `repair`
from pos
where pos.status='خام'
group by pos.psp_id
UNION
select change_location.status as status,pos.psp_id ,0 as `Raw` ,COUNT( change_location.serial_code) as `Cancell`,0 AS `repair`
from pos
INNER JOIN change_location
on change_location.serial_code = pos.serial_code
where change_location.status='ابطالی'
and
change_location.update_date > pos.update
group by pos.psp_id
UNION
select change_location.status as status, pos.psp_id,0 as `Raw` ,0 as `Cancell`,COUNT(change_location.serial_code) AS `repair`
from pos
INNER JOIN change_location
on change_location.serial_code = pos.serial_code
where change_location.status='تعمیری'
and
change_location.update_date > pos.update
group by pos.psp_id
) t3
group by psp_id"
"select `status`,`psp_id` ,`Raw`,`Cancell`,`repair`
from(
select pos.status as status,pos.psp_id ,COUNT( pos.serial_code) as `Raw`,0 as `Cancell` ,0 AS `repair`
from pos
where pos.status='خام'
group by pos.psp_id
UNION
select change_location.status as status,pos.psp_id ,0 as `Raw` ,COUNT( change_location.serial_code) as `Cancell`,0 AS `repair`
from pos
INNER JOIN change_location
on change_location.serial_code = pos.serial_code
where change_location.status='ابطالی'
and
change_location.update_date > pos.update
group by pos.psp_id
UNION
select change_location.status as status, pos.psp_id,0 as `Raw` ,0 as `Cancell`,COUNT(change_location.serial_code) AS `repair`
from pos
INNER JOIN change_location
on change_location.serial_code = pos.serial_code
where change_location.status='تعمیری'
and
change_location.update_date > pos.update
group by pos.psp_id
) t3
group by psp_id"