产品表结构
goods_id goods_name goods_price
产品id 产品名称 产品价钱
111 N86手机 398
112 N97手机 790
.......产品点击表结构如下:
id goods_id stats_date day_click
ID 产品id 日期 日点击量
1 111 2010-08-01 3278
2 112 2010-08-01 3178
3 111 2010-08-02 2278
4 112 2010-08-02 3323
.......
求mysql语句,可以统计指定日期范围(2010-08-01至2010-08-31)内所有产品的总点击排行。按点击隆序排列,每页返回15条记录,要求返回产品ID,产品名称,产品价钱,总点击,每日点击量
如下:
good_id goods_name goods_price count_click 8月1日 8月2日......8月31日
产品id 产品名称 产品价钱 总点击 日点击 日点击......日点击
111....
222....
goods_id goods_name goods_price
产品id 产品名称 产品价钱
111 N86手机 398
112 N97手机 790
.......产品点击表结构如下:
id goods_id stats_date day_click
ID 产品id 日期 日点击量
1 111 2010-08-01 3278
2 112 2010-08-01 3178
3 111 2010-08-02 2278
4 112 2010-08-02 3323
.......
求mysql语句,可以统计指定日期范围(2010-08-01至2010-08-31)内所有产品的总点击排行。按点击隆序排列,每页返回15条记录,要求返回产品ID,产品名称,产品价钱,总点击,每日点击量
如下:
good_id goods_name goods_price count_click 8月1日 8月2日......8月31日
产品id 产品名称 产品价钱 总点击 日点击 日点击......日点击
111....
222....
这个我mysql不会。。
看看大大写~
sum(day_click) as count_click,
sum(if(stats_date='2010-08-01',day_click,0) as `8月1日`,
sum(if(stats_date='2010-08-02',day_click,0) as `8月2日`,
sum(if(stats_date='2010-08-03',day_click,0) as `8月3日`,
...,
sum(if(stats_date='2010-08-31',day_click,0) as `8月31日`,
from 产品表 a inner join 产品点击表 b on a.goods_id=b.goods_id
where stats_date between '2010-08-01' and '2010-08-31'
group by a.goods_id
学习下mysql 的动态..
(
goods_id int,
goods_name varchar(10),
goods_price int
);
create table product_click
(
id int not null auto_increment primary key,
goods_id int,
stats_date date,
day_click int
);
insert product select
111,'N86手机',398 union all select
112,'N97手机', 790;
insert product_click select
1 ,111, '2010-08-01', 3278 union all select
2 ,112, '2010-08-01', 3178 union all select
3 ,111, '2010-08-02', 2278 union all select
4 ,112, '2010-08-02', 3323 ;set @str='';select @str:=concat(@str,',','sum(case when stats_date=\'',stats_date,'\' then day_click end ) as `',stats_date,'`')
from (select distinct stats_date from product_click where stats_date between '2010-08-01' and '2010-08-31')k ;
set @str=concat('select a.goods_id,goods_name,goods_price,sum(day_click) as count_click',@str,' from product a join product_click b on a.goods_id=b.goods_id group by a.goods_id,goods_name,goods_price');PREPARE stm from @str ;execute stm;
+----------+------------+-------------+-------------+------------+------------+
| goods_id | goods_name | goods_price | count_click | 2010-08-01 | 2010-08-02 |
+----------+------------+-------------+-------------+------------+------------+
| 111 | N86手机 | 398 | 5556 | 3278 | 2278 |
| 112 | N97手机 | 790 | 6501 | 3178 | 3323 |
+----------+------------+-------------+-------------+------------+------------+
2 rows in set (0.00 sec)
改为:as 'aug_1',也不行
sum(day_click) as count_click,
sum(if(stats_date='2010-08-01',day_click,0)) as `8月1日`,
sum(if(stats_date='2010-08-02',day_click,0)) as `8月2日`,
sum(if(stats_date='2010-08-03',day_click,0)) as `8月3日`,
...,
sum(if(stats_date='2010-08-31',day_click,0) as `8月31日`,
from 产品表 a inner join 产品点击表 b on a.goods_id=b.goods_id
where stats_date between '2010-08-01' and '2010-08-31'
group by a.goods_id少了个括号)