Version 1 (modified by 4 months ago) ( diff ) | ,
---|
Напредни извештаи од базата (SQL и складирани процедури)
1. Приказ на доставувачи кои имаат доставено производи со информација за колку различни производи имаат доставено, количината на тие производи во сите магацини и во колку различни магацини ги има тие производи и вкупната количина што ја имаат нарачано клиентите.
SELECT s.supplierId, s.supplier_name, q1.products_count, q2.total_quantity_in_warehouses, COALESCE(COUNT(DISTINCT pw.warehouseId), 0) AS distinct_warehouses_count, COALESCE(SUM(oi.quantity), 0) AS total_quantity_in_order_items FROM suppliers s LEFT JOIN ( SELECT s.supplierId, COUNT(DISTINCT p.productId) AS products_count FROM suppliers s LEFT JOIN products p ON s.supplierId = p.supplierId GROUP BY s.supplierId ) q1 ON s.supplierId = q1.supplierId LEFT JOIN ( SELECT s.supplierId, COALESCE(SUM(pw.quantity), 0) AS total_quantity_in_warehouses FROM suppliers s LEFT JOIN products p ON s.supplierId = p.supplierId LEFT JOIN products_in_warehouse pw ON p.productId = pw.productId GROUP BY s.supplierId ) q2 ON s.supplierId = q2.supplierId LEFT JOIN products p ON s.supplierId = p.supplierId LEFT JOIN products_in_warehouse pw ON p.productId = pw.productId LEFT JOIN order_item oi ON p.productId = oi.productId GROUP BY s.supplierId, s.supplier_name, q1.products_count, q2.total_quantity_in_warehouses HAVING q1.products_count > 0;
2. Извештај за првите и последните 5 производи рангирани според вкупната нарачана количина
with sales as (select *, rank () over (order by total_quantity desc) sales_rank from (select p.product_name, sum(quantity) as total_quantity from order_item oi join products p on p.productid = oi.productid group by oi.productid, p.product_name order by total_quantity) ) select * from sales where sales_rank <= 5 union select * from sales where sales_rank >= ((select max(sales_rank) from sales)-5) order by total_quantity desc;
Note:
See TracWiki
for help on using the wiki.