mysql分段解析_MYSQL分段统计

产品表

CREATE TABLE &＃96;product&＃96; (

&＃96;product_id&＃96; int(11) NOT NULL AUTO_INCREMENT,

&＃96;product_model&＃96; varchar(255) NOT NULL,

&＃96;product_price&＃96; decimal(15,4) NOT NULL,

&＃96;product_status&＃96; tinyint(1) unsigned NOT NULL,

&＃96;product_add_time&＃96; int(11) unsigned DEFAULT ‘0‘,

PRIMARY KEY (&＃96;product_id&＃96;),

KEY &＃96;product_price&＃96; (&＃96;product_price&＃96;) USING BTREE

) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;24 DEFAULT CHARSET&＃61;utf-8

分类表

CREATE TABLE &＃96;category&＃96; (

&＃96;category_id&＃96; int(11) unsigned NOT NULL AUTO_INCREMENT,

&＃96;category_name&＃96; varchar(255) NOT NULL,

PRIMARY KEY (&＃96;category_id&＃96;)

) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;4 DEFAULT CHARSET&＃61;utf-8

产品分类表

CREATE TABLE &＃96;product_to_category&＃96; (

&＃96;product_id&＃96; int(11) unsigned NOT NULL,

&＃96;category_id&＃96; int(11) unsigned NOT NULL,

PRIMARY KEY (&＃96;product_id&＃96;,&＃96;category_id&＃96;)

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf-8

要获取产品价格区间统计上架的Shoes类别的产品&＃xff0c;状态为在售\停售的数目

SELECT

sum(CASE when product.product_price<10 then 1 else 0 end)   AS ‘(0,10)‘,

sum(CASE when  product.product_price>&＃61;10 and  product.product_price<20 then 1 else 0 end)   AS ‘[10,20)‘,

sum(CASE when  product.product_price>&＃61;20 and  product.product_price<30 then 1 else 0 end)   AS ‘[20,30)‘,

sum(CASE when  product.product_price>&＃61;30 and  product.product_price<40 then 1 else 0 end)   AS ‘[30,40)‘,

sum(CASE when  product.product_price>&＃61;40  then 1 else 0 end)   AS ‘[40,&＃43;)‘

FROM test.product AS product

LEFT JOIN test.product_to_category map ON product.product_id &＃61; map.product_id

LEFT JOIN test.category category ON map.category_id &＃61; category.category_id

WHERE category.category_name &＃61; ‘Shoes‘ group by product.product_status;

MYSQL分段统计

标签&＃xff1a;category   prim   unsigned   技术   from   cas   rod   creat   int

本条技术文章来源于互联网&＃xff0c;如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源&＃xff1a;http://www.cnblogs.com/huixuexidezhu/p/7095713.html