———————–2019/6月份 <<必知必会>>书本练习-实践练习—————————
—order by没有where就是在前，而又多个列的组合条件在在后面
select top 10 * from vf_tstostock_ucml
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname from vf_tstostock_ucml where (prodcod =’al15-083′ or stofcy =’l1′) and vcrtypname =’杂项入仓单’ order by stoid desc
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,*from vf_tstostock_ucml where stu in(‘黑色.均码’,’dba152601.均码’)
select top 10 * from vf_tstostock_ucml
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname from vf_tstostock_ucml where not stu =’黑色.均码’
–desc降序 z-a-1
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,stu from vf_tstostock_ucml where stu <> ‘黑色.均码’
order by bpscod desc
——————2.-like的搜索————————
–不等于stu <> ‘黑色.均码’值 模糊查询+排序+分组
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,stu from vf_tstostock_ucml where stu <> ‘黑色.均码’
and stu like ‘红%’ order by bpscod desc
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,stu,*from vf_tstostock_ucml where stu <> ‘黑色.均码’
–‘红%l%’ 与’红%l’ 的区别：%l%这个是为了区分空格就是不被索引出来的，一般用这个哈，方正结果都一样
and stu like ‘红%l%’
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,stu,*from vf_tstostock_ucml where stu <> ‘黑色.均码’
and stu like ‘红%l’
–12.均码 –dba150903.均码 ‘[^红1d]%中的^ –是脱字符–检索不属与红，1，d的开头，同时值又不等于黑色.均码的
select top 10 stoid,bpscod,bpsnam,itmnam,stofcyname,stu,*from vf_tstostock_ucml where (stu <> ‘黑色.均码’)
and stu like ‘[^红1d]%’ order by itmcod desc
——————-3.拼接字符———————–
–无效？？？50页的知识
— select top 10 bpsnam +'(’+con_content+‘)’ as 公司形象 from vf_tstostock_ucml order by itmcod
select top 10 bpsnam +’/(con_content)’ as 公司形象 from vf_tstostock_ucml order by itmcod
–select top 10 bpsnam ||'(con_content)’ from vf_tstostock_ucml order by itmcod –sql不能用–其他的mysql可以
–ririm函数，为确定格式化的数据，就比如拼接字符串的空啊(sum )这些。-就在需要拼接的字符串前面加ririm函数实现后变为(sum)
–ltrim函数 （去掉串左边的空格） trim(去掉两边的空格)
select top 10 * from vf_tstostock_ucml
–计算字段–就是计算他的总额 两句差不多有点相识–
select top 10 bpscod,bpsnam,itmnam,stofcyname,stu,pcu*qtypcu as pq from vf_tstostock_ucml where bpscod =’z999999′
select top 10 bpscod,bpsnam,itmnam,stofcyname,stu,pcu*qtypcu as pq from vf_tstostock_ucml order by bpscod desc
–使用函数：convert()–强转日期 getdate() –获取当前日期 substring()–获取串的组成部分
–mysql这个平台使用–不同是curdate —获取当前日期
———————————-groug by 分组————————————————————–
———————————————————————————————————–
———————————————————————————————————————
——————————–2019/6/15 <<即查即用>>书本练习-基础练习–————————–
–统计的年表–
select count(totqtystu) from tsohorder where totqtystu <>1
–基础表
select top 10 * from tsohorder
–排序从小到大,,,
select * from tsohorder order by sohnum asc
–四种别名的方式
select sohid 哈哈,shipto “不服”,sohtyp “你真棒”,credep as “正常”,* from tsohorder order by bpccod
–为聚合函数设置别名 min ,sum ,count,avg,max
select min(sohid) as ‘最大值’,max(totlinamt) as ‘最大值’,sum(sohid) as ‘总和’,avg(sohid) as ‘平均值’,count(*) as 多少 from tsohorder
–去掉重复
select distinct top 100 bpcnam from tsohorder
–总结结合– 去掉重复 100限制查询，别名，拼接，显示返回的 列和所以，排序+降序, 模糊查询筛选，
select distinct top 100 bpcnam as ‘名称’,sohnum +’/’ as 拼接,*from tsohorder
where creusr like ‘n%’ and bpcnam =’散客’
order by bpccod desc
————————拼接+字段合计+算数运行+强转————————-
–拼接字符串合并为一列
–人生思考 –这个别名的用途在创建视图的时候会用到 –显示的数据便是创建别名的数字
select top 10 sohnum+’/’+bpcnam+’/’+convert(varchar(100),totlinamt) as ‘两列合并’,*from tsohorder
–算数平均数–心得 –只对数字有用，字符串的无效
select top 10 totatilinamt+currat as ‘两列计算’,totqtystu%totlinamt as ‘余数’,convert(varchar(100),totlinamt/dlvsta)+’/’+bpcnam as 除法 from tsohorder
–四舍五入出错–从数据类型 varchar 转换为 float 时出错。 原因前面是整数，后面是字符串–
–想办法在里面在嵌套一层，四舍五入保留两位小数
–select top 10 totatilinamt+currat as ‘两列计算’,totqtystu%totlinamt as ‘余数’,round(convert(float,totlinamt/dlvsta),2)+’/’+bpcnam as 除法 from tsohorder
–保留2位小数加强转 余数：2%3,7%3
select top 10 (((totatilinamt+currat*dlvsta)*2-1))%10 as ‘各类加急乘除运行’,totqtystu%totlinamt as ‘余数’,convert(varchar(100),totprerecamt%totatilinamt) as ‘2%3或者7%3’,convert(varchar(100),round(convert(float,totlinamt/dlvsta),2))+’/’+bpcnam as 除法 from tsohorder
–totlincost限时条数不等于0的数,为零的不显示，说明了少了很多条为空的数据不给它显示了
select top 100 sohid+(50*4) as 元,*from tsohorder
where totlincost <>0
————————--年-月–日 格式 –—————————————–
select top 100 sohid+(50*4) as 元,convert(varchar(100),rstprerecamt)+’元’ as ‘金额’,
convert(varchar(10),month(subtim))+’月’,
getdate() as ‘当前操作时间’,
convert(varchar(10),year(getdate()))+’-‘+convert(varchar(10),month(getdate())) +’-‘+’01’ as ‘年-月=日’,
dateadd(month,1,orddat)-day(dateadd(day,1,orddat)) as ‘日差值’,
datediff(yy,’2018-10-10′,’2020-10-22′) as ‘年间隔差’,
datediff(month,’2018-10-10′,’2019-10-22′) as ‘月间隔差’,
datediff(day,’2018-10-10′,’2019-10-22′)+1 as ‘日间隔差’,
dateadd(month,1,orddat)-day(dateadd(month,1,orddat)) as ‘本月月底’,
dateadd(yy,datediff(yy,0,orddat)-1, 0) as ‘去年第一天’,
dateadd(ms,-3,dateadd(yy,datediff(yy,0,orddat), 0)) as ‘去年最后一年’,
dateadd(yy,datediff(yy,0,orddat)+1, 0) as ‘本年第一天’,
dateadd(ms,-3,dateadd(yy,datediff(yy,0,orddat)+2, 0)) as ‘本年最后一年’,
dateadd(yy,datediff(yy,0,orddat)-2, 0) as ‘近2年开始’,
dateadd(ms,-3,dateadd(yy,datediff(yy,0,orddat)+1, 0)) as ‘近2年结束’,
—
1+2,
*from tsohorder
where totlincost<>0 and rstprerecamt<>0
select top 100 * from tsohorder
—总仓stofcyname 名字不对齐，总仓这一些一点，后面中间又是总仓-还有值为的null，会明显多出来很多，多出来400条
select stofcyname,* from vf_tpthreceipthd_ucml
—限制一个条件不等于null，（并且总仓数据的时候会比较整齐，比如说总仓的时候不会乱–，都是按照选项有序）
–同时为空的显示，少了很多条数据，有利于优化速度
select stofcyname,* from vf_tpthreceipthd_ucml
where stofcyname is not null
–===========================中间过渡- -项目实践-核心====================—-
———————————————————————————–
—len 为了salfcyl的l后面的数字对其有序排序，
–salfcy asc这个不能少，少了就乱了
select len(‘no’),len(‘日期+1’),len(12345),salfcy from tsohorder
group by salfcy
order by len(salfcy) asc,salfcy asc
–where dateadd(‘2018’,datediff(‘2018’,0,getdate()),0) < orddat
————-实践下视图的部分–
select
‘年’ as curtype,
–,null as year –年份/地点2019/12楼96档
curyear,
null as salfcy,–销售地点
null as salfcyname, –销售地点
sum(totatilinamt) as totatilinamtoder, –1.订单金额：9,000，00
count(totqtystu) as sohnumoder, –2.订单笔数
sum(totatilinamt)/count(totqtystu) as totatilinamtprice, –3.客单价unit price
count(bpccod) as sohnumbpcqty, –4.客户个数
sum(prodcodqty) as prodcodqty, –5.销售数量：980款/90.000件
sum(tsr.totqtystu) as totqtystumun, –6。发货数量：89,999件（订单上的发货汇总
sum(nsendqty) as totqtystumuncat,–未发数量 –7.非当天发货数量
sum(totatilinamt)/sum(prodcodqty) as totatilinamtpricecot —8.均价 金额除以销售数量
from(
select
year(tsr.orddat) as curyear,
tsr.totatilinamt,
tsr.totqtystu,
tsr.bpccod,
tp.prodcodqty,
tsp.nsendqty,
tsp.nsendqty,
tsp.prodcodqty
isnull(tsr.qtypcu,0)-isnull(dtsrsdhqty,0)+isnull(d.cncqty,0) as unsendqty –2018-06-25 su 未发货数量 –2018-09-06过滤红字订单的
from
tsohorder tsr,tsodorderd tp where tsr.sohnum=tp.sohnum and h.sohsta=’2′
and h.orddat>=@curyear_sdaydat
and h.orddat<=@curyear_edaydat
) as t
group by curyear,salfcy
order by len(salfcy) asc,salfcy asc
———————————————————————–