SQL 问与答5

 

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, supplier_id, COUNT(*) AS product_count
FROM products
GROUP BY supplier_id;

查找包含超过 2 个产品的类别

SELECT 'OrangeDBM' as AUTHOR, category, COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

查找整体第二高的价格

SELECT 'OrangeDBM' as AUTHOR, MAX(price)
FROM products
WHERE price < (SELECT MAX(price) FROM products);

查找最昂贵的电子产品

SELECT 'OrangeDBM' as AUTHOR, productname, price
FROM products
WHERE category = 'Electronics'
AND price = (SELECT MAX(price) FROM products WHERE category = 'Electronics');