MySQL分组统计

蔚落 2021-11-16 23:56 469阅读 0赞

分组统计

练习SQL脚本

/*
     Navicat Premium Data Transfer
    
     File Encoding         : utf-8
    
     Date: 07/29/2019 15:12:46 PM
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    --  Table structure for `goods`
    -- ----------------------------
    DROP TABLE IF EXISTS `goods`;
    CREATE TABLE `goods` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `goods_name` varchar(20) DEFAULT NULL,
      `cat_id` int(5) DEFAULT NULL,
      `goods_num` int(10) DEFAULT NULL,
      `goods_price` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    --  Records of `goods`
    -- ----------------------------
    BEGIN;
    INSERT INTO `goods` VALUES ('1', '钢笔', '1', '3', '4'), ('2', '篮球', '2', '12', '120'), ('3', 'T恤', '3', '5', '58'), ('4', 'Mac', '4', '2', '18989'), ('5', 'ThinkPad', '4', '4', '7800'), ('6', '小米note8', '5', '2', '1999'), ('7', '魅族16', '5', '1', '2400'), ('8', '华为p5', '5', '2', '5000'), ('9', '大米', '6', '2', '3'), ('10', '小米', '6', '1', '2');
    COMMIT;
    
    SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `goods` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `goods_name` varchar(20) DEFAULT NULL,
      `cat_id` int(5) DEFAULT NULL,
      `goods_num` int(10) DEFAULT NULL,
      `goods_price` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

下面是相关练习问题以及sql：

#计算商品的平均价格
    select AVG(g.goods_price) from goods g ;
    #计算商品的总数
    select COUNT(1) from goods g;
    #计算商品的总价
    select SUM(g.goods_price) from goods g;
    #计算商品的价格最大值
    select MAX(g.goods_price)  from goods g;
    #计算库存商品总价值
    select SUM(g.goods_num*g.goods_price)  from goods g;
    #计算每个类别的商品的平均价格
    select g.cat_id,AVG(g.goods_price)from goods g GROUP BY g.cat_id;
    #计算每个类别的数量
    select g.cat_id,COUNT(1) from goods g GROUP BY g.cat_id;
    #查询每个类别下价格最大的商品信息，并根据类别排序。
    select * from goods g WHERE g.goods_price in(select MAX(goods_price) from goods  GROUP BY cat_id ) ORDER BY g.cat_id DESC
    #查询类别号大于3的价格最大的商品信息，并根据类别排序。
    select * from goods g WHERE g.goods_price in(select MAX(goods_price having > 3) from goods  GROUP BY cat_id ) ORDER BY g.cat_id DESC