30 másodperces query:
SELECT webshop_id,
w.name,
w.hidden,
(SELECT count(*) FROM prices
WHERE webshop_id=w.webshop_id)
AS prices_count,
(SELECT count(*) FROM prices
WHERE webshop_id=w.webshop_id and deleted=1)
AS deleted_count,
(SELECT count(*) FROM prices
WHERE webshop_id=w.webshop_id and product_id>0
and skipped=0 and deleted=0)
AS paired_count,
(SELECT count(*) FROM prices
WHERE webshop_id=w.webshop_id and skipped=1 and deleted=0)
AS skipped_count,
(SELECT count(*) FROM prices
WHERE webshop_id=w.webshop_id and skipped=0
and product_id=0 and deleted=0)
AS waiting_count
FROM webshops w
WHERE w.book_shop = 0
HAVING prices_count>0
ORDER BY w.name
6 másodperces query:
SELECT w.webshop_id, w.name, w.hidden,
x.prices_count, x.deleted_count, x.paired_count,
x.skipped_count, x.waiting_count
FROM webshops w
JOIN (
select p.webshop_id,
count(*) AS prices_count,
sum(deleted)
AS deleted_count,
sum(if(product_id > 0 and skipped = 0 and deleted = 0, 1, 0))
AS paired_count,
sum(if(skipped = 1 and deleted = 0, 1,0))
AS skipped_count,
sum(if(skipped = 0 and product_id = 0 and deleted = 0, 1, 0))
AS waiting_count
from prices p
group BY webshop_id
HAVING prices_count > 0
ORDER BY webshop_id
) AS x
ON x.webshop_id = w.webshop_id
WHERE w.book_shop = 0
ORDER BY w.name
Note: ez a bejegyzés évekkel azután lett publikus hogy a rendszert lecserélték egy másikra.
)
