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
这是显示出库单的 视图；也是最好掌握的。