常用代码（数据库）

-- mysql 格式化时间
DATE_FORMAT(t.oldstarttime ,'%Y-%c-%d %h:%i:%s')
DATE_FORMAT(t.oldstarttime ,'%h:%i:%s')
DATE_FORMAT(t.oldstarttime ,'%Y-%c-%d')
DATE_FORMAT(t.oldstarttime ,'%h:%i')

 

-- NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。
-- CURDATE()以’YYYY-MM-DD’的格式返回今天的日期，可以直接存到DATE字段中。
-- CURTIME()以’HH:MM:SS’的格式返回当前的时间，可以直接存到TIME字段中。

 

--分批删或修改

while 1=1
begin
delete top (5000) from t_ibe_loginfo;
if @@ROWCOUNT <5000 break;
end
go

 

-- 当前时间的前一天
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);

 

-- mysql查询数据每行前添加序列
SELECT @rowno:=@rowno+1 AS rowno,CODE,NAME FROM t_aircom t,(SELECT @rowno:=0) _tmp;

 

-- varchar转int
cast(列名 as int)

-- varchar转int
convert(int,列名);

 

-- mssql分页
-- 2 是页数，下标
-- 10是每页大小
-- 注意第一行从1开始，因为用的num是物理行数字不是索引
select t.id from (
    select ROW_NUMBER()over(order by o.creattime asc) as num,* from t_jd_create_order o with(nolock) where 1=1
    and o.creattime >='2018-06-01'
) as t where num between 1+(2*10) and 2*10+10

 

-- 数据按时间段分组 5分钟一组
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,createtime),120),dateadd(ss,-1,createtime))/5)*5,convert(varchar(10),createtime,120)) as 时间段,  
       count(*) as 行数  
from t_policy_sp with(nolock) where state!='N' and createtime>='2018-06-08'  
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,createtime),120),dateadd(ss,-1,createtime))/5)*5,convert(varchar(10),createtime,120))  
order by 时间段

-- 基本函数使用到的sql
select 
    SUBSTRING(bookPsgs,CHARINDEX(':',bookPsgs)+1,LEN(bookPsgs)-CHARINDEX(':',bookPsgs)+1) as psgs
       ,ROW_NUMBER() over (order by creattime asc) as num
      from t_jd_create_order 
      where 1=1 
      and bookPsgs like #{bookFoals}+'%' 
      and paystate ='1';

 

-- 一列的值去重合并
select( 
select 
CAST(sharefnos.shareflightno1 as varchar)+',' 
from 
(
    select _tmp.shareflightno1 from t_set_shareflightno as _tmp where startcity='CTU' and endcity='XMN' and carrier='MF' group by shareflightno1) sharefnos 
    for xml path('')
) as sharefno

 

-- stuff('a',1,1,'b') a源字符串，第一个位置开始（不是下标），删除1位字符，用b字符串代替，得到b
-- 这里主要是去掉一开始的逗号拼接符
-- select ','+case(col as varhcar) from table for xml path('')拼接多列值成字符串
-- 这个sql的意义，按共享航司分组，并拼接各个共享航司所对应的共享航班号，用逗号拼接    
select 
a.marketcarrier sharecarrier
,STUFF
(
    (
        select 
        ',' + CAST(_tmp.shareflightno1 as varchar(300))
        from t_set_shareflightno as _tmp with(nolock)
        where 1=1
        and _tmp.state!='N'
        and _tmp.startcity='CTU'
        and _tmp.endcity='XMN'
        and _tmp.carrier='MF'
        and _tmp.marketcarrier = a.marketcarrier
        FOR XML PATH('')
    )
    ,1
    ,1
    ,''
)AS sharefnos
from t_set_shareflightno as a with(nolock)
where 1=1
and a.state!='N'
and a.startcity='CTU'
and a.endcity='XMN'
and a.carrier='MF'
group by a.marketcarrier
order by a.marketcarrier

 

--共享航班表，按共享航司分组，显示航线和共享航班号拼接
select distinct
    m.startcity
    ,m.endcity
    ,m.carrier
    ,m.marketcarrier
    ,(
        select 
        distinct
        _a.sharefnos
         from
        (
            select 
            a.startcity as startcity
            ,a.endcity as endcity
            ,a.carrier as carrier
            ,a.marketcarrier as sharecarrier
            ,STUFF
            (
                (
                    select 
                    ',' + CAST(_tmp.shareflightno1 as varchar(300))
                    from t_set_shareflightno as _tmp with(nolock)
                    where 1=1
                    and _tmp.state=a.state
                    and _tmp.startcity=a.startcity
                    and _tmp.endcity=a.endcity
                    and _tmp.carrier=a.carrier
                    and _tmp.marketcarrier = a.marketcarrier
                    --and _tmp.marketcarrier in('')
                    --and _tmp.marketcarrier not in('')
                    FOR XML PATH('')
                )
                ,1
                ,1
                ,''
            )AS sharefnos
            ,ROW_NUMBER()
            over(partition by a.marketcarrier order by a.marketcarrier asc) as p
            from t_set_shareflightno as a with(nolock)
            where 1=1
            and a.state=m.state
            and a.carrier=m.carrier
            and a.startcity=m.startcity
            and a.endcity=m.endcity
            and a.marketcarrier = m.marketcarrier
            --and a.marketcarrier in('')
            --and a.marketcarrier not in('')
            
        )as _a 
        where 1=1
        and _a.sharecarrier = m.marketcarrier
    ) as sharefnos
    from
    t_set_shareflightno as m with(nolock)
    where 1=1 
    and m.state!='N'
    and m.startcity='HGH'
    and m.endcity='CKG'
    and m.carrier='CA'
    and m.marketcarrier in('G5','TV')
    ---and m.marketcarrier not in('')

 

-- 通过 over(partition by xx order by xx)来展示分组后其它的列
select 
        ss.actualcarrier
        ,ss.actualseat
        ,ss.sharecarrier
        ,ss.shareseat
        ,ss.startdate
        ,ss.enddate
        ,ROW_NUMBER() 
        over(
            partition by ss.actualcarrier,ss.actualseat,ss.sharecarrier,ss.shareseat 
            order by ss.actualcarrier,ss.actualseat,ss.sharecarrier,ss.shareseat
        ) 
    from t_config_sharecarrier_seat ss
    where ss.state='Y';