BULK insert A from 'C:/CDR201301.txt' --批量插入表
sp_iqcolumn('tv_user') --查看表中数据列/*CASE..WHEN条件选择*/
select distinct case when tv_vod.series_flag = 1 then tv_vod.series_id else tv_vod.program_id end item_id, case when tv_vod.series_flag = 1 then tv_vod.series_name else tv_vod.program_name end item_name,tv_castrolemap.castrole_type, tv_cast.cast_id, tv_cast.cast_name into item_castfrom tv_contentmapping, tv_vod, tv_castrolemap, tv_castwhere tv_contentmapping.parent_id = tv_vod.program_idand tv_contentmapping.element_id = tv_castrolemap.castrole_idand tv_castrolemap.cast_id = tv_cast.cast_idand tv_contentmapping.element_type = 7 (select L.R,max(L.R) as gt,min(L.R)as lt from (select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count) as F,sum(s_fee) as M from iac_fct_m201408_tmp group by s_userid)L group by L.R)select min(L.R),max(L.R)-min(L.R),min(L.F),max(L.F)-min(L.F),min(L.M),max(L.M)-min(L.M) from (select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count) as F,sum(s_fee) as M from iac_fct_m201408 group by s_userid)Lselect L.s_userid,convert(numeric(8,6),-avg(L.R)/30) as Rl,convert(numeric(8,6),(1-avg(L.F))/240) as F1,convert(numeric(8,6),(-avg(L.M))/2671) as M1 from (select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count) as F,sum(s_fee) as M from iac_fct_m201408 group by s_userid)L group by L.s_useridselect name from sysobjects where type='U'
select max(L.F), from
(select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count) as F,sum(s_fee) as M from iac_fct_m201408_tmp group by s_userid)Lselect L.s_userid,convert(numeric(8,6),(1-avg(L.F))/41) as F1 from
(select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count)as F,sum(s_fee) as M from iac_fct_m201408_tmp group by s_userid)L group by L.s_userid select max(L.M),min(L.M),max(L.M)-min(L.M) from (select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count) as F,sum(s_fee) as M from iac_fct_m201408_tmp group by s_userid)Lselect L.s_userid,convert(numeric(8,6),(3-avg(L.M))/395) as M1 from
(select s_userid,datediff(dd,max(substring(s_maxdate,1,8)),'20140831') as R,sum(s_count)as F,sum(s_fee) as M from iac_fct_m201408_tmp group by s_userid)L group by L.s_userid select s.mdn, s.cdr_city_id, s.area_id, s.mdn_prov_id, t.s_cycle_date is_findfrom areaky00001 s left join (select min(k.s_cycle_date) s_cycle_date, k.mdn, k.cdr_city_idfrom areaky00001 kwhere k.cdr_city_id != k.mdn_city_idgroup by k.mdn, k.cdr_city_id) ton s.s_cycle_date = t.s_cycle_date and s.mdn = t.mdn and s.cdr_city_id = t.cdr_city_idk.cdr_city_id /*人数按省份排名比例*/select M.cdr_city_id,M.city_name,M.f_rank,M.mdn_prov_id,M.cp_name,convert(char(25),f_count*100/f_total)||'%' as f_rate from (select cdr_city_id,city_name,mdn_prov_id,cp_name,count(distinct mdn) as f_count,rank() over(partition by cdr_city_id order by f_count desc) as f_rank from tmp_lvj_quansheng,kyfx_cfg_city,kyfx_cfg_province where convert(int,city_id)=cdr_city_id and mdn_prov_id=cp_idgroup by cdr_city_id,city_name,cp_name,mdn_prov_id)M,(select cdr_city_id,count(distinct mdn) as f_total from tmp_lvj_quanshenggroup by cdr_city_id)N where M.cdr_city_id=N.cdr_city_id