A. 金蝶 k3 如何對帳套執行 sql 指令
這是一個刪除的SQL語句，條件是：部門是EBO， key為Reg，userid是16394 這些條件組合,userid應該是資料庫的唯一標識ID,一般來說用戶名都叫username.
B. 求一句按月匯總銷售額的SQL語句
select substr(to_char(訂單日期,'yyyymmdd'),0,6),sum(銷售額) from 表 group by substr(to_char(訂單日期,'yyyymmdd'),0,6);
C. 請問 sqlserver 各個商品各月銷售額統計 sql語句怎麼寫
可以完成,思路如下:
通過pid 商品編碼分組,得到銷售日期的每個月列,後用sum(caseFact_mWHEN月份 then數量end)來操作即可.
--年度售額：
selectpid商品編碼
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'01'thencCostend)),0)'countCost_1'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'02'thencCostend)),0)'countCost_2'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'03'thencCostend)),0)'countCost_3'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'04'thencCostend)),0)'countCost_4'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'05'thencCostend)),0)'countCost_5'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'06'thencCostend)),0)'countCost_6'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'07'thencCostend)),0)'countCost_7'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'08'thencCostend)),0)'countCost_8'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'09'thencCostend)),0)'countCost_9'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'10'thencCostend)),0)'countCost_10'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'11'thencCostend)),0)'countCost_11'
,isnull(convert(dec(18,2),sum(caseFact_mWHEN'12'thencCostend)),0)'countCost_12'
,isnull(convert(dec(18,2),sum(cCost)))'countTotal'
fromTabgroupbypid
--Fact_m指的就是銷售日期[月],最後還有一列為年度總計
希望能幫到你！
D. 金蝶K3中如何做其中一個客戶的銷售統計，怎麼操作啊著急
銷售統計無非就是關於這個客戶：
①、訂了多少貨，訂的什麼貨；——銷售訂單 過濾某一客戶
②、發貨的情況；——銷售出庫
③、開發票了嗎？——銷售費用發票
④、收款的情況（還有多少收了，多少沒收？什麼時間客戶付款？）——應收款明細表、匯總表
E. 想在金蝶K3 RISE 12.3里做一個這樣的報表，直接用SQL語句做的，如下圖，圖2的表頭是兩行的，怎麼做
SQL做出來的表和上述的內容會略有差異的.就是指你上述表中客戶的內容不會有空行概念,而是填滿的,類似:
A 定單1,bb,cc
A 定單2,cc,dd
B 定單3,xx,yy
B 定單4,xx,cc
這樣的.
F. 請問如何能獲取金蝶K3的【報表分析】的SQL語句
Select t4.FItemID,Sum(t4.FQty) As FQty
Into #ICInventory
From ICInventory t4 Inner Join t_ICItem t1 On t1.FItemID=t4.FItemID
Inner Join t_Stock t3 On t4.FStockID=t3.FItemID
Where 1 = 1
Group By t4.FItemID
Insert Into #ICInventory Select t4.FItemID,Sum(0) As FQty
From ICInventory t4 Inner Join t_ICItem t1 On t1.FItemID=t4.FItemID
Inner Join t_Stock t3 On t4.FStockID=t3.FItemID
Where 1 = 1
and t4.FItemID Not In (Select Distinct(FItemID) From #ICInventory)
Group By t4.FItemID
Insert Into #ICInventory Select t1.FItemID,0 From T_ICItem t1
Where t1.FItemID Not In (Select Distinct(FItemID) From ICInventory)
Select t5.FName As FClassName, t2.FShortNumber,t2.FNumber,t2.FName,t2.FModel,t2.FQtyDecimal,
t3.FName As FBUUnitName,ROUND(t1.FQty, t2.FQtyDecimal) As FActBUUnitQty,t2.FSecInv As FSecInvBUUnitQty,
FBUUnitDiff=ROUND(t1.FQty-t2.FSecInv, t2.FQtyDecimal),
t4.FName As FCUUnitName,ROUND(t1.FQty/t4.FCoeffiCient, t2.FQtyDecimal) As FActCUUnitQty,ROUND(t2.FSecInv/t4.FCoeffiCient, t2.FQtyDecimal) As FSecInvCUUnitQty,
FCUUnitDiff=ROUND(t1.FQty/t4.FCoeffiCient-t2.FSecInv/t4.FCoeffiCient,t2.FQtyDecimal),
FStatus=(Case When ROUND(t1.FQty ,t2.FQtyDecimal) - t2.FSecInv < 0 Then '庫存不足' Else '' End),FSumSort=0
Into #temp
From #ICInventory t1 Inner Join t_ICItem t2 On t1.FItemID=t2.FItemID
Inner Join t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID
Inner Join t_MeasureUnit t4 On t2.FStoreUnitID=t4.FMeasureUnitID
Left Join t_Item t5 On t2.FParentID=t5.FItemID
where (t1.FQty/t4.FCoeffiCient-t2.FSecInv/t4.FCoeffiCient)<0 order by t2.FNumber IF EXISTS(Select * From #TEMP)
Insert Into #Temp
Select '','','','合計','',4,
'',Sum(FActBUUnitQty),Sum(FSecInvBUUnitQty),Sum(FBUUnitDiff),
'',Sum(FActCUUnitQty),Sum(FSecInvCUUnitQty),Sum(FCUUnitDiff),
'',101
From #temp
Select * From #temp Where FBUUnitDiff<0 Order By FSumSort,FSecInvBUUnitQty ASC
Drop Table #icinventory
Drop Table #temp
G. 我要在金碟k3中製作一張報表sql查詢語句怎麼寫
這個和你的流程定義有關，在K3的每個模塊下都有一個「查詢分析」工具，在這里你可以新建查詢，和標準的SQL語句一樣，只是他提供了一些關鍵字用來代替資料庫中的值。
你打開這個工具，它有一個「幫助」，它有一些限制你可以看一下。
H. SQL語句統計每天、每月、每年的銷售總額
一、SQL語句統計每年的銷售總額
select year(ordertime) 年，
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)
二、SQL語句統計每月的銷售總額
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)，
month(ordertime
三、SQL語句統計每日的銷售總額
select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)，
month(ordertime),
day(ordertime)
(8)金蝶k3銷售收入統計sql語句擴展閱讀
mysql查詢每天、每周、每月的數據方法
一、查詢每天的數據
SELECT
COUNT(1) AS countNumber,
DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime
FROM
testTable
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')
二、查詢每周的數據
SELECT
COUNT(1) AS countNumber,
WEEK(createTime) as dateTime
FROM
testTable
GROUP BY WEEK(createTime)
三、查詢每月的數據：
SELECT
COUNT(1) AS countNumber,
MONTH(createTime) as dateTime
FROM
testTable
GROUP BY MONTH(createTime)
I. 金蝶K3ERP銷售報表開發：第一行顯示日期怎麼在第二行開始統計數量
set nocount on SELECT CASE WHEN GROUPING(v1.Fdate)=1 THEN 1 ELSE 0 END AS Fdate_Grouping,
CONVERT(varchar,v1.Fdate) AS Fdate_NOGrouping,
CASE WHEN GROUPING(v1.Fdate)=1 THEN 101
ELSE 0 END AS FSumSort ,SUM(CASE WHEN t4.FName ='（河南）散戶' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty1,
SUM(CASE WHEN t4.FName ='（華東）華潤萬家有限公司' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty2,
SUM(CASE WHEN t4.FName ='（華南）華潤萬家有限公司' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty3,
SUM(IsNull(u1.Fauxqty,0)) AS Fauxqty281, IDENTITY(int,1,1) AS column_name into #CrossTab FROM ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
Inner Join t_Organization t4 on v1.FSupplyID=t4.FItemID
left outer join t_SubMessage t7 on v1.FSaleStyle=t7.FInterID
Inner Join t_Stock t8 on u1.FDCStockID=t8.FItemID
left outer join t_Emp t9 on v1.FFManagerID=t9.FItemID
left outer join t_Emp t10 on v1.FSManagerID=t10.FItemID
Inner Join t_User t11 on v1.FBillerID=t11.FUserID
Inner Join t_ICItem t14 on u1.FItemID=t14.FItemID
Inner Join t_MeasureUnit t17 on u1.FUnitID=t17.FItemID
left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
Inner Join t_MeasureUnit t30 on t14.FUnitID=t30.FItemID
left outer join t_SubMessage t40 on v1.FMarketingStyle=t40.FInterID
left outer join v_ICTransType t70 on u1.FSourceTranType=t70.FID
left outer join ICVoucherTpl t16 on v1.FPlanVchTplID=t16.FInterID
left outer join ICVoucherTpl t13 on v1.FActualVchTplID=t13.FInterID
left outer join t_Department t105 on v1.FDeptID=t105.FItemID
left outer join t_Emp t106 on v1.FEmpID=t106.FItemID
left outer join t_Emp t107 on v1.FManagerID=t107.FItemID
left outer join t_AuxItem t112 on u1.FAuxPropID=t112.FItemid
left outer join t_MeasureUnit t500 on t14.FStoreUnitID=t500.FItemID
left outer join t_Currency t503 on v1.FCurrencyID=t503.FCurrencyID
left outer join t_StockPlace t510 on u1.FDCSPID=t510.FSPID
left outer join ZPStockBill t523 on v1.FInterID=t523.FRelateBillInterID
left outer join t_SonCompany t550 on v1.FRelateBrID=t550.FItemID
left outer join t_MeasureUnit t552 on t14.FSecUnitID=t552.FItemID
left outer join t_SonCompany t560 on v1.FBrID=t560.FItemID
left outer join rtl_vip t650 on v1.FVIPCardId=t650.Fid
left outer join Rtl_WorkShift t651 on v1.FWorkShiftID=t651.FID
left outer join t_BaseBondedManageType t621 on v1.FManageType=t621.FID
Inner Join t_SubMessage t123 on u1.FPlanMode=t123.FInterID
left outer join CRM_ServiceRequest t800 on v1.FInterID=t800.FSourceInterID
left outer join t_Organization t44 on v1.FConsignee=t44.FItemID
left outer join t_SubMessage tVMI on u1.FIsVMI=tVMI.FInterID
left outer join t_Supplier tSup on u1.FEntrySupply=tSup.FItemID
left outer join t_SubMessage tq224 on u1.FChkPassItem=tq224.FInterID
left outer join t_User tcf on v1.FConfirmer=tcf.FUserID
left outer join t_PayColCondition t_Pay on v1.FPayCondition=t_Pay.FID
left outer join t_Item t3416 on u1.FEntrySelfB0178=t3416.FItemID
WHERE 1=1 AND v1.FTranType=21 AND v1.FTranType=21 AND ((v1.FDate>='2014-07-01' AND v1.FDate<'2014-08-01')) Group By v1.Fdate WITH ROLLUP
update #CrossTab set Fauxqty281=Fauxqty1+Fauxqty2+Fauxqty3
select Case Fdate_Grouping When 1 Then '合計' When 2 Then '小計' ELSE Convert(nVarchar,Fdate_NOGrouping) End As Fdate,* from #CrossTab where FSumSort<> 102 order by column_name
其中兩個華潤是我舉例子用的客戶名字，你自己實際情況自己加，然後我行是客戶，列是日期，這個問題應該不大，如果你一樣要行是日期的話，自己改吧~
對了，我用的是12.3版本
J. 金蝶K3 中的銷售出庫sql語句怎麼寫
SELECT *FROM vwICBill_8
這是顯示出庫單的 視圖；也是最好掌握的。