convert from text to varchar有默认值=30

最新推荐文章于 2022-01-23 09:23:37 发布
最新推荐文章于 2022-01-23 09:23:37 发布
阅读量162 收藏
点赞数
分类专栏： 技术天地 文章标签： Sybase
本文介绍了在Sybase环境中，如果不指定VARCHAR的长度，默认情况下长度为30字节的问题。特别强调了在进行类型转换时，如果未明确指定长度可能会导致数据截断的风险。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

在sybase环境 select CHAR_LENGTH(convert(varchar, someTextType)) from someTable
server return 30
sybase中如果不指定convert(varchar(n), sometextType) 那么n默认值是30，今天就吃了这苦头，发贴记下来
在where中如果么比较sometext 和varchar字段相等记住n一定么显示声明，n的大小是target column的varchar(n)
否则convert的最大长度只会是30

Sybase UserGuide 原文如下
[quote][size=18]Converting from one character type to another[/size]
When you convert from a multibyte character set to a single-byte character set,characters with no single-byte equivalent are converted to blanks.text columns can be explicitly converted to char, nchar, unichar, varchar,univarchar, or varchar. You are limited to the maximum length of the character datatypes, pagesize. If you do not specify the length, the converted value has a default length of 30 bytes.[/quote]
Sybase 时间处理Convert 的使用
五月天的博客
10-18 2393
1. 怎样将datetime类型的日期转换为yyyy-mm-dd格式   SELECT STR_REPLACE(CONVERT(VARCHAR, GETDATE(), 111) ,'/','-');   2. DateTime转换成Varchar样式   语句及查询结果： Select CONVERT(varchar(100), GETDATE(), 0): 05 16...
mysql varchar类型实例_mysql varchar类型求和实例操作
weixin_28748867的博客
02-27 1780
mysql varchar类型求和实例操作有的小伙伴在学习数据库的时候，创建表结构的时候不小心把某字段设置成了varchar但是在统计求和的时候就傻眼了，接下来跟着小编学习一下，不用改该列数据类型也能求和的方法吧！1、打开 数据库连接客户端Navicat Premium ，创建一个新的表结构，这里age这列 故意 设置为 varchar。2、创建表成功之后，为刚刚的表创建一些测试的数据，这里如下图...
2018-06-28 第五十三天 oracle
weixin_34121304的博客
06-28 115
一、数据库对象 oracle 的数据库对象：表、用户、约束、索引、序列、视图 （一）关于用户的操作 --创建一个新的用户，然后给新的用户创建一张表，然后给表中添加一些数据。查询表中的数据。 --对用户进行操作 --创建用户需要当前用户拥有dba的权限。 --新创建的用户没有任何的权限，连基本的登录的权限都没有。 createuser...
第三十一章 SQL函数 CONVERT
yaoxin521123的博客
01-23 1万+
文章目录第三十一章 SQL函数 CONVERT参数描述CONVERT(datatype,expression,format-code){fn CONVERT(expression,datatype)}CONVERT 类方法示例CONVERT() 示例{fn CONVERT()} 示例 第三十一章 SQL函数 CONVERT 将给定表达式转换为指定数据类型的函数。 CONVERT(datatype,expression[,format-code]) {fn CONVERT(expression,dataty
修改表字段长度为VARCHAR(80) 报 This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
06-04
-改为TEXT字段后，该字段将不能有默认值（除了MySQL5.7.7之前的版本），但注意新版本（8.0）中允许对TEXT设置默认值（使用表达式），如DEFAULT''，但不支持直接使用字面值（除非是常量表达式）。-改为TEXT后，排序和...
Set NoCount On SET ANSI_WARNINGS OFF Create Table #Happen2( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FQty4 Decimal(28,10), FCUUnitQty4 Decimal(28,10), FAmount4 Decimal(28,10), FQty5 Decimal(28,10), FCUUnitQty5 Decimal(28,10), FAmount5 Decimal(28,10), FQty6 Decimal(28,10), FCUUnitQty6 Decimal(28,10), FAmount6 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10)) Create Table #Happen( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FQty4 Decimal(28,10), FCUUnitQty4 Decimal(28,10), FAmount4 Decimal(28,10), FQty5 Decimal(28,10), FCUUnitQty5 Decimal(28,10), FAmount5 Decimal(28,10), FQty6 Decimal(28,10), FCUUnitQty6 Decimal(28,10), FAmount6 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10)) Create Table #Happen1( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FQty4 Decimal(28,10), FCUUnitQty4 Decimal(28,10), FQty5 Decimal(28,10), FCUUnitQty5 Decimal(28,10), FQty6 Decimal(28,10), FCUUnitQty6 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) ) Insert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty1, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty1, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty2, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty2, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty3, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty3, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty4, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty4, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty5, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty5, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty6, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient As FCUUnitQty6, 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 join="" icstockbillentry="" t6="" on="" t5="" finterid="t6.FInterID" left="" join="" t_measureunit="" t7="" on="" t1="" fstoreunitid="t7.FMeasureUnitID" left="" join="" t_stock="" t2="" on="" t2="" fitemid="(case" when="" t5="" ftrantype="24" then="" t6="" fscstockid="" else="" t6="" fdcstockid="" end="" where="" t1="" fitemid="t6.FItemID" and="" t5="" ftrantype="" in="" 1="" 2="" 5="" 10="" 40="" and="" t5="" frob="1)" or="" t5="" ftrantype="" in="" 21="" 24="" 28="" 29="" and="" t5="" frob="-1))" and="" not="" t5="" ftrantype="" in="" 1="" and="" t5="" fpomode="36681" and="" not="" t5="" ftrantype="1" and="" t6="" fsourceinterid=""> 0 and EXISTS(SELECT 1 FROM ICHookRelations t8 where t6.FinterID=t8.fIBInterID and t8.FIBTag=4 ))) And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件 AND t1.FDeleted=0 Insert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty1, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty1, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty2, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty2, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty3, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty3, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty4, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty4, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty5, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty5, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t6.FBegQty Else 0 End) As FQty6, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty6, 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join ICInvInitial t6 On t1.FItemID = t6.FItemID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID Left Join t_Stock t2 On t2.FItemID = t6.FStockID Where 1=1 And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件 AND t1.FDeleted=0 Insert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,0,0,0,0,0,0,0,0,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t3.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join ICINVENTORY t3 On t1.FItemID = t3.FItemID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID Left Join t_Stock t2 On t2.FItemID = t3.FStockID Where 1=1 And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件 AND t1.FDeleted=0 Insert Into #HAPPEN2 Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY, Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,0,Sum(fqty4) As FQty4 ,Sum(fCUUnitqty4) As FCUUnitQty4 ,0,Sum(fqty5) As FQty5 ,Sum(fCUUnitqty5) As FCUUnitQty5 ,0,Sum(fqty6) As FQty6 ,Sum(fCUUnitqty6) As FCUUnitQty6 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY) From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID GROUP By t1.FITEMID,t1.FBatchNo Update #Happen2 Set FQty1= FQty,FQty2=0 ,FQty3=0 ,FQty4=0 ,FQty5=0 ,FQty6=0 Where FQty-FQty1<0 Update #Happen2 Set FQty2= FQty-FQty1,FQty3=0 ,FQty4=0 ,FQty5=0 ,FQty6=0 Where FQty-FQty1-FQty2<0 Update #Happen2 Set FQty3= FQty-FQty1-FQty2,FQty4=0 ,FQty5=0 ,FQty6=0 Where FQty-FQty1-FQty2-FQty3<0 Update #Happen2 Set FQty4= FQty-FQty1-FQty2-FQty3,FQty5=0 ,FQty6=0 Where FQty-FQty1-FQty2-FQty3-FQty4<0 Update #Happen2 Set FQty5= FQty-FQty1-FQty2-FQty3-FQty4,FQty6=0 Where FQty-FQty1-FQty2-FQty3-FQty4-FQty5<0 Update #Happen2 Set FQty6= FQty-FQty1-FQty2-FQty3-FQty4-FQty5 Update #Happen2 Set FAmount1=FPrice*FQty1 Update #Happen2 Set FAmount2=FPrice*FQty2 Update #Happen2 Set FAmount3=FPrice*FQty3 Update #Happen2 Set FAmount4=FPrice*FQty4 Update #Happen2 Set FAmount5=FPrice*FQty5 Update #Happen2 Set FAmount6=FPrice*FQty6 Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0 ,FCUUnitQty3=0 ,FCUUnitQty4=0 ,FCUUnitQty5=0 ,FCUUnitQty6=0 Where FCUUnitQty-FCUUnitQty1<0 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1 ,FCUUnitQty3=0 ,FCUUnitQty4=0 ,FCUUnitQty5=0 ,FCUUnitQty6=0 Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2<0 Update #Happen2 Set FCUUnitQty3= FCUUnitQty-FCUUnitQty1 -FCUUnitQty2 ,FCUUnitQty4=0 ,FCUUnitQty5=0 ,FCUUnitQty6=0 Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3<0 Update #Happen2 Set FCUUnitQty4= FCUUnitQty-FCUUnitQty1 -FCUUnitQty2 -FCUUnitQty3 ,FCUUnitQty5=0 ,FCUUnitQty6=0 Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4<0 Update #Happen2 Set FCUUnitQty5= FCUUnitQty-FCUUnitQty1 -FCUUnitQty2 -FCUUnitQty3 -FCUUnitQty4 ,FCUUnitQty6=0 Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4-FCUUnitQty5<0 update="" happen2="" set="" fcuunitqty6="FCUUnitQty-FCUUnitQty1" -fcuunitqty2="" -fcuunitqty3="" -fcuunitqty4="" -fcuunitqty5="" insert="" into="" happen="" select="" t1="" fitemid="" 0="" t1="" fbatchno="" sum="" fqty="" as="" fqty="" sum="" fcuunitqty="" as="" fcuunitqty="" sum="" fqty1="" as="" fqty1="" sum="" fcuunitqty1="" as="" fcuunitqty1="" sum="" famount1="" as="" famount1="" sum="" fqty2="" as="" fqty2="" sum="" fcuunitqty2="" as="" fcuunitqty2="" sum="" famount2="" as="" famount2="" sum="" fqty3="" as="" fqty3="" sum="" fcuunitqty3="" as="" fcuunitqty3="" sum="" famount3="" as="" famount3="" sum="" fqty4="" as="" fqty4="" sum="" fcuunitqty4="" as="" fcuunitqty4="" sum="" famount4="" as="" famount4="" sum="" fqty5="" as="" fqty5="" sum="" fcuunitqty5="" as="" fcuunitqty5="" sum="" famount5="" as="" famount5="" sum="" fqty6="" as="" fqty6="" sum="" fcuunitqty6="" as="" fcuunitqty6="" sum="" famount6="" as="" famount6="" 1="" min="" fprice="" case="" sum="" fcuunitqty="" when="" 0="" then="" 0="" else="" min="" fprice="" sum="" fqty="" sum="" fcuunitqty="" end="" min="" fprice="" sum="" fqty="" from="" happen2="" t1="" inner="" join="" t_icitem="" t2="" on="" t1="" fitemid="t2.FItemID" group="" by="" t1="" fitemid="" t1="" fbatchno="" having="" sum="" fqty="">=0) CREATE TABLE #DATA( FNumber Varchar(355) null, FShortNumber Varchar(355) null, FName Varchar(355) null, FModel Varchar(355) null, FUnitName Varchar(355) null, FCUUnitName Varchar(355) null, FQtyDecimal smallint null, FPriceDecimal smallint null, FQty Decimal(28,10) Null, FCUUnitQty Decimal(28,10) Null, FPrice Decimal(28,10) NULL, FCUPrice Decimal(28,10) Null, FAmount Decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FQty4 Decimal(28,10), FCUUnitQty4 Decimal(28,10), FAmount4 Decimal(28,10), FQty5 Decimal(28,10), FCUUnitQty5 Decimal(28,10), FAmount5 Decimal(28,10), FQty6 Decimal(28,10), FCUUnitQty6 Decimal(28,10), FAmount6 Decimal(28,10), FSumSort smallint not null Default(0), Flevel0 Decimal(10,3), Flevel1 Decimal(10,3), Flevel2 Decimal(10,3), Flevel3 Decimal(10,3), Flevel4 Decimal(10,3), Flevel5 Decimal(10,3), Flevel6 Decimal(10,3), FID int IDENTITY) INSERT INTO #DATA ( FNumber,FShortNumber,FName,FModel,FUnitName,FCUUnitName, FQtyDecimal,FPriceDecimal,FQty,FCUUnitQty,FPrice,FCUPrice,FAmount,FQty1, FCUUnitQty1, FAmount1, FQty2, FCUUnitQty2, FAmount2, FQty3, FCUUnitQty3, FAmount3, FQty4, FCUUnitQty4, FAmount4, FQty5, FCUUnitQty5, FAmount5, FQty6, FCUUnitQty6, FAmount6, FSumSort) SELECT CASE WHEN GROUPING(t1.FNumber)=1 THEN '合计' ELSE t1.FNumber END, '','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1), SUM(FCUUnitQty1), SUM(FAmount1), SUM(FQty2), SUM(FCUUnitQty2), SUM(FAmount2), SUM(FQty3), SUM(FCUUnitQty3), SUM(FAmount3), SUM(FQty4), SUM(FCUUnitQty4), SUM(FAmount4), SUM(FQty5), SUM(FCUUnitQty5), SUM(FAmount5), SUM(FQty6), SUM(FCUUnitQty6), SUM(FAmount6), CASE WHEN GROUPING(t1.FNumber)=1 THEN 101 ELSE 0 END FROM #Happen v2 Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID Left Join t_Stock t2 On v2.FStockID=t2.FItemID Where 1=1 Group by t1.FNumber WITH ROLLUP Having Sum(FQty)>0 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4 Where t1.FNumber=t2.FNumber And t2.FUnitGroupID=t3.FUnitGroupID And t2.FStoreUnitID=t4.FMeasureUnitID And t3.FStandard=1 SELECT FNumber 物料编码,FName 物料名称,FUnitName 基本计量单位,FCUUnitName 库存单位,FQty 库存数量,FPrice 单价,FAmount 金额, Fqty1 '0-29天数量',fAmount1 '0-29天金额',Fqty2 '30-59天数量',fAmount2 '30-59天金额',Fqty3 '60-89天数量',fAmount3 '60-89天金额',Fqty4 '90-179天数量',fAmount4 '90-179天金额',Fqty5 '180-359天数量',fAmount5 '180-359天金额',Fqty6 '360天以上数量',fAmount6 '360天以上金额' FROM #DATA DROP TABLE #DATA Drop Table #Happen Drop Table #Happen1 Drop Table #Happen2 提示列名 'FPOMode' 无效。
07-31
注意：在插入数据后添加列，那么新添加的列在插入的数据中都是NULL（除非有默认值），然后更新部分行的值。 总结步骤： 1. 确认错误发生的具体位置（哪一行代码）。 2. 检查该行代码中引用的列名和表名。 3. ...
Varchar数据类型
05-17
SELECT * FROM BadOrders WHERE CONVERT(DATE,OrderDateString) >= '2023-01-01'; ``` #### MySQL 特殊情况 值得注意的是，在不同的 RDBMS 平台间可能存在细微差别。比如 MySQL 支持两种 row_format 设置影响如何...
– 定义动态日期范围 DECLARE @EndDate DATE = GETDATE(); DECLARE @StartDate DATE = DATEADD(DAY, -365, @EndDate); – 创建部门临时表 IF OBJECT_ID(‘tempdb…#TEMP_Department’) IS NOT NULL DROP TABLE #TEMP_Department; CREATE TABLE #TEMP_Department (Department nvarchar(20)); INSERT INTO #TEMP_Department EXEC [ZY_P_Get_SAP_Account_to_TempDept]; – 基础销售额计算 IF OBJECT_ID(‘tempdb…#Sales’) IS NOT NULL DROP TABLE #Sales; SELECT T.客户名称, SUM(T.RMB总价) AS 销售额 INTO #Sales FROM [ZY_TB_SALES_Details] T INNER JOIN #TEMP_Department D ON T.综合部门 = D.Department WHERE T.交货日期 BETWEEN @StartDate AND @EndDate AND T.业务体系 = ‘经销体系’ GROUP BY T.客户名称; – 1. 计算总毛利（非项目+项目） IF OBJECT_ID(‘tempdb…#NonProjectProfit’) IS NOT NULL DROP TABLE #NonProjectProfit; SELECT T.客户名称, SUM(T.扣技服后毛利) AS 非项目毛利 INTO #NonProjectProfit FROM [ZY_TB_SALES_Details] T INNER JOIN #TEMP_Department D ON T.综合部门 = D.Department WHERE T.交货日期 BETWEEN @StartDate AND @EndDate AND T.业务体系 = ‘经销体系’ AND T.项目合同YN IS NULL GROUP BY T.客户名称; IF OBJECT_ID(‘tempdb…#ProjectProfit’) IS NOT NULL DROP TABLE #ProjectProfit; SELECT 客户名称, SUM(有效综合毛利RMB) AS 项目毛利 INTO #ProjectProfit FROM ZY_TB_JX_XM_SalesPer_Profit WHERE 交货日期 BETWEEN @StartDate AND @EndDate AND 综合部门 IN (SELECT Department FROM #TEMP_Department) GROUP BY 客户名称; IF OBJECT_ID(‘tempdb…#ProfitScore’) IS NOT NULL DROP TABLE #ProfitScore; SELECT COALESCE(N.客户名称, P.客户名称) AS 客户名称, ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) AS 总毛利, CASE WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 1000000 THEN 100 WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 500000 THEN 80 WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 100000 THEN 60 WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 10000 THEN 40 WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) > 0 THEN 20 ELSE 0 END * 0.3 AS 毛利得分 INTO #ProfitScore FROM #NonProjectProfit N FULL OUTER JOIN #ProjectProfit P ON N.客户名称 = P.客户名称; – 2. 计算预收款 IF OBJECT_ID(‘tempdb…#Advance’) IS NOT NULL DROP TABLE #Advance; WITH CTE_OACT AS (SELECT AcctCode, AcctName FROM OACT), CTE_BANK AS ( SELECT DISTINCT Number AS ‘进账单流水号’, BankName AS ‘银行信息’ FROM [WEB_BANKINCOME] UNION ALL SELECT 到帐单流水号 AS ‘进账单流水号’, 银行信息 FROM [ZY_TB_WEB_BANKINCOME_OLD] ), CTE_Result AS ( SELECT T0.CardName AS N’客户名称’, T0.OpenBal AS N’未清金额’, T0.Canceled FROM ORCT T0 INNER JOIN CTE_OACT t3 ON t0.TrsfrAcct = t3.AcctCode LEFT JOIN (SELECT DISTINCT 进账单流水号, 银行信息 FROM CTE_BANK) M0 ON M0.进账单流水号 = T0.U_LSH LEFT JOIN [ZY_VIEW_XM] X0 ON X0.ConCode = T0.U_contractnumber LEFT JOIN OCRD d1 ON d1.CardCode = t0.CardCode WHERE T0.taxdate<= @EndDate AND T0.U_Department IN (‘上海办’,‘生命科学部’,‘分销部’,‘工业部’,‘天津办’,‘基础科研部’,‘沈阳办’,‘烟台办’,‘广州办’,‘成都办’,‘商务部’) ) SELECT [客户名称], SUM(CASE WHEN Canceled = ‘N’ THEN [未清金额] ELSE 0 END) AS 预收款金额 INTO #Advance FROM CTE_Result GROUP BY [客户名称]; – 3. 计算应收款和超期应收款 IF OBJECT_ID(‘tempdb…#OverdueBase’) IS NOT NULL DROP TABLE #OverdueBase; SELECT T.客户名称, T.未收款金额, ISNULL(X.XM_YN, ‘N’) AS 项目合同, T.DN日期 AS 销货日期, T.业务体系 INTO #OverdueBase FROM [ZY_TB_Receivable_Balance_Base] T INNER JOIN #TEMP_Department X0 ON X0.Department = T.综合部门 LEFT JOIN [ZY_VIEW_XM] X ON X.ConCode = T.合同号 WHERE T.业务体系 = ‘经销体系’; – 完整应收款 (用于展示) IF OBJECT_ID(‘tempdb…#TotalReceivable’) IS NOT NULL DROP TABLE #TotalReceivable; SELECT 客户名称, SUM(未收款金额) AS 应收款总额 INTO #TotalReceivable FROM #OverdueBase WHERE 项目合同 = ‘N’ GROUP BY 客户名称; – 超期应收款 (用于得分计算) IF OBJECT_ID(‘tempdb…#Overdue’) IS NOT NULL DROP TABLE #Overdue; SELECT 客户名称, SUM(未收款金额) AS 超期应收款 INTO #Overdue FROM #OverdueBase WHERE 项目合同 = ‘N’ AND DATEDIFF(DAY, 销货日期, @EndDate) >= 180 GROUP BY 客户名称; – 4. 计算库存 – 完整库存 (用于展示) IF OBJECT_ID(‘tempdb…#TotalInventory’) IS NOT NULL DROP TABLE #TotalInventory; SELECT [客户名称], SUM([含税总价RMB]) AS 库存总额 INTO #TotalInventory FROM ( SELECT R0.CardName AS ‘客户名称’, CASE WHEN A0.U_CustomsRate IS NOT NULL THEN CASE WHEN A0.U_Import =‘Y’ AND A0.U_RevisedDuty IS NOT NULL THEN (A0.U_ForeignPrice * A0.U_CustomsRate + ISNULL(A0.U_RevisedDuty,0)) * (A0.U_InputVATRate/100+1) WHEN A0.U_Import =‘Y’ AND A0.U_RevisedDuty IS NULL THEN (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) * (A0.U_InputVATRate/100+1) ELSE (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) END * B0.Quantity ELSE CASE WHEN A0.U_Import =‘Y’ THEN (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) * (A0.U_InputVATRate/100+1) ELSE (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) END * B0.Quantity END AS ‘含税总价RMB’ FROM OBTN A0 JOIN OIBT B0 ON B0.ItemCode = A0.ItemCode AND B0.BatchNum = A0.DistNumber AND B0.SysNumber = A0.SysNumber LEFT JOIN ORDR R0 ON R0.SupplCode = A0.U_SaleContNo WHERE B0.Quantity > 0 AND R0.CardName IS NOT NULL ) AS SubQuery GROUP BY [客户名称]; – 5. 获取客户主数据 IF OBJECT_ID(‘tempdb…#CustomerMaster’) IS NOT NULL DROP TABLE #CustomerMaster; SELECT CardCode AS 客户编号, CardName AS 客户名称, CASE WHEN UPPER(ISNULL(cntctprsn, ‘’)) = ‘VIP’ THEN ‘VIP’ ELSE ‘’ END AS 客户类型 INTO #CustomerMaster FROM OCRD WHERE Cardtype = ‘C’; – 6. 创建主客户表 IF OBJECT_ID(‘tempdb…#MasterClient’) IS NOT NULL DROP TABLE #MasterClient; SELECT CM.客户编号, CM.客户类型, CM.客户名称, ISNULL(P.总毛利, 0) AS 近期毛利, ISNULL(P.毛利得分, 0) AS 毛利得分, ISNULL(A.预收款金额, 0) AS 预收款金额, ISNULL(R.应收款总额, 0) AS 应收款总额, ISNULL(TI.库存总额, 0) AS 库存总额, ISNULL(O.超期应收款, 0) AS 超期应收款 INTO #MasterClient FROM #CustomerMaster CM LEFT JOIN #ProfitScore P ON CM.客户名称 = P.客户名称 LEFT JOIN #Advance A ON CM.客户名称 = A.客户名称 LEFT JOIN #TotalReceivable R ON CM.客户名称 = R.客户名称 LEFT JOIN #TotalInventory TI ON CM.客户名称 = TI.客户名称 LEFT JOIN #Overdue O ON CM.客户名称 = O.客户名称; – 7. 计算得分 (修复列不明确问题) IF OBJECT_ID(‘tempdb…#Scores’) IS NOT NULL DROP TABLE #Scores; SELECT MC.客户名称, – 明确指定使用#MasterClient表的客户名称 – 预收款得分 (ISNULL(预收款占比得分, 0) + ISNULL(预收款金额得分, 0)) / 2 * 0.2 AS 预收款得分, – 超期应收款得分 (ISNULL(超期占比得分, 100) + ISNULL(超期金额得分, 100)) / 2 * 0.3 AS 超期得分, – 库存得分 CASE WHEN ISNULL(库存占比得分, 100) < ISNULL(库存金额得分, 100) THEN ISNULL(库存占比得分, 100) ELSE ISNULL(库存金额得分, 100) END * 0.2 AS 库存得分 INTO #Scores FROM #MasterClient MC LEFT JOIN #Sales S ON MC.客户名称 = S.客户名称 CROSS APPLY ( SELECT – 预收款得分计算 CASE WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 1 THEN 100 WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 0.5 THEN 75 WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50 WHEN 预收款金额 > 0 THEN 25 ELSE 0 END AS 预收款占比得分, CASE WHEN 预收款金额 >= 1000000 THEN 100 WHEN 预收款金额 >= 500000 THEN 75 WHEN 预收款金额 >= 100000 THEN 50 WHEN 预收款金额 > 0 THEN 25 ELSE 0 END AS 预收款金额得分, – 超期应收款得分计算 CASE WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 1 THEN 0 WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 0.5 THEN 25 WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 0.1 THEN 50 WHEN 超期应收款 > 0 THEN 75 ELSE 100 END AS 超期占比得分, CASE WHEN 超期应收款 >= 1000000 THEN 0 WHEN 超期应收款 >= 500000 THEN 25 WHEN 超期应收款 >= 100000 THEN 50 WHEN 超期应收款 > 0 THEN 75 ELSE 100 END AS 超期金额得分, – 库存得分计算 CASE WHEN 库存总额 / NULLIF(S.销售额, 0) >= 1 THEN 0 WHEN 库存总额 / NULLIF(S.销售额, 0) >= 0.5 THEN 25 WHEN 库存总额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50 WHEN 库存总额 > 0 THEN 75 ELSE 100 END AS 库存占比得分, CASE WHEN 库存总额 >= 1000000 THEN 0 WHEN 库存总额 >= 500000 THEN 25 WHEN 库存总额 >= 100000 THEN 50 WHEN 库存总额 > 0 THEN 75 ELSE 100 END AS 库存金额得分 ) AS Scores; – 8. 汇总最终结果 SELECT MC.客户编号, MC.客户类型, MC.客户名称, ROUND( ISNULL(MC.毛利得分, 0) + ISNULL(S.预收款得分, 0) + ISNULL(S.超期得分, 0) + ISNULL(S.库存得分, 0), 2) AS 用户评级, MC.应收款总额 AS 应收款, MC.预收款金额 AS 预收款, MC.库存总额 AS 专项库存, MC.近期毛利 AS 近期毛利, ‘’ AS 备注2 FROM #MasterClient MC LEFT JOIN #Scores S ON MC.客户名称 = S.客户名称 ORDER BY 用户评级 DESC; – 清理所有临时表 DROP TABLE #Sales, #NonProjectProfit, #ProjectProfit, #ProfitScore, #Advance, #TEMP_Department, #OverdueBase, #Overdue, #CustomerMaster, #MasterClient, #TotalReceivable, #TotalInventory, #Scores; 这个是我目前的代码，现在需要将这个结果利用fdl推到ip:192.168.3.1 3306 id=lpsoft password=@Aa.1234 database=OrderManage 他的T_Customer表中。具体的字段如下 序号 显示字段 表名 字段名 类型 数据类型 必填 默认值 说明 1 主键Id T_Customer Id 整数数据类型 bigint 是 无 15位的不重复随机整数 1 SAP客户编号 T_Customer S6 文本 varchar(50) 否 无 2 客户名称 T_Customer Name 文本 varchar(50) 否 无 3 电话 T_Customer Phone 文本 varchar(15) 否 无 4 电话2 T_Customer Phone2 文本 varchar(15) 否 无 5 客户类型 T_Customer CustomerTypeCode 下拉：普通/VIP varchar(20) 否 普通=703192237846597 VIP=703192237850693 6 用户评级 T_Customer S5 文本 varchar(50) 否 无 7 应收款 T_Customer S1 文本 varchar(50) 否 无 8 预收款 T_Customer S2 文本 varchar(50) 否 无 9 专项库存 T_Customer S3 文本 varchar(50) 否 无 10 近期毛利 T_Customer S4 文本 varchar(50) 否 无 11 备注 T_Customer Remark 长文本 text 否 无 帮我写个代码完成推送，id例如20250731103042（时间部分） + 7（随机数） → 202507311030427 帮我形成完整的代码
最新发布
08-01
- 生成14位当前时间字符串（如：CONVERT(VARCHAR(14), GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') -> 这样得到yyyyMMddHHmmss） - 然后为每一行生成一个0-9的随机数：CAST(ABS(CAST...
Select * From master.dbo.sysdatabases 查询本数据库信息
milijiangjun的博客
06-27 3204
转载自：https://blog.youkuaiyun.com/Aug0st/article/details/42241255Sysobjects：SQL-SERVER的每个数据库内都有此系统表，它存放该数据库内创建的 所有对象，如约束、默认值、日志、规则、存储过程等，每个对象在表中占一行。 对象类型(xtype)。可以是下列对象类型中的一种： C = CHECK 约束 D = 默认值或 DEFAULT 约束...
不允许从数据类型 ntext 到 varchar 的隐性转换。请使用 CONVERT 函数来运行此查询
haoxiang110的专栏
10-25 7507
<br />由于数据库中的[dbo].[proc_Info_AddMail]存储过程中的@body_file varchar(8000) ='',变量声名为varchar(8000)，当报告数据超过37篇左右时（我测试的时候用了37篇，具体多少，也不太清楚），这个变量就会被超出范围，所以报出这个异常， <br />解决方法： <br />将这个变量改成text类型，问题解决。
sql 时间转换格式 convert(varchar(10),字段名,转换格式)
热门推荐
jimlong的专栏
03-12 8万+
convert(varchar(10),字段名,转换格式) CONVERT(nvarchar(10),count_time,121) CONVERT为日期转换函数，一般就是在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar) 相互转换的时候才用到的函数的3个参数，第1个参数为，转换后的大小，第2个为转换日期的字段或函
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值