数据库问题集合

1：Id这个字段是int型，而我现在要删除这个字符串所提供的条件 _Id="34,35,";

解决办法：刚开始的时候还为怎么把中间带有，的字符串转化为int型而烦恼，后来发现其实完全不用多想，直接放进去，系统会辨别的。

只是要注意下面这两个("和“）不要写成('"和"')，多了这个',结果就会成为Id in ('34,35') 从而报错

 "delete from Notice_List where Id in (" + Id.Substring(0,_Id.Length-1) + ")";

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2：Date =' 2013-04-01' 数据库里明明有这个日期，但是就是搜不到。

后来到群里问了下，找到问题。

是因为我原来的格式后面是时间的，而且不是00:00:00,所以找不到。

两个解决办法。

1 如果需要时间的，改成00:00:00就成 2 如果只是要日期 那么清空字段重新获取就好。

看来我在解决问题的时候脑子还是转的不快，这类的问题还是要了解基础的。

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3：datetime字段可以用Tostring直接操作，不用转换。

如：select * from InfoReport_1 where date = '"+Date.Tostring("yyyy-MM-dd")+"'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4:从excel导入数据

INSERT INTO [aa].[dbo].[WoodManageApprove ]()

select from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=F:\文件名.xls',标签名$)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5：去除一个字段中的某个文字

replace(replace(b.TownName,'镇',''),'乡',''

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6：数字转成大写汉字货币的函数(0以上不含小数)

 

CREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int) 

RETURNS VARCHAR(200) AS 

BEGIN 

Declare @v_LowerStr VARCHAR(200) -- 小写金额 

Declare @v_UpperPart VARCHAR(200) 

Declare @v_UpperStr VARCHAR(200) -- 大写金额

Declare @i_I int

set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格

set @i_I = 1

set @v_UpperStr = ''

while ( @i_I <= len(@v_LowerStr))

begin

select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)

WHEN '.' THEN '元'

WHEN '0' THEN ''

WHEN '1' THEN '壹'

WHEN '2' THEN '贰'

WHEN '3' THEN '叁'

WHEN '4' THEN '肆'

WHEN '5' THEN '伍'

WHEN '6' THEN '陆'

WHEN '7' THEN '柒'

WHEN '8' THEN '捌'

WHEN '9' THEN '玖'

END

+ 

case @i_I

WHEN 5 THEN '拾'

WHEN 6 THEN '佰'

WHEN 7 THEN '仟'

WHEN 8 THEN '万'

WHEN 9 THEN '拾'

WHEN 10 THEN '佰'

WHEN 11 THEN '仟'

WHEN 12 THEN '亿'

WHEN 13 THEN '拾'

WHEN 14 THEN '佰'

WHEN 15 THEN '仟'

WHEN 16 THEN '万'

ELSE ''

END

set @v_UpperStr = @v_UpperPart + @v_UpperStr

set @i_I = @i_I + 1

end

if ( 0 = @v_TransType)

begin

set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','拾') 

set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','佰') 

set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','仟') 

set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')

set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')

set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')

set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')

set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')

set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')

set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')

set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')

end

return @v_UpperStr + '整'

END

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------