2019/6月份 <<必知必會>>書本練習-實踐練習 order by沒有where就是在前,而又多個列的組合條件在在後面select top 10 * from VF_TSTOSTOCK_UCMLselect top 10 STOID,BPSCOD,BPSNAM,ITMNAM,STOFCYName ...
----------------------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
-----------------------------------------------------------------------