数据计算

最新推荐文章于 2022-09-07 11:24:52 发布

bachubo0111

最新推荐文章于 2022-09-07 11:24:52 发布

阅读量121

点赞数

原文链接：http://www.cnblogs.com/zhouyang82/p/6729482.html

版权

DISTINCT() 过滤重复
COUNT()    统计个数
    SELECT COUNT(DISTINCT(cateid)) FROM cs_goods ORDER BY cateid
    SELECT COUNT(*) FROM cs_goods
    
SUM()   求和
       求price列总和
       SELECT SUM

DISTINCT() 过滤重复
COUNT()    统计个数
    SELECT COUNT(DISTINCT(cateid)) FROM cs_goods ORDER BY cateid
    SELECT COUNT(*) FROM cs_goods
    
SUM()   求和
       求price列总和
       SELECT SUM(price) FROM cs_goods
       求每个月总销售额
       SELECT SUM(price),SUBSTRING(FROM_UNIXTIME(createtime),1,7) AS ymonth FROM cs_goods GROUP BY ymonth;
       求每天总销售额
       SELECT SUM(price),DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth ORDER BY ymonth DESC;
       求每天销售额大于100的记录
       SELECT SUM(price) AS total,DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth HAVING total>100 ORDER BY ymonth DESC;

AVG()  求平均
       求所有商品平均单价
       SELECT AVG(price) FROM cs_goods;
       求每个分类下商品平均单价
       SELECT AVG(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;
MAX()  求最大值
       求每个分类下最高单价
       SELECT MAX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;
MIN()  求最小值
       求每个分类下最小单价
       SELECT MIX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;

(price) FROM cs_goods 求每个月总销售额 SELECT SUM(price),SUBSTRING(FROM_UNIXTIME(createtime),1,7) AS ymonth FROM cs_goods GROUP BY ymonth; 求每天总销售额 SELECT SUM(price),DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth ORDER BY ymonth DESC; 求每天销售额大于100的记录 SELECT SUM(price) AS total,DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth HAVING total>100 ORDER BY ymonth DESC; AVG() 求平均 求所有商品平均单价 SELECT AVG(price) FROM cs_goods; 求每个分类下商品平均单价 SELECT AVG(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid; MAX() 求最大值 求每个分类下最高单价 SELECT MAX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid; MIX() 求最小值 求每个分类下最小单价 SELECT MIX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;

DISTINCT() 过滤重复
COUNT()    统计个数
    SELECT COUNT(DISTINCT(cateid)) FROM cs_goods ORDER BY cateid
    SELECT COUNT(*) FROM cs_goods
    
SUM()   求和
       求price列总和
       SELECT SUM(price) FROM cs_goods
       求每个月总销售额
       SELECT SUM(price),SUBSTRING(FROM_UNIXTIME(createtime),1,7) AS ymonth FROM cs_goods GROUP BY ymonth;
       求每天总销售额
       SELECT SUM(price),DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth ORDER BY ymonth DESC;
       求每天销售额大于100的记录
       SELECT SUM(price) AS total,DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth HAVING total>100 ORDER BY ymonth DESC;

AVG()  求平均
       求所有商品平均单价
       SELECT AVG(price) FROM cs_goods;
       求每个分类下商品平均单价
       SELECT AVG(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;
MAX()  求最大值
       求每个分类下最高单价
       SELECT MAX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;
MIX()  求最小值
       求每个分类下最小单价
       SELECT MIX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;

转载于:https://www.cnblogs.com/zhouyang82/p/6729482.html