SQLserver 积累:数值 + null = null, int 类型转 varchar 类型 , 日期类型转 varchar 类型

玫城

于 2023-01-30 08:31:20 发布

阅读量968

点赞数

分类专栏：数据库文章标签： sqlserver 数据库 java

本文链接：https://blog.csdn.net/weixin_70506521/article/details/128796443

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

在SQLserver中处理数据类型不匹配处理:

1.处理 int 等其他数值类型 + null = null 的问题

将null 转为 0 进行再进行 + 运算

2.处理 int 或日期等类型数据不能与 varchar 类型比较处理的问题

将 int 或日期等类型数据转换成 varchar 类型数据再进行数据处理

以一个SQLserver存储过程为例:

USE [AIS20200118104248]
GO
/****** Object:  StoredProcedure [dbo].[wsd_khywytjb]    Script Date: 2023/1/30 8:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--客户 业务员实际应收统计表
ALTER PROCEDURE [dbo].[wsd_khywytjb]
--查询条件
@startDate datetime,   --开始日期
@endDate datetime,     --结束日期
@cName varchar(50),    --业务员
@cOrg varchar(50)      --客户

AS
BEGIN
select b.FNumber 业务员编码, b.FName 业务员,c.FNumber 客户编码, c.FName 客户,
  sum(m.FAmount) 合同金额,
  sum(m.FAmount2) 实际金额,
  sum(m.FAmount5) 回款金额, 
  sum(m.FAmount3) 应付安装费金额, 
  sum(m.FAmount4) 已付安装费金额, 
  sum(m.FAmount3 - m.FAmount4) 未付安装费金额,
  sum(m.FAmount9) 应付调试费金额,
  sum(m.FAmount10) 已付调试费金额,
  sum(m.FAmount9 - m.FAmount10 ) 未付调试费金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount3 - m.FAmount4 + m.FAmount9 - m.FAmount10) 应收金额, 
  isnull(t.FAmount, 0) 初始金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount3 - m.FAmount4 + m.FAmount9 - m.FAmount10) - isnull(t.FAmount, 0) 实际应收
from wsd_hkdjtz a
		 left join t_Item c on a.FBase2=c.FItemID
		 left join t_Item b on a.FBase=b.FItemID
		 left join wsd_hkdjtz_mx m on a.FID=m.FID
		 left join t_BOS200000020 t on b.FNumber =  CONVERT(varchar(50),t.FBase) and c.FNumber = CONVERT(varchar(50),t.FBase1)
where(a.FDate1>=CONVERT(varchar(50),@startDate,23) or CONVERT(varchar(50),@startDate,23)='' or ISNULL(CONVERT(varchar(50),@startDate,23),'1')='1') 
  and (a.FDate1<=CONVERT(varchar(50),@endDate,23) or CONVERT(varchar(50),@endDate,23)='' or ISNULL(CONVERT(varchar(50),@endDate,23),'1')='1')
  and (b.FName like ('%'+ @cName + '%') or @cName='' or ISNULL(@cName,'1')='1')
  and (c.FName like ('%'+ @cOrg + '%') or @cOrg='' or ISNULL(@cOrg,'1')='1')
group by b.FNumber,b.FName, c.FNumber,c.FName, t.FAmount

union all 
  select '合计','','','',
  sum(m.FAmount) 合同金额,
  sum(m.FAmount2) 实际金额,
  sum(m.FAmount5) 回款金额, 
  sum(m.FAmount3) 应付安装费金额, 
  sum(m.FAmount4) 已付安装费金额, 
  sum(m.FAmount3 - m.FAmount4) 未付安装费金额,
  sum(m.FAmount9) 应付调试费金额,
  sum(m.FAmount10) 已付调试费金额,
  sum(m.FAmount9 - m.FAmount10 ) 未付调试费金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount3 - m.FAmount4 + m.FAmount9 - m.FAmount10 ) 应收金额, 
  sum(isnull(t.FAmount, 0)) 初始金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount3 - m.FAmount4 + m.FAmount9 - m.FAmount10) - sum(isnull(t.FAmount, 0)) 实际应收
  from wsd_hkdjtz a
		 left join t_Item c on a.FBase2=c.FItemID
		 left join t_Item b on a.FBase=b.FItemID
		 left join wsd_hkdjtz_mx m on a.FID=m.FID
		 left join t_BOS200000020 t on b.FNumber =  CONVERT(varchar(50),t.FBase) and c.FNumber = CONVERT(varchar(50),t.FBase1)
	where(a.FDate1>=CONVERT(varchar(50),@startDate,23) or CONVERT(varchar(50),@startDate,23)='' or ISNULL(CONVERT(varchar(50),@startDate,23),'1')='1') 
		and (a.FDate1<=CONVERT(varchar(50),@endDate,23) or CONVERT(varchar(50),@endDate,23)='' or ISNULL(CONVERT(varchar(50),@endDate,23),'1')='1')
		and (b.FName like ('%'+ @cName + '%') or @cName='' or ISNULL(@cName,'1')='1')
		and (c.FName like ('%'+ @cOrg + '%') or @cOrg='' or ISNULL(@cOrg,'1')='1')
END