一共有三张表
t_goods 物品表 t_category 类别表 t_color 颜色表
结构如下:
问题1:查询 每个分类下的总数是多少
SELECT t.`name`,SUM(total) sum_total FROM t_goods g,t_category t WHERE g.type=t.id GROUP BY t.`name`;
每个分类下 肯定用到了分组,总数肯定用sum函数
问题2:查询 每个分类下平均数前2名的类别是哪些
SELECT c.`name`,AVG(total) avg_count FROM t_goods g,t_category c WHERE c.id=g.type GROUP BY type ORDER BY avg_count DESC LIMIT 2 ;
问题3:查询出每个分类下各个颜色下的总和
SELECT ss.tcName,
MAX(CASE ss.cName WHEN
'红色' THEN ss.total ELSE 0 END) AS '红色',
MAX(CASE ss.cName WHEN
'紫色' THEN ss.total ELSE 0 END) AS '紫色',
MAX(CASE ss.cName WHEN
'蓝色' THEN ss.total ELSE 0 END) AS '蓝色',
MAX(CASE ss.cName WHEN
'金色' THEN ss.total ELSE 0 END) AS '金色'
FROM (
SELECT t.`name` tcName,c.`name` cName,SUM(g.total) total,g.type,g.color FROM t_goods g,t_category t,t_color c WHERE g.type=t.id AND g.color=c.id GROUP BY g.type,g.color) ss
group by ss.tcName
=======================================================================================================================
结果:1.
结果:2
结果:3
利用sql 查询出的结果1
SELECT ss.tcName,
ss.cName,
ss.total FROM (
SELECT t.`name` tcName,c.`name` cName,SUM(g.total) total,g.type,g.color FROM t_goods g,t_category t,t_color c WHERE g.type=t.id AND g.color=c.id GROUP BY g.type,g.color) ss;
结果
进行行转列
后