数据库 - 第五次实验

SELECT commodity.name,onsell.status,onsell.rem_amount,AVG(orders.buyerrate),COUNT(*)  
FROM onsell JOIN orders ON onsell.cid = orders.cid AND onsell.sid = orders.sid  
            JOIN commodity ON onsell.cid = commodity.cid  
WHERE onsell.cid = 2410 AND onsell.sid = 122

结果：

SELECT stores.name,SUM(onsell.price) 
FROM brand JOIN commodity ON brand.bid = commodity.bid   
           JOIN orders ON commodity.cid = orders.cid
           JOIN stores ON orders.sid = stores.sid
           JOIN onsell ON orders.cid = onsell.cid   
WHERE brand.name = '蒂芙尼' AND orders.sid = onsell.sid
GROUP BY stores.sid  
ORDER BY SUM(onsell.price) DESC LIMIT 5

结果：

SELECT commodity.name,onsell.price,COUNT(*)
FROM onsell JOIN commodity ON onsell.cid = commodity.cid
            JOIN orders ON onsell.sid = orders.sid AND onsell.cid = orders.cid
WHERE onsell.sid = 1
GROUP BY onsell.cid
ORDER BY COUNT(*) DESC LIMIT 10

结果：

SELECT commodity.name,sell_summary.total_sell,sell_summary.avg_buyer_rate
FROM commodity JOIN (
                    SELECT onsell.cid,SUM(onsell.price) AS total_sell,AVG(buyerrate) AS avg_buyer_rate
                    FROM onsell JOIN orders ON onsell.sid = orders.sid AND onsell.cid = orders.cid
				    WHERE orders.status = '已完成'
				    GROUP BY onsell.cid
				    ) AS sell_summary ON commodity.cid = sell_summary.cid
WHERE commodity.bid = 1

结果：

5.(疑问)

SELECT stores.name,stores.level,COUNT(orders.oid) AS total_sale,COUNT(onsell.cid)AS num_products,AVG(orders.buyerrate) AS avg_buyer_rate
FROM stores JOIN onsell ON stores.sid = onsell.sid 
            JOIN orders ON onsell.sid = orders.sid AND onsell.cid = orders.cid  
WHERE stores.sid = 1
GROUP BY stores.name, stores.level

结果：

SELECT commodity.name,brand.name,stores.name,onsell.onsell_date,onsell.rem_amount,onsell.price,AVG(orders.buyerrate)
FROM stores JOIN onsell ON stores.sid = onsell.sid
            JOIN commodity ON commodity.cid = onsell.cid
            JOIN brand ON commodity.bid = brand.bid
			JOIN orders ON onsell.sid = orders.sid AND onsell.cid = orders.cid
WHERE onsell.status = '正常' AND commodity.type = '女装'
GROUP BY onsell.sid,onsell.cid
ORDER BY AVG(orders.buyerrate) DESC LIMIT 10

结果：

先建一个视图，找出user 1购买的商品种类

CREATE VIEW user1_like
AS
SELECT commodity.type AS commodity_type,COUNT(orders.oid) AS sales_count
FROM orders JOIN user ON orders.uid = user.uid
            JOIN commodity ON orders.cid = commodity.cid
			JOIN onsell ON onsell.cid = commodity.cid
WHERE user.uid = 1  
GROUP BY commodity.name

SELECT commodity.name,commodity.type,count(orders.oid)
FROM orders JOIN commodity ON orders.cid = commodity.cid
            JOIN onsell ON onsell.cid = commodity.cid
			JOIN user1_like ON user1_like.commodity_type = commodity.type
GROUP BY commodity.type

结果：