mysql sql 进阶 in work

  

group_concat  

select 'scale' as name, count(1) 
from xx
where is_scale = 0
union all
select 'high' as name, count(1)
from xx
where `is_high_ technology` = 0

union all

(UNIX_TIMESTAMP(oc.update_date)-UNIX_TIMESTAMP(oc.create_date))/3600

最近1年、1月、1周

SELECT * from oms_order_item_comment 
     where comment_time between date_sub(now(),interval 6 month) and now()  -- 半年
SELECT * from oms_order_item_comment 
     where comment_time between date_sub(now(),interval 1 week) and now()  -- 七天
SELECT * from oms_order_item_comment 
     where comment_time between date_sub(now(),interval 1 year) and now()  -- 一年

and (t2.operation_address is null || t1.registered_address != t2.operation_address) #当t2.operation_address is null,则不判断地址是否相同

UNION 和 ORDER BY 一起使用

1.需求分组排序完    再合并

(   select * from (
                select a from a1  order by a2
     ) as table1
) 
union 
(   select * from ( 
                 select b from b1 order by b2 
    ) as table2 
) 
2.整体合并     再排序

select  *  from

（...   union    ....）   as  a    【派生表别忘了加别名，否则 Every derived table must have its own alias】

order  by..

order by 别忘了在 select语句中查询出

union和union all的差别是前者会忽略重复数据

自动生成连续序号

set @rowNo=0;
SELECT (@rowNO := @rowNo+1) AS rowno,*****************