SELECT md5(concat_ws('_','$day',city_id,product_id,product_type_id,product_level_id)),
$day,a.city_id,b.name,product_id,product_name,product_type_id,d.name,product_level_id,e.name,
sum(i_total_num),sum(i_car_in_running),sum(i_car_in_rental),sum(i_car_in_short_outage),sum(i_car_in_maintain),sum(i_car_in_annual_check),sum(i_car_in_repair),
sum(i_car_in_violation),sum(i_car_in_short_other),sum(i_car_in_long_outage) 
FROM dwm_car_product_state_number as a left join t_rs_city as b on a.city_id=b.id 
left join t_rs_product_type as d on a.product_type_id=d.id 
left join t_rs_product_level as e on a.product_level_id=e.id 
GROUP BY city_id,product_id,product_type_id,product_level_id

1. 统计时细化到原子统计单位，此统计表的主键即为统计单位的主键
2. 按维度聚合时，拼接所有维度计算签名来作为统计聚合表的主键，这样做可以避免数据重复，尤其是在更新数据时，可以不用先删除数据
3. load data infile 'xxxx' ignore/replace into table... 通过指定replace来更新数据


分组后取第一条查询问题

1. --方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
   
2. --方法2：
   
3. select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
   
4. --方法3：
   
5. select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
   
6. --方法4：
   
7. select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
   
8. --方法5
   
9. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

复制代码

# 所有日期字段更新为10天后
UPDATE dm_team_recommend SET vday=DATE_ADD(vday,INTERVAL 10 DAY)  

SELECT md5(concat_ws('_','$day',city_id,product_id,product_type_id,product_level_id)),
$day,a.city_id,b.name,product_id,product_name,product_type_id,d.name,product_level_id,e.name,
sum(i_total_num),sum(i_car_in_running),sum(i_car_in_rental),sum(i_car_in_short_outage),sum(i_car_in_maintain),sum(i_car_in_annual_check),sum(i_car_in_repair),
sum(i_car_in_violation),sum(i_car_in_short_other),sum(i_car_in_long_outage) 
FROM dwm_car_product_state_number as a left join t_rs_city as b on a.city_id=b.id 
left join t_rs_product_type as d on a.product_type_id=d.id 
left join t_rs_product_level as e on a.product_level_id=e.id 
GROUP BY city_id,product_id,product_type_id,product_level_id

1. 统计时细化到原子统计单位，此统计表的主键即为统计单位的主键
2. 按维度聚合时，拼接所有维度计算签名来作为统计聚合表的主键，这样做可以避免数据重复，尤其是在更新数据时，可以不用先删除数据
3. load data infile 'xxxx' ignore/replace into table... 通过指定replace来更新数据


分组后取第一条查询问题

1. --方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
   
2. --方法2：
   
3. select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
   
4. --方法3：
   
5. select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
   
6. --方法4：
   
7. select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
   
8. --方法5
   
9. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

复制代码

# 所有日期字段更新为10天后
UPDATE dm_team_recommend SET vday=DATE_ADD(vday,INTERVAL 10 DAY)