———————-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
———————————————————————–
