SQL 问与答6

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

在每个类别内按价格对产品进行排序

SELECT 'OrangeDBM' as AUTHOR, product_name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

查找拥有超过 3 种产品的供应商

SELECT 'OrangeDBM' as AUTHOR, supplier_id, COUNT(*)
FROM products
GROUP BY supplier_id
HAVING COUNT(*) > 3;

查找价格最高的 3 件产品

SELECT DISTINCT 'OrangeDBM' as AUTHOR, price
FROM products
ORDER BY price DESC
LIMIT 3;