sql 库存明细帐处理示例(包含结存数)
sql库存明细帐处理示例(包含结存数)
sql 库存明细帐处理示例(包含结存数)--结存表CREATE TABLE Stocks(Item varchar(10),Period int,Balance int) INSERT Stocks SELECT 'aa',200501,100UNION ALL SELECT 'cc',200501,100--明细账数据CREATE TABLE tb(ID int IDENTITY PRIMARY KEY,Item varchar(10), --产品编号Quantity int, --交易数量Flag bit, --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)Date datetime) --交易日期INSERT tb SELECT 'aa',100,1,'2005-1-1'UNION ALL SELECT 'aa',90 ,1,'2005-2-1'UNION ALL SELECT 'aa',55 ,0,'2005-2-1'UNION ALL SELECT 'aa',-10,1,'2005-2-2'UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'UNION ALL SELECT 'aa',200,1,'2005-2-2'UNION ALL SELECT 'aa',90 ,1,'2005-2-1'UNION ALL SELECT 'bb',95 ,1,'2005-2-2'UNION ALL SELECT 'bb',65 ,0,'2005-2-3'UNION ALL SELECT 'bb',-15,1,'2005-2-5'UNION ALL SELECT 'bb',-20,0,'2005-2-5'UNION ALL SELECT 'bb',100,1,'2005-2-7'UNION ALL SELECT 'cc',100,1,'2005-1-7'GO--查询时间段定义DECLARE @dt1 datetime,@dt2 datetimeSELECT @dt1='2005-2-2',@dt2='2005-2-10'--查询--期初库存年月及计算期初数的开始时间)DECLARE @Period int,@dt datetimeSELECT @Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112), @dt=DATEADD(Day,1-Day(@dt1),@dt1)--查询期初库存SELECT Item=ISNULL(a.Item,b.Item),Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),[IN]=ISNULL(b.[IN],0),[IN_Retrun]=ISNULL(b.[IN_Retrun],0),[OUT]=ISNULL(b.[OUT],0),[OUT_Return]=ISNULL(b.[OUT_Return],0),Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b .Amount,0)FROM(--期初数SELECT Item,Balance FROM Stocks WHERE Period=@Period)a FULL JOIN(--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)SELECT Item,Date=CONVERT(char(10),@dt1,120),Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE-Quantity END),[IN]=0,[IN_Retrun]=0,[OUT]=0,[OUT_Return]=0,Amount=0FROM tb aWHERE Date>=@dt AND Date<@dt1AND NOT EXISTS(SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))GROUP BY ItemUNION ALL--指定时间段内有交易发生的数据SELECT Item,Date=CONVERT(char(10),Date,120),Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)FROM tb WHERE Item=a.Item AND Date>=@dt ANDDate<MIN(a.Date)),[IN]=SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),[IN_Retrun]=SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),[OUT]=SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),[OUT_Return]=SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)FROM tb aWHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)GROUP BY CONVERT(char(10),Date,120),Item)b ON a.Item=b.ItemORDER BY Item,Date/*--结果Item Date Opening IN IN_Retrun OUTOUT_Return Balance---------- ---------------- -------------- --------------------------- ----------- ------------------- -----------aa 2005-02-02 225 200 10 0 0 415aa 2005-02-03 415 0 0 0 5 420bb 2005-02-02 0 95 0 0 0 95bb 2005-02-03 95 0 0 65 0 30bb 2005-02-05 30 0 15 0 20 35bb 2005-02-07 35 100 0 0 0 135cc 2005-02-01 100 0 0 0 0 100--*/。
sql 出入库表结构设计
sql 出入库表结构设计
设计出入库表结构的关键是要确保表能够准确记录每次出入库操作的必要信息,同时满足数据一致性和查询性能的要求。
以下是一个简单的示例,展示了一个出入库表的基本结构设计:
1.出入库表(Inventory Table)
o ID:出入库记录的唯一标识符,可以使用自增主键。
o产品ID:被出入库的产品的唯一标识符,关联到产品表(Product Table)。
o数量:产品的数量,标识该次出入库操作涉及的产品数量。
o类型:出库(out)或入库(in)。
o时间戳:出入库操作的时间戳,用于记录操作发生的时间。
o操作人:执行出入库操作的人员,可以是员工ID或姓名等。
o备注:可选字段,用于记录任何其他信息。
2.产品表(Product Table)
o产品ID:产品的唯一标识符。
o产品名称:产品的名称或描述。
o单位:产品的计量单位,如个、件、箱等。
o其他产品相关的字段,如供应商、价格等。
以上是一个基本的出入库表结构设计,你可以根据实际需求和
业务逻辑进行扩展和调整。
例如,如果需要记录更详细的操作信息,可以添加更多的字段,如操作地点、操作原因等。
同时,可以根据实际情况建立其他的相关表,如用户表、供应商表等,以满足更复杂的数据需求。
需要注意的是,合理的索引设计以及适当的数据库规范化也是确保良好查询性能和数据一致性的关键。
在设计表结构时,还应考虑到出入库流程的具体要求,以及数据的增删改查操作等因素。
用SQL查询分析实现类似金蝶K3的收发存明细表
⽤SQL查询分析实现类似⾦蝶K3的收发存明细表使⽤SQL查询分析实现类收发存的报表,原始需求在的第四部分。
下图是实现需求。
⼀、准备删除临时表[buy]判断是否存在临时表,存在则删除[/buy]if OBJECT_ID('tempdb..#inv') is not nulldrop table #invif OBJECT_ID('tempdb..#t_mto') is not nulldrop table #t_MTOif OBJECT_ID('tempdb..#t_mtoentry') is not nulldrop table #t_MTOEntry判断是否存在需调整记录[buy]判断采购申请单上是否存在需要调整的记录,存在则继续。
需要满⾜条件是:1.采购申请单上存在MTO计划模式的物料,计划跟踪号不为999999;2.库存中有可调整的物料。
那么库存中哪些是可以调整的物料呢?还得满⾜以下条件:2.1批号为999999;2.2计划跟踪号为空或者999999;3.3⽆浮动计量单位的,基本库存数量要⼤于0,有浮动计量单位的,辅助数量和基本数量的库存均需⼤于0;[/buy]declare @finterid int = 1059if exists (select f1 from (select case when (ti.FSecUnitID = 0 and t2.FQty > 0) OR (ti.FSecUnitID > 0 and t2.FQty > 0 and t2.FSecQty > 0 ) then 1 else 0 end as "F1"from PORequestEntry t1left join ICInventory t2 on t1.FItemID = t2.FItemIDleft join t_ICItem ti on t1.FItemID = ti.FItemIDwhere t1.FInterID = @finteridand t1.FPlanMode = '14035'and t1.FMTONo <> '999999'and t2.FBatchNo = '999999'and t2.FMTONo in ('','999999')) as t where f1 = 1)⼆、开始begin寻找内码查询mto调整单的内码declare @maxNum intselect @maxNum = FMaxNum from ICMaxNum where FTableName = 't_MTOChange'set @maxNum = @maxNum + 1update ICMaxNum set FMaxNum = @maxNum构建表结构将采购申请、存货表、需计算的字段进⾏联结,并构成临时表#invselectt3.FBillNo,t1.FEntryID,t1.FDetailID,t1.FItemID,t1.FMTONo as "t1FMTONo",ti.FSecUnitID,t1.FQty as "t1FQty",t1.FCommitQty,t1.FUnitID,tm.FCoefficient,t1.FAuxCommitQty,t1.FAuxQty,t1.FSecQty as "t1FSecQty",t1.FSecCommitQty,t1.FOrderQty,t1.FMRPClosed,t1.FPlanMode,t1.FEntrySelfP0139, --辅助数量(计算)t1.FEntrySelfP0140, --关联数量t1.FEntrySelfP0141, --关联标志t2.FStockID,t2.FStockPlaceID,t2.FKFPeriod,t2.FKFDate,t2.FQty as "t2FQty",t2.FSecQty as "t2FSecQty",case when t2.FSecQty > 0 then t2.FQty / t2.FSecQty else 0 end as "FSecCoefficient",t2.FBatchNo,t2.FMTONo as "t2FMTONo",CAST(null as int) as "FMTOInterID",CAST(null as decimal(28,10)) as "FChaQty",cast(null as decimal(28,10)) as "FMTOChange",cast(null as decimal(28,10)) as "FSumMTOChange",CAST(null as decimal(28,10)) as "FBegQty",CAST(null as decimal(28,10)) as "FEndQty",cast(null as decimal(28,10)) as "FSecChaQty",cast(null as decimal(28,10)) as "FSecMTOChange",cast(null as decimal(28,10)) as "FSecSumMTOChange",CAST(null as decimal(28,10)) as "FSecBegQty",CAST(null as decimal(28,10)) as "FSecEndQty",DENSE_RANK() OVER (ORDER BY t1.FItemID) AS RANK1,ROW_NUMBER() over (PARTITION by t1.FDetailID order By t1.FEntryID,t2.FMTONo,t2.FQty desc) RANK2 into #invfrom PORequestEntry t1left join ICInventory t2 on t1.FItemID = t2.FItemIDleft join t_ICItem ti on t1.FItemID = ti.FItemIDleft join t_MeasureUnit tm on t1.FUnitID = tm.FMeasureUnitIDleft join PORequest t3 on t1.FInterID =t3.FInterIDwhere t1.FInterID = @finteridand t1.FPlanMode = '14035'and t1.FMTONo <> '999999'and t2.FBatchNo = '999999'and t2.FMTONo in ('','999999')and t2.FQty > 0and ((ti.FSecUnitID = 0 and t2.FQty > 0) OR (ti.FSecUnitID > 0 and t2.FQty > 0 and t2.FSecQty > 0 ))更新更新#inv表的MTO调整单内码update #inv set FMtoInterID = @maxNum构建临时表⽤来存放mto调整单的表头数据create table #t_MTO(FID int,FClassTypeID int,FTranType int,FBillNo nvarchar(255),FDate datetime,FNote nvarchar(255),FBillerID int,FCheckDate datetime,FEmpID int,FCheckerID int,FDeptID int,FStatus smallint,FUpStockWhenSave bit,FPrintCount int,FSourceBillNo nvarchar(50),FSourceTranType int)⽤来存放mto调整单的表体数据create table #t_MTOEntry(FID int,FIndex int,FItemID int,FAuxPropID int,FBatchNo varchar(255),FStockID int,FSPID int,FBaseQty decimal(23,10),FSecUnitID int,FUnitID int,FQty decimal(23,10),FSecCoefficient decimal(23,10),FSecQty decimal(23,10),FChangeQty_Base decimal(23,10),FChangeQty decimal(23,10),FChangeSecQty decimal(23,10),FKFDate datetime,FKFPeriod int,FPeriodDate datetime,FFromMTONo nvarchar(50),FToMTONo nvarchar(50),FChangeBaseQty decimal(23,10),FSelectedProcID int,FEntrySupply int,FStockTypeID int,FMrpNo nvarchar(50))三、计算declare @R1 int = 1declare @maxR int = (select MAX(RANK1) from #inv)while @R1 <= @maxR beginif OBJECT_ID('tempdb..#inv2') is not nulldrop table #inv2select *,DENSE_RANK() OVER (ORDER BY FDetailID) AS RANK3 into #inv2 from #inv where RANK1 = @R1declare @FSecUnitID int = (select top(1) FSecUnitID from #inv2 where RANK1 = @R1)if @FSecUnitID = 0 begin --没有辅助单位的declare @R2 int = 1declare @maxR2 int = (select MAX(RANK2) from #inv2 where RANK3 = 1)declare @FChaQty decimal(28,10) = (select t1FQty from #inv2 where RANK2 = 1 and RANK3 = 1)while @R2 <= @maxR2 beginset @FChaQty = @FChaQty - (select t2FQty from #inv2 where RANK2 = @R2 and RANK3 = 1)update #inv2 set FChaQty = @FChaQty where RANK2 = @R2 and RANK3 = 1if @FChaQty > 0 update #inv2 set FMTOChange = t2FQty where RANK2 = @R2 and RANK3 = 1if @FChaQty <=0 update #inv2 set FMTOChange = t2FQty + FChaQty where RANK2 = @R2 and RANK3 = 1update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where RANK3 = 1update #inv2 set FMRPClosed = case when (FSumMTOChange - t1FQty >= 0 ) then 1 else 0 end where RANK3 = 1update #inv2 set FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0update #inv2 set FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0set @R2 = @R2 +1enddeclare @R3 int = 1declare @maxR3 int = (select MAX(RANK3) from #inv2)while @R3 <= @maxR3 begindeclare @i int = 1declare @maxI int = (select MAX(rank2) from #inv2 where RANK3 = @R3)while @i <= @maxI beginupdate #inv2 set FBegQty = t2FQty where RANK2 = @i and RANK3 = 1update #inv2 set FBegQty = (select FEndQty from #inv2 where RANK2 = @i and RANK3 = @R3 and FEndQty >0) where RANK2 = @i and RANK3 = @R3+1 set @i = @i + 1enddeclare @R31 int = 2while @R31 <= @maxR3 begindeclare @j int = (select min(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)declare @maxJ int = (select max(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)declare @FChaQty2 decimal(28,10)= (select t1FQty from #inv2 where RANK2 = 1 and RANK3 = @R31)while @j <= @maxJ beginset @FChaQty2 = @FChaQty2 - (select FBegQty from #inv2 where RANK2 = @j and RANK3 = @R31)update #inv2 set FChaQty = @FChaQty2 where RANK2 = @j and RANK3 = @R31if @FChaQty2 > 0 update #inv2 set FMTOChange = FBegQty where RANK2 = @j and RANK3 = @R31if @FChaQty2 <=0 update #inv2 set FMTOChange = FBegQty + FChaQty where RANK2 = @j and RANK3 = @R31update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = @R31 and FMTOChange >0) where RANK3 = @R31update #inv2 set FMRPClosed = case when (FSumMTOChange - t1FQty >= 0 ) then 1 else 0 end where RANK3 = @R31update #inv2 set FEndQty = FBegQty - FMTOChange where RANK3 = @R31 and FMTOChange > 0update #inv2 set FEndQty = FBegQty where RANK3 = @R31 and FMTOChange <= 0set @j = @j+1endset @R31 = @R31 +1endset @R3 = @R3 + 1endendif @FSecUnitID >0 begin --有辅助单位的declare @R2s int = 1declare @maxR2s int = (select MAX(RANK2) from #inv2 where RANK3 = 1)declare @FSecChaQty decimal(28,10) = (select FEntrySelfP0139 from #inv2 where RANK2 = 1 and RANK3 = 1)while @R2s <= @maxR2s beginset @FSecChaQty = @FSecChaQty - (select t2FSecQty from #inv2 where RANK2 = @R2s and RANK3 = 1)update #inv2 set FSecChaQty = @FSecChaQty where RANK2 = @R2s and RANK3 = 1if @FSecChaQty > 0 update #inv2 set FSecMTOChange = t2FSecQty where RANK2 = @R2s and RANK3 = 1if @FSecChaQty <=0 update #inv2 set FSecMTOChange = t2FSecQty + FSecChaQty where RANK2 = @R2s and RANK3 = 1update #inv2 set FSecSumMTOChange = (select SUM(FSecMTOChange) from #inv2 where RANK3 = 1 and FSecMTOChange > 0) where RANK3 = 1update #inv2 set FMRPClosed = case when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 else 0 end where RANK3 = 1update #inv2 set FSecEndQty = t2FSecQty - FSecMTOChange where RANK3 = 1 and FSecMTOChange > 0update #inv2 set FSecEndQty = t2FSecQty where RANK3 = 1 and FSecMTOChange <= 0update #inv2 set FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @R2s and RANK3 = 1update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where RANK3 = 1update #inv2 set FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0update #inv2 set FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0set @R2s = @R2s +1enddeclare @R3s int = 1declare @maxR3s int = (select MAX(RANK3) from #inv2)while @R3s <= @maxR3s begindeclare @is int = 1declare @maxIs int = (select MAX(rank2) from #inv2 where RANK3 = @R3s)while @is <= @maxIs beginupdate #inv2 set FSecBegQty = t2FSecQty where RANK2 = @is and RANK3 = 1update #inv2 set FSecBegQty = (select FSecEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FSecEndQty >0) where RANK2 = @is and RANK3 = @R3s+1 update #inv2 set FBegQty = t2FQty where RANK2 = @is and RANK3 = 1update #inv2 set FBegQty = (select FEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FEndQty >0) where RANK2 = @is and RANK3 = @R3s+1set @is = @is + 1enddeclare @R31s int = 2while @R31s <= @maxR3s begindeclare @js int = (select min(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)declare @maxJs int = (select max(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)declare @FSecChaQty2 decimal(28,10)= (select FEntrySelfP0139 from #inv2 where RANK2 = 1 and RANK3 = @R31s)while @js <= @maxJs beginset @FSecChaQty2 = @FSecChaQty2 - (select FSecBegQty from #inv2 where RANK2 = @js and RANK3 = @R31s)update #inv2 set FSecChaQty = @FSecChaQty2 where RANK2 = @js and RANK3 = @R31sif @FSecChaQty2 > 0 update #inv2 set FSecMTOChange = FSecBegQty where RANK2 = @js and RANK3 = @R31sif @FSecChaQty2 <=0 update #inv2 set FSecMTOChange = FSecBegQty + FSecChaQty where RANK2 = @js and RANK3 = @R31supdate #inv2 set FSecSumMTOChange = (select SUM(FSecMTOChange) from #inv2 where RANK3 = @R31s and FSecMTOChange >0) where RANK3 = @R31supdate #inv2 set FMRPClosed = case when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 else 0 end where RANK3 = @R31supdate #inv2 set FSecEndQty = FSecBegQty - FSecMTOChange where RANK3 = @R31s and FSecMTOChange > 0update #inv2 set FSecEndQty = FSecBegQty where RANK3 = @R31s and FSecMTOChange <= 0update #inv2 set FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @js and RANK3 = @R31supdate #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = @R31s and FMTOChange > 0 ) where RANK3 = @R31supdate #inv2 set FEndQty = FBegQty - FMTOChange where RANK3 = @R31s and FMTOChange > 0update #inv2 set FEndQty = FBegQty where RANK3 = @R31s and FMTOChange <= 0set @js = @js+1endset @R31s = @R31s +1endset @R3s = @R3s + 1endendselect * from #inv2四、结束反写采购申请明细表update te sette.FMRPClosed = ti.FMRPClosed,te.FCommitQty = ti.FSumMTOChange,te.FAuxCommitQty = ti.FSumMTOChange / ti.FCoefficient,te.FSecCommitQty = case when ti.FSecSumMTOChange IS null then 0 else ti.FSecSumMTOChange end,te.FOrderQty = ti.FSumMTOChange,te.FEntrySelfP0140 = case when ti.FSecUnitID = 0 then ti.FSumMTOChange else ti.FSecSumMTOChange end,te.FEntrySelfP0141 = 1from PORequestEntry te,#inv2 ti where te.FDetailID = ti.FDetailID插⼊MTO单据体临时表insert into #t_MTOEntryselectFMTOInterID,'' as "FIndex",FItemID,'0' as "FAuxPropID",FBatchNo,FStockID,FStockPlaceID,FBegQty,null as "FSecUnitID",FUnitID,FBegQty / FCoefficient as "FQty",FSecCoefficient,case when FSecBegQty is null then 0 else FSecBegQty end as "FSecQty",'0' as FChangeQty_Base,FMTOChange / FCoefficient as "FChangeQty",case when FSecMTOChange is null then 0 else FSecMTOChange end as "FChangeSecQty",case when (FKFDate = '') then null else FKFDate end AS "FKFDate",FKFPeriod,case when (FKFDate = '') then null else FKFDate + FKFPeriod end AS "FPeriodDate",t2FMTONo,t1FMTONo,FMTOChange,'','','',''from #inv2 where FMTOChange > 0set @R1 = @R1+1end插⼊MTO单据头临时表insert into #t_MTOselectFMTOInterID,1107011,1107011,'MTOAUTO'+LTRIM(str(FMTOInterID)),CONVERT(varchar(10),getdate(),23)+' 00:00:00.000','',16394,GETDATE(),2649,16394,277,1,0,0,FBillNo,70from #inv2插⼊数据表insert into t_MTOChange select * from #t_MTOinsert into t_MTOChangeEntry select * from #t_MTOEntry插⼊审批流Insert Into ICClassCheckRecords1107011(FPage,FBillID,FBillEntryID,FBillNo, FBillEntryIndex,FCheckLevel,FCheckLevelTo,FMode,FCheckMan, FCheckIdea,FCheckDate,FDescriptions) Values (1,@maxnum,0,'MTOAUTO'+ltrim(str(@maxnum)),0,-99,-1,0,16394,'',GetDate(),'审核')Insert Into ICClassCheckRecords1107011(FPage,FBillID,FBillEntryID,FBillNo, FBillEntryIndex,FCheckLevel,FCheckLevelTo,FMode,FCheckMan, FCheckIdea,FCheckDate,FDescriptions) Values (1,@maxnum,0,'MTOAUTO'+ltrim(str(@maxnum)),0,-1,1,0,16394,'',GetDate(),'审核')校对即时库存EXEC CheckInventory更新采购申请单单据头MTO内码update PORequest set FChildren = FChildren + 1,FHeadSelfP0134 = @maxNum where FInterID = @finterid结束end。
SQL实验4
3.------------------------------------------------------------------------------------------
select 材料消耗明细表.*
from 成本预算表,材料消耗明细表
where 材料消耗明细表.单据号=成本预算表.单据号 and 成本预算表.预算单位='112201002 '
13
select *
from 材料消耗明细表
where 单据号 in(
select 单据号
from 成本预算表
where 材料三 >2000
);
单据号 物码 消耗数量 单价
zy2011003 wm001 200 10
zy2011003 wm002 200 10
zy2011003 112201002 s001 10500.00 张三 2011-5-1 2011-5-6 2011-5-23 作业公司作业二队 调剖 6500.00 2000.00 2000.00 2500.00 0.00 2000.00 500.00 1400.00 10400.00 李四 2011-5-26 10400.00 王五 2011-5-28
12.
select distinct 成本预算表.施工单位,SUM(结算金额) 总结算金额
from 成本预算表
group by 施工单位
施工单位 总结算金额
作业公司作业二队 21300.00
作业公司作业三队 21900.00
作业公司作业一队 11900.00
select *
from 成本预算表
where 成本预算表.结算日期>='2011-5-1' and 成本预算表.结算日期<='2011-5-28'
sqlserver存储过程举例
sqlserver存储过程举例SQL Server存储过程是一段预先编译好的SQL代码,能够被多次执行。
它可以接受输入参数并返回输出参数,还可以执行逻辑判断和循环等复杂操作。
下面我列举了10个例子来展示SQL Server存储过程的使用。
1. 创建新的存储过程:```sqlCREATE PROCEDURE sp_CreateNewEmployee@FirstName NVARCHAR(50),@LastName NVARCHAR(50),@Salary FLOATASBEGININSERT INTO Employees (FirstName, LastName, Salary)VALUES (@FirstName, @LastName, @Salary)END```这个存储过程用于向Employees表中插入新的员工记录。
2. 更新存储过程:```sqlCREATE PROCEDURE sp_UpdateEmployeeSalary@EmployeeID INT,@NewSalary FLOATASBEGINUPDATE EmployeesSET Salary = @NewSalaryWHERE EmployeeID = @EmployeeID END```这个存储过程用于更新指定员工的薪水。
3. 删除存储过程:```sqlCREATE PROCEDURE sp_DeleteEmployee @EmployeeID INTASBEGINDELETE FROM EmployeesWHERE EmployeeID = @EmployeeID END```这个存储过程用于删除指定员工的记录。
4. 查询存储过程:```sqlCREATE PROCEDURE sp_GetEmployeeByID@EmployeeID INTASBEGINSELECT * FROM EmployeesWHERE EmployeeID = @EmployeeIDEND```这个存储过程用于根据员工ID查询员工信息。
k3即时库存查询sql语句
4、客户资料表 t_organization
5、供应商资料表 t_supplier
6、单据模板表 com_screen
7、商品型号表 t_icitemcore ;商品属性表 t_icitemmaterial ;基础商品资料视图表 t_icitem
8、商品单位信息表 t_icitembase
where u1.FQty<>0
Select t2.fnumber as 仓库代码,t2.FName as 仓库名称,t1.fnumber as 物料代码 ,t1.FName as 物料名称 ,t1.FModel as 型号,u1.FBatchNo as 批号,t5.fnumber as 仓位代码,t5.FName as 仓位, t3.FName as 基本单位,u1.FQty as 基本单位数量, t19.FName as 第二计量单位,u1.FSecQty as 第二数量, u1.FQtyLock as[ 锁定数量(基本单位)] From #TempInventory u1
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on பைடு நூலகம்1.FStockPlaceID=t5.FSPID
left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID
[FMyKFDate] [varchar] (255),
[FStockTypeID] [Int] NOT NULL,
[FQtyLock] [decimal](28, 10) NOT NULL,
SQL语句心得思迅常用
品牌档案表 t_bd_item_brand会员类别表 t_rm_vip_type会员档案表 t_rm_vip_info限用次数 use_num剩余积分 now_acc_num积累积分 acc_num已用积分 dec_num商品档案表 t_bd_item_info类别编码 item_clsno商品名称 item_name类别档案表 t_bd_item_cls供应商档案表 t_bd_supcust_info库存数量表 t_im_branch_stock分店仓库表 t_bd_branch_info系统操作员表 t_sys_operator供应商商品关系表 t_bd_supcust_item会员付款记录表 t_rm_vip_acclist会员储值表 t_rm_vip_savelist1,修改某个商品的库存use isszmv8goupdate t_im_branch_stockset stock_qty ='库存数'where item_no = '02030114 '2,修改整个系统库存use isszmv8goupdate t_im_branch_stockset stock_qty ='库存数'3,在整个数据库中修改商品类别select * from t_bd_item_info where item_name like '%资生堂%'update t_bd_item_info set item_clsno='01' where item_name like '%资生堂%'4,在每个类别中修改商品类别use isszmv8goupdate t_bd_item_info set item_clsno='修改后的类别' where item_clsno='修改前的类别' and item_name like '%惠氏%'--item_clsno列别编码, item_name 商品名称5,批量修改商品品牌use isszmv8goupdate t_bd_item_info set item_brandname='惠氏',item_brand='请填入品牌编码' where item_name like '%惠氏%'6,开通历史库存查询功能use isszmv8 --执行前请先备份数据库godelete from t_sys_system where sys_var_id = 'histroystock_query'INSERT INTO t_sys_system(sys_var_id, sys_var_name, sys_var_value)VALUES('histroystock_query', '启用历史库存查询' , '1');7,修改所有门店分店变价use isszmv8update t_bd_item_info set branch_price = 1 (“0” 代表不能分店变价,1代表可以)8,修改所有门店前台打折use isszmv8update t_bd_item_info set en_dis = 1 (“0” 代表不能前台打折,1代表可以)9,修改所有门店前台议价use isszmv8update t_bd_item_info set change_price = 1 (“0” 代表不能前台议价,1代表可以)10,修改所有商品积分use isszmv8update t_bd_item_info set vip_acc_flag = 111,修改所有商品赠送use isszmv8update t_bd_item_info set en_gift = '1'12,删除分店机构信息use isszmv8godelete from t_bd_branch_info where branch_no='分店编号'13,删除分店出入库流水use isszmv8godelete from t_im_flow where branch_no='分店编号'14,批量修改积分有效期use isszmv8goupdate t_rm_vip_info set vip_end_date = '2015-10-01' --修改积分有效期,日期可修改15,SQL语句备份数据库use mastergobackup database 数据库名 to disk='D:\备份名.bak'16,SQL语句还原数据库use masterrestore database 数据库名 from disk='c:\备份名.bak' with REPLACE17,修改会员IDuse isszmv7goupdate t_rm_vip_acclist set card_id='新的卡号' where card_id='2011108/6' update t_rm_vip_good_flow set card_id='新的卡号' where card_id='2011108/6' update t_rm_vip_info set card_id='新的卡号' where card_id='2011108/6'update t_rm_vip_savelist set card_id='新的卡号' where card_id='2011108/6' update t_rm_protion_flow set card_id='新的卡号' where card_id='2011108/6'18,修改会员积分有效期use isszmv7goUPDATE t_rm_vip_info SET vip_end_date = '2015-10-01'19,修改会员储值有效期Use isszmV7goUPDATE t_rm_vip_info SET sav_end_date = '2010-11-9'20,未发生业务的门店重新初始化use isszmv8goupdate t_bd_branch_info set com_init='0' where branch_no='门店编码'21,释放日志文件use mastergobackup transaction isszmv8_branch with no_loggoDBCC SHRINKDATABASE(isszmv8_branch)Go22,修改客户信息使用的门店use isszmv9goupdate t_bd_supcust_info set mbranch_no = '门店六位机构号' where supcust_no = '客户编码'go23,会员礼品兑换的总部统一管理开通语句use isszmv8goif not exists (select 1 from t_sys_system where sys_var_id = 'vip_good_zb')begininsert into t_sys_system values('vip_good_zb','会员礼品兑换的总部统一管理','1','否',null,'',null,null)endelsebeginupdate t_sys_system set sys_var_value='1' where sys_var_id = 'vip_good_zb' end24,开通SX3500盘点机使用自编码use isszmv8_branch --专卖V8分部数据库名称goif not exists(select * from t_sys_system where sys_var_id='takestock_itemsub') insert into t_sys_system values('takestock_itemsub','盘点机下载自编码',1,'否','1=使用自编码,0=使用货号',1,null,null)25,将大类编号为30的商品的会员价1,会员价3都调成零售价use isszmv9goUPDATE t_bd_item_info SET vip_price = sale_price,vip_price3 = sale_price WHERE substring(item_clsno,1,2)='30'26,将大类编号为30的商品的会员价1、会员价3设置为等于零售价的8折use isszmv9goUPDATE t_bd_item_info SET vip_price = sale_price*0.8,vip_price3 = sale_price*0.8 WHERE substring(item_clsno,1,2)='30'。
sql_出入库表结构设计_概述说明
sql 出入库表结构设计概述说明1. 引言1.1 概述在数据库设计中,出入库表结构的设计是非常重要且必不可少的一部分。
出入库表用于记录物品的进出情况,包括物品的数量、时间、来源和目标等信息。
合理的出入库表结构设计可以提高数据管理效率,减少数据冗余和错误。
1.2 文章结构本文将系统介绍SQL出入库表结构设计的相关内容,并按照如下结构组织文章内容:- 引言:对本文进行概述说明,介绍文章的目的和结构。
- SQL 出入库表结构设计:详细讨论SQL出入库表结构设计的过程和方法。
- 出库表结构设计:介绍出库表(Outbound Table)在数据库中的设计原则、字段解释以及关联关系等方面。
- 入库表结构设计:阐述入库表(Inbound Table)在数据库中的设计规范,包括字段解释、关联关系等要点。
- 结论:总结SQL出入库表结构设计的要点,并对未来改进空间或优化方向提供相关建议。
1.3 目的本文旨在提供一个全面而清晰的指南,帮助读者理解和掌握SQL出入库表结构设计的基本原则和方法。
通过学习本文,读者将能够:- 全面了解SQL出入库表结构设计的重要性和应用场景。
- 掌握数据需求分析的基本方法,并能够应用到具体的出入库业务中。
- 理解表设计原则及其在实际中的运用。
- 学习主键和索引设计的技巧,从而提高数据库查询效率。
- 获得出库表和入库表结构设计的具体指导,并了解字段解释、关联关系、数据类型和约束设置等方面的要点。
通过本文提供的知识和指导,读者将能够更好地应对出入库表结构设计所面临的挑战,并在实际工作中设计出高效、可靠的数据库结构。
2. SQL 出入库表结构设计2.1 数据需求分析SQL出入库表结构设计的首要任务是对数据需求进行详细分析。
在设计出入库表结构之前,需要明确数据库中需要存储哪些数据以及这些数据有什么特点和关系。
考虑以下问题可以帮助我们进行需求分析:- 需要存储的数据有哪些?例如产品信息、仓库信息、供应商信息等;- 各类数据之间存在怎样的关联关系?例如产品和仓库之间的关系、产品和供应商之间的关系等;- 数据表中需要包含哪些字段?字段应该具有何种类型和约束?- 数据表中是否需要定义其他对象,如触发器、视图或存储过程,以实现更复杂的功能?通过对以上问题的深入思考与调研,能够帮助我们准确地确定SQL出入库表结构的设计方向。
sql 数据库仓库管理
课程设计报告书2011-2012学年第2学期课程名称:网络数据库课程设计设计题目:服装企业ERP系统系别:信息工程系班级:成员:起止日期:指导教师:目录1.概述 (1)1)仓库管理 (1)2.需求分析 (1)1)组织结构 (1)2)处理数据 (2)3)处理功能分析 (2)3.概要设计 (3)1)概念模型设计 (3)2)逻辑结构设计 (8)3)物理设计 (11)4. 详细设计及软件测试 (12)5. 课程设计总结与体会 (12)6. 软件使用说明 (13)7. 致谢 (13)食品企业ERP系统11.概述该系统主要包含仓库管理模块功能。
1)维修管理仓库管理也叫仓储管理,指的是对仓储货物的收发、结存等活动的有效控制,其目的是为企业保证仓储货物的完好无损,确保生产经营活动的正常进行,并在此基础上对各类货物的活动状况进行分类记录,以明确的图表方式表达仓储货物在数量、品质方面的状况,以及目前所在的地理位置、部门、订单归属和仓储分散程度等情况的综合管理形式。
本项业务涉及的数据单据和表格有:库存调拨汇总表(它包括所的内容有仓库编码、产品编码、产品名称、初始库存数量、初始库存金额、进入仓库数量、进入仓库金额、调出仓库数量、调查仓库金额、总的数量变动、总的金额变动、最后库存量、最后金额、库存预报警线)和仓库产品信息(包括仓库编码、产品编码、产品名称、产品类型、进货日期、出货日期、单价、数量、总的价格、生产商、产品计算单位)以初始库存信息(它包括仓库代码、、产品编码、产品名称、库存数量、库存金额)还有仓库盘点(它包括仓库名称、产品编码、产品名称、调出数量、调出的金额、调入的数量、调入的金额、警戒线、调出调入日期)此仓库管理还记录了:库存数量汇总表、库存调拨汇总表、现有仓库、仓库盘点汇、库存不足报警总表、废品处理汇总表、仓库盘点明细表等。
(注释:主要是服装企业ERP系统的现状进行简单说明,说明系统功能、应用场合、发展情况等,请学生完善它。
用友U8维护案例-SQL版本(内部资料)
www . ufsoft . com
销售管理
问题描述: 821先发货模式下发货单不再显示发票号,如何查询发货单开票情况。 解决办法:由于U8支持发货单拆单生成多张发票,所以无法在发货单上显示多个发 票号。可通过以下三种变通办法来查看发货单开票的情况 1.通过发货单列表,设置出结算数量,来查询发货单开票情况; 2.可在发货结算勾对表、发货开票收款勾对表查询发货开票情况; 3.发货单点击右键查询对应的发票;
www . ufsoft . com
销售管理
问题描述:821版本,发货单或发票不能保存、不能审核。 分 析: 常见原因: 1.accsess版本一般是最大单号表出现问题。 2.Sql版本则是目前最大单号异常,如出现0099999999或者000000009z(带字符), 由于821单据号增加时,是先查询已存在的最大单号,再加1,得出目前单据号, 如果遇到上面异常单据号,系统将无法处理。 3.sql版,dispatchlist/s,salebillvouch/s、rdrecord/s的表结构出现问题. 4. 关于单据定义、单据项目定义的表vouchers、invoiceitem_sal等出现问题。 解决办法: 1.修改maxvouch,或者启用远程应用。 2.将异常单号修改正确,或者启用远程应用。 3.参照999表结构,修改相应属性,或者修复表。 4.从999帐套中导入vouchers、invoiceitem_sal等表的默认数值。
问题描述:U821版本,销售统计表数量乘以单价不等金额。 解决办法:update rpt_flddef set condition='价税合计/数量' where id_field in (select id_field from rpt_flddef a inner join rpt_glbdef b on a.id=b.id where b.systemid='sa' and =' 销售 统计表' and a.modeex=0 and ='单价')
用SQL实现统计报表中的“小计”和“合计”
⽤SQL实现统计报表中的“⼩计”和“合计”问题:开发⼀个关于各烟叶等级的⼆次验级的原发件数、原发重量及验收重量的统计报表。
其中,原发件数、原发重量和验收重量等列要求计算出各等级组别的⼩计和所有记录的合计。
语句:SELECT DECODE(GROUPING(T4.TOBACCO_CLASS_TYPE) + GROUPING(T1.TOBACCO_CLASS_NAME),1,DECODE(T4.TOBACCO_TYPE,51, ‘上等烟⼩计’,52, ‘中等烟⼩计’,53, ‘下等烟⼩计’,54, ‘低等烟⼩计’,‘⼩计’),2,‘合计’,T1.TOBACCO_CLASS_NAME) TOBACCO_CLASS_NAME,T4.TOBACCO_CLASS_TYPE,NVL(SUM(_PIECE),0) TOTAL_ORG_PIECE,NVL(SUM(_WEIGHT), 0) TOTAL_ORG_WEIGHT,NVL(SUM(T1.AMOUNT), 0) TOTAL_AMOUNTFROM VI_FK_BALANCE_DETAIL T1, TB_TOBACCO_CLASS T4WHERE T1.TOBACCO_CLASS_ID=T4.TOBACCO_CLASS_IDAND T1.ACCOUNT_YEAR=T4.ACCOUNT_YEARAND T4.DEL_FLAG=0AND T4.ENABLE_FLAG=0AND T1.REC_DATE > TO_DATE(‘2006-11-05’, ‘YYYY-MM-DD’GROUP BY ROLLUP( T4.TOBACCO_CLASS_TYPE,T1.TOBACCO_CLASS_NAME)ORDER BY T4.TOBACCO_CLASS_TYPE通过查询得到统计结果如下表所⽰,该表的统计结果已经满⾜了统计要求。
烟叶等级等级组别原发件数原发重量验收重量(B1F)上桔⼀514945197800197508.1(B2F)上桔⼆518335333400332316.9(C1F)中桔⼀516942776027610.54(C2F)中桔⼆518033212031650.4(C3F)中桔三516381255240255372.6(X1F)下桔⼀517530003012.2上等烟⼩计5121233849320847470.8(B3F)上桔三524701188040187389.9(B4F)上桔四5212248804866.81(C3V)中微青三5217469606934.06(C4F)中桔四524639185560185276.4(X2F)下桔⼆5217396956069029.79(X2V)下微青⼆522610401038.34(X3F)下桔三5212635052050439.86中等烟⼩计5212664506560504975.1 (X4F)下桔四5310240804075.62下等烟⼩计5310240804075.62 (B3K)上杂三5400249.39低等烟⼩计5400249.39合计3399913599601356771。
sqlserver 进销存 计算方法
sqlserver 进销存计算方法进销存管理系统是各类企业日常运营中不可或缺的部分,能够帮助企业合理控制库存、优化供应链、降低运营成本。
SQL Server 是广泛应用于企业中的数据库管理系统,具有强大的数据处理和分析能力。
本文将详细介绍在SQL Server 环境下,进销存管理系统中常用的计算方法。
一、库存计算方法1.库存期初值期初值是指某一商品在计算周期开始时的库存数量。
通常,期初值可以通过以下SQL 语句获取:```sqlSELECT SUM(stock_quantity) AS initial_stockFROM inventoryWHERE product_id = @product_id AND date < @start_date;```其中,@product_id 表示商品ID,@start_date 表示计算周期的开始日期。
2.采购入库采购入库是指在进销存管理系统中,商品从供应商处采购并进入库存的过程。
采购入库的计算方法如下:```sqlSELECT SUM(purchase_quantity) AS purchase_stockFROM purchaseWHERE product_id = @product_id AND date BETWEEN@start_date AND @end_date;```其中,@product_id 表示商品ID,@start_date 和@end_date 分别表示计算周期的开始日期和结束日期。
3.销售出库销售出库是指商品从库存中出库并销售给客户的过程。
销售出库的计算方法如下:```sqlSELECT SUM(sale_quantity) AS sale_stockFROM saleWHERE product_id = @product_id AND date BETWEEN@start_date AND @end_date;```其中,@product_id 表示商品ID,@start_date 和@end_date 分别表示计算周期的开始日期和结束日期。
sql 数学运算
SQL 支持多种数学运算,允许你在查询中执行算术操作。
以下是一些常见的SQL 数学运算示例:1.加法(+):sql复制代码SELECT column1 + column2 AS sum FROM table_name;2.减法(-):sql复制代码SELECT column1 - column2 AS difference FROM table_name;3.乘法(*):sql复制代码SELECT column1 * column2 AS product FROM table_name;4.除法(/):sql复制代码SELECT column1 / column2 AS quotient FROM table_name;5.模运算(%): 返回除法的余数。
sql复制代码SELECT column1 % column2 AS remainder FROM table_name;6.幂运算(^): 在某些数据库中可用。
sql复制代码SELECT column1 ^ column2 AS power FROM table_name;7.取绝对值: 使用ABS()函数。
sql复制代码SELECT ABS(column1) AS absolute_value FROM table_name;8.开方运算: 使用SQRT()函数。
sql复制代码SELECT SQRT(column1) AS square_root FROM table_name;9.求余数: 使用MOD()函数。
这在某些数据库中是可用的。
sql复制代码SELECT MOD(column1, column2) AS modulus FROM table_name;10.指数运算: 使用POW()函数(或POWER())。
这在某些数据库中是可用的。
sql复制代码SELECT POW(column1, column2) AS exponentiation FROM table_name;11.四舍五入: 使用ROUND()函数。
金蝶SQL语句
金蝶SQL语句select * from t_tabledescription where fdescription like '%关键字%'--模糊查询select * from t_Stock --仓库IDselect * from ICInventory --及时库存update ICInventory set fstockid = '仓库ID' --更新语句SELECT * FROM t_SubMessage Where Fname='外购入库'insert into t_SubMessage values('0','30000','FPLX05','103','返修委外','103','0','0','','1','null','')INSERT INTO t_SubMessage (fmodifytime) VALUES (0x00000000000507eF)---出库单区域查找CREATE VIEW ABC AS select FName as 客户, fbillno as 单据编号,FDate as 出库日期 ,DATEADD(day,1095,FDate) AS 质保期 from ICStockBill inner join t_Organization on FSupplyID=FItemID where FTranType='21'CREATE VIEW DEF AS select t_SubMessage.FName as 区域,t_Organization.FName as 客户 from t_Organization inner join t_SubMessage on finterid=fregionidCREATE VIEW chukuxinxi AS select def.区域, abc.客户,abc.单据编号,abc. 出库日期, abc.质保期 from abc inner join DEF on ABC.客户=def.客户select * from chukuxinxi where 区域='国外'---销售发票按照合同号查询---select * from SEOrder---select * from t_Organization---CREATE VIEW a AS select t_Organization.FName as 客户,SEOrder.FBillNo as 订单号, SEOrder.FHeadSelfS0153 as 合同号from SEOrder inner join t_Organization on SEOrder .FCustID=t_Organization .FItemID---CREATE VIEW b AS select * from ICSaleEntry---select * from a---select * from b---select * from c---CREATE VIEW c AS select * from b inner join a on a.订单号=b.forderbillno---select t_ICItem.FName ,c.订单号 ,c.合同号 from c inner join t_icitem on c.FItemID=t_ICItem.FItemID where 订单号<>合同号---select t_ICItem.FName ,c.订单号 ,c.合同号 from c inner join t_icitem on c.FItemID=t_ICItem.FItemID where 订单号=合同号---WMS查询序列号select * from tb_stockout 单据信息select * from tb_stockout_item 单据分项信息select * from tb_stockout_detail 扫描条码信息select tb_stockout.customer_name as 客户,tb_stockout.order_date as 出库日期, dateadd (DAY,1097,order_date ) from tb_stockoutselect * from tb_stockout_detail where stockout_item_id='60' select * from aselect * from bselect * from tb_stockout_detail 扫描条码信息create view b as select a.出库单号 as 出库单号, a.出库单 as 出库单,a.客户as 客户,a.出库日期as 出库日期,a.保质期as 保质期,tb_stockout_detail.box_code as 装箱单, tb_stockout_detail.sequence_number as 序列号 from a inner join tb_stockout_detail on a.序号=tb_stockout_detail.stockout_item_idselect * from tb_stockout_detail 扫描条码信息select * fromtb_stockout 单据信息tb_stockout_item 单据分项信息create view a as select tb_stockout_item.stockout_item_id as 序号,tb_stockout.stockout_no as 出库单号, tb_stockout.stockout_order_no as 出库单,tb_stockout.customer_name as 客户,tb_stockout.order_date as 出库日期,dateadd (DAY,1097,order_date ) as 保质期from tb_stockout inner join tb_stockout_item on tb_stockout.stockout_order_no=tb_stockout_item.stockout_orde r_noselect * from AIS20170515172850 ..DEFexec sp_addlinkedserver 'AIS20121023172833:1433', ' ', 'SQLOLEDB ', '192.168.68.134 'exec sp_addlinkedsrvlogin 'AIS20121023172833:1433 ', 'false ',null, 'sa ', '123 'select * from [192.168.68.134].[AIS20121023172833:1433].[dbo].[t_User_Bak]----序列号查询select * from a ..bselect * from ais ..cselect * from tb_stockoutselect a ..b.保质期,a ..b.序列号, ais ..c.区域 from a ..b inner join ais ..c on a ..b.客户=ais ..c.客户 where 区域<>'国外'create view c as select t_SubMessage.FName as 区域,t_Organization.FName as 客户 from t_Organization inner join t_SubMessage on finterid=fregionidselect * fromselect * from t_fieldDescription where fDescription like '%核算项目%'select * from t_tableDescription where fDescription like '%余额%'select * from t_RP_ContactBal inner join t_Organization on FCustomer=FItemIDselect * from t_RP_ContactBal inner join t_supply on FCustomer=FItemIDselect * from t_TransInitBalance inner join t_Account on t_account.FAccountID=t_TransInitBalance.FAccountID select * from t_Organizationselect * from t_Balance inner join t_Account ont_account.FAccountID=t_Balance.FAccountIDselect * from t_BalanceSELECT t_Balance.FYear as 年 , FPeriod as 月, t_Account.fname as 科目 , t_Balance.FDetailID as 方向 , t_Balance.FCurrencyID as , FBeginBalanceFor, FDebitFor, FCreditFor, FYtdDebitFor, FYtdCreditFor, FEndBalanceFor, FBeginBalance, FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance, FFrameWorkID, FIsAdjustPeriodFROM t_Balance inner join t_Account on t_account.FAccountID=t_Balance.FAccountIDSELECT * FROM ICSale inner join SEOrder on SEOrder.FCustID=ICSale.FCustIDselect * from absselect * from t_OrganizationSELECT SEOrder.FBillNo as 发票号 FROM SEOrder inner join t_Organization on SEOrder.FCustID=t_Organization.FItemID SELECT * FROM SEOrder inner join t_Organization on SEOrder.FCustID=t_Organization.FItemIDselect a.FName as 客户,b.FBillNo as 单据编号,d.fbillno as 发票号 from t_Organization a, seorder b,ICSaleEntry c ,ICSale d where d.FCustID=a.FItemIDselect * from ICSaleselect * from ICSaleEntryselect * from ICSaleEntry inner join ICSaleselect * from @SQLselect * from t_SubMessage where FName='东北区'select t_Organization.fname as 客户, icstockbill.fbillno as 单据from ICStockBill inner join t_Organization on FSupplyID=FItemID a, select t_SubMessage.FName as 区域,t_Organization.FName as 客户from t_Organization inner join t_SubMessage on finterid=fregionid b where a. t_Organization.fname =b.t_Organization.FNameselect * from t_Organizationselect * from ABCselect * from DEFCREATE VIEW chukuxinxi AS select def.区域, abc.客户,abc.单据编号,abc. 出库日期, abc.质保期 from abc inner join DEF on ABC.客户=def.客户select * from chukuxinxiselect * from t_SubMessage where FName='东北区'CREATE VIEW view_name ASselect * from t_Organization--select top 1 * from ICStockBill order by fsupplyidCREATE VIEW DEF AS select t_SubMessage.FName as 区域,t_Organization.FName as 客户 from t_Organization inner joint_SubMessage on finterid=fregionidCREATE VIEW ABC AS select FName as 客户, fbillno as 单据编号,FDate as 出库日期 ,DATEADD(day,1095,FDate) AS 质保期 from ICStockBill inner join t_Organization on FSupplyID=FItemID where FTranType='21'select * fromselect * from t_fieldDescription where fDescription like '%区域%'select * from t_tableDescription where fDescription like '%%'SELECT * FROM ICStockBill where FTranType='28'SELECT * FROM ICStockBill where FTranType='5'SELECT * FROM ICStockBill where FBillNo like '%WWJG%'SELECT * FROM ICStockBill where FTranType='21'select * from t_Sc_NotifyBillPayClientSELECT fsupplyid ,fbillno,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期 FROM ICStockBill where FTranType='21'select * from t_Organization where FItemID='359'SELECT fsupplyid,fbillno,FDate,DATEADD(day,1095,FDate) FROM ICStockBillselect fbillno,FDate, dateadd(DAY,1095,getdate()) from ICStockBillselect * from t_item where FName='联泰'select * from t_item where FName='东北区'select * from t_Organizationselecta.ID,b.ID,/doc/bc1932454.html,,(聚合函数) as 'xxx' from ICStockBillSelect * from t_SubMesType where FTypeID = 26Select * From t_SysFunction Where FNumber = 'pu0301'SELECT FDeleted, FTypeID,FInterID FItemID, FID FNumber,FName,FSpec FROM t_SubMessage where FTypeID='26'select * from t_Balanceselect * from t_Accountselect t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Balance.FAccountID as 科目内码_余额表 from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountIDselectt_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Balance.FAccountID as 科目内码_余额表,from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountIDorder byt_Account.FNumber,t_Balance.FPeriodwhere FTranType='21'select * from t_SubMessage where FName='东北区'select * from t_Organizationselect t_SubMessage.FName as 区域,t_Organization.FName as 客户from t_Organization inner join t_SubMessage on finterid=fregionidselect FName as 客户,fbillno as 单据编号,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期from ICStockBill inner join t_Organization on fsupplyid=FItemID where FTranType='21'select t_SubMessage.FName as 区域,t_Organization.FName as 名称from t_Organization inner join t_SubMessage on finterid=fregionidselect as 区域 , FName as 客户,fbillno as 单据编号,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期from ICStockBill inner join t_Organization on fsupplyid=FItemID where FTranType='21'select * from t_Item where FName='东北区'select Fshortname as 客户,FRegionid as 区域from t_Organization inner join t_Item on FRegionid=FItemIDFRegionid as 区域from t_Organization inner join t_SubMessage on FRegionID=FInterIDselect * from t_Organization--select top 1 * from ICStockBill order by fsupplyidselect * from t_fieldDescription where fDescriptionlike '%区域%'select faddress as 地址, FName as 客户,fbillno as 单据编号,FDate as 到货日期 ,DATEADD(day,1095,FDate) AS 质保期 from ICStockBill inner join t_Organization on fsupplyid=FItemID where FTranType='21'select FName as 区域, FName as 客户,fbillno as 单据编号,FDate as 到货日期 ,DATEADD(day,1095,FDate) AS 质保期 from ICStockBill inner join t_Organization on fsupplyid=FItemID where FTranType='21',DATEADD(day,1095,FDate) AS 质保期from ICStockBill fregionid inner join t_Organization on fsupplyid=FItemID where FTranType='21'billNo as 单据号, FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期 from ICStockBill fregionid inner join t_Organization onfsupplyid=FItemID where FTranType='21'select * from t_tabledescription where fdescription like '%用户%'--模糊查询select top 1 * from ICStockBill order by fsupplyidselect top 1 * from t_Organization order by FItemIDselect FName from ICStockBill inner join t_Organization on fsupplyid=FItemID---单据的总体信息select * from t_UserSELECT fsupplyid ,fbillno,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期 FROM ICStockBill where FTranType='21'select fsupplyid ,fbillno,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期from t_Organization fitemid , ICStockBill FSuperiorID where FTranType='21' and fitemid= FSuperiorIDSELECT fsupplyid ,fbillno,FDate as 到货日期,DATEADD(day,1095,FDate) AS 质保期 FROM ICStockBill where FTranType='21'---凭证与单据的关联select * from t_Voucher where FNumber='26' and FVoucherID='36'select * from t_VoucherEntry where FVoucherID='36'select * from t_tableDescription where FDescription like '%付款%'select * from t_tableDescription where FDescription like '%单据%'select * from t_RP_NewReceiveBill where FNumber='CFKD000004'select * from t_RP_NewReceiveBill where FNumber='CFKD000005'select * from t_rp_Exchangeupdate t_RP_NewReceiveBill set FVoucherID='36' where FNumber='CFKD000004'select * from t_RPVoucherAndBill where FVoucherID='38'select * from v_ICTransTypeselect * From t_checkprojectselect * from t_fieldDescription where FDescription like '%单据%'select * From t_VoucherEntryinsert into t_RPVoucherAndBill values (9635,36,3,1234,0)---单据表select * from t_tableDescriptionselect * from t_fieldDescription--select * from ICMO where FBillNo='RWD17040600011' (任务单)--update ICMO set fqty='3120' where FBillNo='RWD17040600011'--update ICMO set fauxqty='3120' where FBillNo='RWD17040600011'--update ICMO set FStatus='' where FBillNo='RWD17042000011'(任务单)--delete ICMO where FBillNo='RWD17042000011'(任务单)--select * from t_fieldDescription where FDescription='汇报'--select * from ICMORpt where FBillNo='RWDHB006656'(汇报/请检单 )--select * from ICMORptEntry where Finterid='18928'--update ICMORpt set FStatus='' where FBillNo='RWDHB007703'(汇报/请检单 )--delete ICMORpt where FBillNo='RWDHB007703'(汇报/请检单)--select * from ICStockBill where FBillNo='CIN005575' (产品入库单)--select * from ICStockBillEntry where FInterID='74894' (产品入库单)--select* from t_icitem where Fitemid='38668'--select * from t_user where Fname='杨亚娟'--update ICStockBill set fcheckerid='' where FBillNo='CIN005266' (产品入库单)--delete ICStockBill where FBillNo='CIN005266' (产品入库单)--delete ICStockBillEntry where FInterID='74894' and FEntryID='1'(产品入库单)--select * from ICStockBill where FBillNo='SOUT014829' (生产领料单)--select * from ICStockBillEntry where FInterID='71148'(生产领料单)--update ICStockBill set fcheckerid='16631' where FBillNo='SOUT014780' (生产领料单)--delete ICStockBill where FBillNo='SOUT014780'--delete ICStockBillentry where FInterID='71148' andfentryid='4'(生产领料单)--select * from ICStockBill where FBillNo='QOUT004252' (其他出库单)--select * from ICStockBillEntry where FInterID='70173'(其他出库单)--update ICStockBill set fcheckerid='16631' where FBillNo='QOUT004252' (其他出库单)--update ICStockBillentry set fqty='1361' where FInterID='70173' and fentryid='1'--update ICStockBillentry set fauxqty='1361' where FInterID='70173' and fentryid='1'--delete ICStockBill where FBillNo='QOUT004356' (其他出库单)--delete ICStockBillentry where FInterID='70173' and fentryid='1'(其他出库单)set nocount on declare @strItemNumber nvarchar(700) declare @strStockNo nvarchar(100) declare @strSelectSQL varchar(3000) set @strItemNumber='*FItemShortNo*' set @strItemNumber = ''+@strItemNumber+'' set @strSelectSQL='Create Table #TempInventory( [FBrNo] [varchar] (10) NOT NULL ,[FItemID] [int] NOT NULL , [FBatchNo] [varchar] (200) NOT NULL , [FStockID] [int] NOT NULL , [FQty] [decimal](28, 10) NOT NULL) Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty From ICInventory u1 where u1.FQty<>0 Insert Into #TempInventorySelect u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty From POInventory u1 where u1.FQty<>0 Select t1.FNumber AS FMaterialNumber,t1.FModel as FMaterialModel,t1.FName AS FMaterialName,t3.FName as FUnitName,t4.FName as FCUUnitName, sum(u1.FQty) as FBUQty,sum(u1.FQty/t4.FCoefficient) as FCUUQty From #TempInventory u1 left join t_ICItem t1 on u1.FItemID = t1.FItemID left join t_Stock t2 on u1.FStockID=t2.FItemID left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) AND t1.FDeleted=0 AND t1.FShortNumber in ('+''''+@strItemNumber+''''+')'+ 'group by u1.FItemID,t1.FNumber,t1.FModel,t1.FName,t3.FName,t4.FName Order By t1.FNumber Drop Table #TempInventory' exec(@strSelectSQL)--- 在所有SQL命令中,如果数据对象的数据类型为非数值的,则必须加上单引号‘’。
第2章SQL表记录的操作
第2章SQL表记录的操作第一篇:第2章 SQL表记录的操作第2章结构化查询语言SQL二、记录的操作1.插入记录——insert 例题:6.1.1.通过SQL INSERT语句插入元组(“p7”,“PN7”,1020)到“零件信息”表(注意不要重复执行插入操作),并将相应的SQL语句存储在文件one.prg中。
16.1.4.使用SQL语句插入一条记录到“分组情况表”中,插入的记录中,组号是“01”,组名是“通俗唱法”。
将该SQL语句存储在four.prg中。
92.1.1.使用SQL INSERT语句在orders表中添加一条记录,其中订单号为“0050”、客户号为“061002”、签订日期为2010年10月10日。
然后将该语句保存在命令文件sone.prg中。
练习:93.1.3.使用SQL INSERT语句在client表中添加一条记录,其中客户号为“071009”、客户名为“杨晓静”、性别为“女”、出生日期1991年1月1日。
然后将该语句保存在命令文件stwo.prg中。
(注意:只能插入一条记录)18.1.3.用SQL命令向SCORE1表中插入一条记录:学号为“993503433”、课程号为“0001”、成绩为99。
21.1.4.使用SQL语句向自由表golden中添加一条记录(“011”,9,7,11)。
并将该SQL语句存储在文件two.prg中。
44.1.2.利用SQL INSERT语句插入记录(“600028”,4.36,4.60,5500)到stock_bk表中。
2.删除记录——delete 6.1.2.通过SQL DELETE语句从“零件信息”表中删除单价小于600的所有记录,将语句存储在文件two.prg中。
44.1.4.利用SQL DELETE语句删除stock_bk.dbf表中“股票代码”为“600000”的股票。
80.1.2.修改并执行two.prg中的语句(其中的SQL语句有错),使之能够正确完成如下功能:将“销售表”中的日期在2000年12月31日前(含2000年12月31日)的记录物理删除。
sql查询金蝶科目余额表个期间的数据
sql查询金蝶科目余额表个期间的数据selectnull as QYDM,null as SBNF,null as SBYF,本期发生_本年累计_科目余额表.会计年度 as 会计年度,本期发生_本年累计_科目余额表.会计期间 as 会计期间,本期发生_本年累计_科目余额表.借贷方向_1借_负1贷 as 借贷方向_1借_负1贷,本期发生_本年累计_科目余额表.科目级次 as 科目级次,本期发生_本年累计_科目余额表.明细科目_1是_0否as 明细科目_1是_0否,本期发生_本年累计_科目余额表.科目编码 as KM,本期发生_本年累计_科目余额表.科目名称 as 科目代码,本期发生_本年累计_科目余额表.本币借方发生额 as 本期借方发生数BQJF,本期发生_本年累计_科目余额表.本币贷方发生额 as 本期贷方发生数BQDF,本期发生_本年累计_科目余额表.本年本币借方发生额 as 本年借方发生数BNJF,本期发生_本年累计_科目余额表.本年本币贷方发生额 as 本年贷方发生数BNDF,年初借方_年初贷方_余额_C.年初借方余额 as 年初借方余额NCJF, 年初借方_年初贷方_余额_C.年初贷方余额 as 年初贷方余额NCDF,期初借方_期初贷方_余额_A.期初借方余额 as 上期借方余额SQJF, 期初借方_期初贷方_余额_A.期初贷方余额 as 上期贷方余额SQDF,期末借方_期末贷方_余额_B.期末借方余额 as 期末借方余额QMJF, 期末借方_期末贷方_余额_B.期末贷方余额as 期末贷方余额QMDFfrom(select(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本年索引码_年_币_科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Balance.FDebit as 本币借方发生额,t_Balance.FCredit as 本币贷方发生额,t_Balance.FYtdDebit as 本年本币借方发生额,t_Balance.FYtdCredit as 本年本币贷方发生额,t_Balance.FAccountID as 科目内码_余额表,t_Account.FAccountID as 科目内码_科目表,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountIDwheret_Balance.FCurrencyID = 1)as 本期发生_本年累计_科目余额表--左连接<期初借方和期初贷方> 余额数据left outer join(select *from(select(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-'+ convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FBeginBalance ) as 期初借方余额,null as 期初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FBeginBalance >= 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 期初借方余额,( - t_Balance.FBeginBalance ) as 期初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FBeginBalance < 0 )andt_Balance.FCurrencyID = 1--以下为会计科目方向为 <贷方>的语句union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FBeginBalance ) as 期初借方余额,null as 期初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FBeginBalance > 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 期初借方余额,( - t_Balance.FBeginBalance ) as 期初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FBeginBalance <= 0 )andt_Balance.FCurrencyID = 1) as 期初借方_期初贷方_余额) as 期初借方_期初贷方_余额_Aon 本期发生_本年累计_科目余额表.本期索引码_年月币科目内码= 期初借方_期初贷方_余额_A.本期索引码_年月币科目内码left outer join--左连接<期末借方和期末贷方> 余额数据(select *from(select(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-'+ convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FEndBalance ) as 期末借方余额,null as 期末贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FEndBalance >= 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-'' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 期末借方余额,( - t_Balance.FEndBalance ) as 期末贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FEndBalance < 0 )andt_Balance.FCurrencyID = 1--以下为会计科目方向为 <贷方>的语句union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-'' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FEndBalance ) as 期末借方余额,null as 期末贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FEndBalance > 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本期索引码_年月币科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 期末借方余额,( - t_Balance.FEndBalance ) as 期末贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FEndBalance <= 0 )andt_Balance.FCurrencyID = 1) as 期末借方_期末贷方_余额) as 期末借方_期末贷方_余额_Bon 本期发生_本年累计_科目余额表.本期索引码_年月币科目内码= 期末借方_期末贷方_余额_B.本期索引码_年月币科目内码--左连接<年初借方年初末贷方> 余额数据left outer join(select *from(select(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本年索引码_年_币_科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FBeginBalance ) as 年初借方余额,null as 年初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FBeginBalance >= 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本年索引码_年_币_科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 年初借方余额,( - t_Balance.FBeginBalance ) as 年初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = 1 ANDt_Balance.FBeginBalance < 0 )andt_Balance.FCurrencyID = 1--以下为会计科目方向为 <贷方>的语句union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本年索引码_年_币_科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,( + t_Balance.FBeginBalance ) as 年初借方余额,null as 年初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初大于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FBeginBalance > 0 )andt_Balance.FCurrencyID = 1union allselect(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,(convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID))) + '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) ) as 本年索引码_年_币_科目编码,t_Balance.FYear as 会计年度,t_Balance.FPeriod as 会计期间,t_Account.FNumber as 科目编码,t_Account.FName as 科目名称 ,t_Account.FDC as 借贷方向_1借_负1贷,t_Account.FLevel as 科目级次,t_Account.FDetail as 明细科目_1是_0否,null as 年初借方余额,( - t_Balance.FBeginBalance ) as 年初贷方余额from t_Balance left outer join t_Account on t_Balance.FAccountID = t_Account.FAccountID--1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初小于0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/where (t_Account.FDC = -1 ANDt_Balance.FBeginBalance <= 0 )andt_Balance.FCurrencyID = 1) as 年初借方_年初贷方_余额where 会计期间 = 1) as 年初借方_年初贷方_余额_Con 本期发生_本年累计_科目余额表.本年索引码_年_币_科目内码= 年初借方_年初贷方_余额_C.本年索引码_年_币_科目内码查询结果:。
sql语句 列运算
sql语句列运算列运算是指在SQL语句中对列进行数学运算或逻辑运算,从而得到一个新的结果列。
下面是我列举的十个符合要求的SQL列运算示例:1. 求和运算:使用SUM函数对某一列进行求和操作,例如计算订单表中的订单总金额。
```SELECT SUM(amount) AS total_amount FROM orders;```2. 平均值运算:使用AVG函数对某一列进行平均值计算,例如计算学生成绩表中某一科目的平均分数。
```SELECT AVG(score) AS average_score FROM student_scores WHERE subject = 'Math';```3. 最大值运算:使用MAX函数找出某一列中的最大值,例如找出商品表中价格最高的商品。
```SELECT MAX(price) AS max_price FROM products;```4. 最小值运算:使用MIN函数找出某一列中的最小值,例如找出员工表中年龄最小的员工。
```SELECT MIN(age) AS min_age FROM employees;```5. 计数运算:使用COUNT函数统计某一列中非空值的个数,例如统计订单表中已完成的订单数量。
```SELECT COUNT(*) AS completed_orders FROM orders WHERE status = 'completed';```6. 字符串拼接运算:使用CONCAT函数将多个字符串列拼接成一个新的字符串列,例如拼接用户表中的姓和名。
```SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;```7. 日期计算运算:使用DATEDIFF函数计算两个日期之间的天数差,例如计算某个商品的上架天数。
```SELECT DATEDIFF(NOW(), release_date) AS days_on_marketFROM products WHERE id = 1;```8. 条件运算:使用CASE语句根据某一列的值进行条件判断,并返回不同的结果,例如根据学生成绩判断等级。
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Period int,@dt datetime
SELECT @Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112),
UNION ALL SELECT 'bb',65 ,0,'2005-2-3'
UNION ALL SELECT 'bb',-15,1,'2005-2-5'
UNION ALL SELECT 'bb',-20,0,'2005-2-5'
[IN]=SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),
[IN_Retrun]=SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),
[OUT]=SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),
aa 2005-02-02 225 200 10 0 0 415
aa 2005-02-03 415 0 0 0 5 420
bb 2005-02-02 0 95 0 0 0 95
@dt=DATEADD(Day,1-Day(@dt1),@dt1)
--查询期初库存
SELECT Item=ISNULL(a.Item,b.Item),
Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),
Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),
UNION ALL SELECT 'bb',100,1,'2005-2-7'
UNION ALL SELECT 'cc',100,1,'2005-1-7'
GO
--查询时间段定义
DECLARE @dt1 datetime,@dt2 datetime
cc 2005-02-01 100 0 0 0 0 100
--*/
Flag bit, --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)
Date datetime) --交易日期
INSERT tb SELECT 'aa',100,1,'2005-1-1'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
Date=CONVERT(char(10),Date,120),
Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb WHERE Item=a.Item AND Date>=@dt AND Date<MIN(a.Date)),
--结存表
CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
INSERT Stocks SELECT 'aa',200501,100
AND NOT EXISTS(
SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))
GROUP BY Item
UNION ALL
--指定时间段内有交易发生的数据
SELECT Item,
GROUP BY CONVERT(char(10),Date,120),Item
)b ON a.Item=b.Item
ORDER BY Item,Date
/*--结果
Item Date Opening IN IN_Retrun OUT OUT_Return Balance
---------- ----
----------------- ----------- ------------------- -----------
Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END),
[IN]=0,
[IN_Retrun]=0,
[OUT]=0,
[OUT_Return]=0,
Amount=0
FROM tb a
WHERE Date>=@dt AND Date<@dt1
UNION ALL SELECT 'aa',200,1,'2005-2-2'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'bb',95 ,1,'2005-2-2'
bb 2005-02-03 95 0 0 65 0 30
bb 2005-02-05 30 0 15 0 20 35
bb 2005-02-07 35 100 0 0 0 135
FROM(
--期初数
SELECT Item,Balance FROM Stocks WHERE Period=@Period
)a FULL JOIN(
--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
SELECT Item,
Date=CONVERT(char(10),@dt1,120),
UNION ALL SELECT 'aa',55 ,0,'2005-2-1'
UNION ALL SELECT 'aa',-10,1,'2005-2-2'
UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'
[IN]=ISNULL(b.[IN],0),
[IN_Retrun]=ISNULL(b.[IN_Retrun],0),
[OUT]=ISNULL(b.[OUT],0),
[OUT_Return]=ISNULL(b.[OUT_Return],0),
Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)
[OUT_Return]=SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),
Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb a
WHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)
UNION ALL SELECT 'cc',200501,100
--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
Item varchar(10), --产品编号
Quantity int, --交易数量