select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%'这里面已经把数字都筛选出来了。select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 查询列这里没有问题,但是把convert(float ,sswx.Zhi1)放条件里面就报错了。也就是说: select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%' )as sswx 这样没有问题,但是 select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%' )as sswx where convert(float,sswx.Zhi1)>111 这样就有问题了
试试这个: select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%' )as sswx where ISNUMERIC(sswx.Zhi1) = 1 and convert(float,sswx.Zhi1)>111
试试这个: select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%' )as sswx where ISNUMERIC(sswx.Zhi1) = 1 and convert(float,sswx.Zhi1)>111
加到 where convert(float,sswx.Zhi1)>111 这个里再加了值筛选数字的,但还是一样。
这样呢?SELECT sswx.paihaoid , sswx.name1 , sswx.zhi1 , CONVERT(FLOAT, sswx.Zhi1) AS tt , sswx.name2 , sswx.zhi2 FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID , sswx1.Name AS Name1 , sswx1.Zhi AS Zhi1 , CONVERT(FLOAT, sswx1.Zhi) AS dd , sswx2.Name AS Name2 , sswx2.Zhi AS Zhi2 FROM ( SELECT * FROM SuShang_WX_PaiHaoAttribute WHERE ISNUMERIC(Zhi1) = 1 ) AS sswx1 LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID WHERE LEN(sswx1.zhi) > 0 AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' AND sswx1.Name LIKE '%密度%' AND sswx2.Name LIKE '%弹性模量%' ) AS sswx WHERE CONVERT(FLOAT, sswx.Zhi1) > 111
这样呢: select * from ( select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 from ( select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 from SuShang_WX_PaiHaoAttribute as sswx1 left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID where LEN(sswx1.zhi) > 0 and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' and sswx1.Name like '%密度%' and sswx2.Name like '%弹性模量%' )as sswx where ISNUMERIC(sswx.Zhi1) = 1 )t where convert(float,Zhi1)>111
先看看这个报不报错SELECT sswx.paihaoid , sswx.name1 , sswx.zhi1 , sswx.Zhi1,-- CONVERT(FLOAT, sswx.Zhi1) AS tt , sswx.name2 , sswx.zhi2 FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID , sswx1.Name AS Name1 , sswx1.Zhi AS Zhi1 , CONVERT(FLOAT, sswx1.Zhi) AS dd , sswx2.Name AS Name2 , sswx2.Zhi AS Zhi2 FROM ( SELECT * FROM SuShang_WX_PaiHaoAttribute WHERE ISNUMERIC(Zhi1) = 1 ) AS sswx1 LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID WHERE LEN(sswx1.zhi) > 0 AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' AND sswx1.Name LIKE '%密度%' AND sswx2.Name LIKE '%弹性模量%' ) AS sswx WHERE CONVERT(FLOAT, sswx.Zhi1) > 111
CONVERT(FLOAT, sswx.Zhi1) AS tt 这个是我特意加上去的,就是测试,加上这个没有问题。
这样呢?SELECT sswx.paihaoid , sswx.name1 , sswx.zhi1 , sswx.Zhi1,-- CONVERT(FLOAT, sswx.Zhi1) AS tt , sswx.name2 , sswx.zhi2 FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID , sswx1.Name AS Name1 , CONVERT(FLOAT, sswx.Zhi1) --sswx1.Zhi AS Zhi1 , CONVERT(FLOAT, sswx1.Zhi) AS dd , sswx2.Name AS Name2 , sswx2.Zhi AS Zhi2 FROM ( SELECT * FROM SuShang_WX_PaiHaoAttribute WHERE ISNUMERIC(Zhi1) = 1 ) AS sswx1 LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID WHERE LEN(sswx1.zhi) > 0 AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' AND sswx1.Name LIKE '%密度%' AND sswx2.Name LIKE '%弹性模量%' ) AS sswx WHERE sswx.Zhi1> 111
试了你的方法,现在是报这个sswx.Zhi1>111 的问题,报一样的错误
能看到有没有异常的值?SELECT distinct sswx.zhi1 FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID , sswx1.Name AS Name1 , CONVERT(FLOAT, sswx.Zhi1) --sswx1.Zhi AS Zhi1 , CONVERT(FLOAT, sswx1.Zhi) AS dd , sswx2.Name AS Name2 , sswx2.Zhi AS Zhi2 FROM ( SELECT * FROM SuShang_WX_PaiHaoAttribute WHERE ISNUMERIC(Zhi1) = 1 ) AS sswx1 LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID WHERE LEN(sswx1.zhi) > 0 AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' AND sswx1.Name LIKE '%密度%' AND sswx2.Name LIKE '%弹性模量%' ) AS sswx --WHERE sswx.Zhi1> 111
或者你直接select CONVERT(FLOAT, sswx.Zhi1) from SuShang_WX_PaiHaoAttribute 看看到哪里报错
没问题,出来都是正常的值,其实我觉得里面 CONVERT(FLOAT, sswx.Zhi1) AS Zhi1 已经成功执行了,应该没什么问题,但是出来外面再一次筛选,就报错了
发现了问题的所在,但是不知道怎么解决 select * from ( select top 10000000 *,convert(float,zhi) as convertzhi from WX_PaiHaoAttribute where name like '%密度%' and LEN(zhi) > 0 and zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%' and zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%' and zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' ) as tmp_tb where convert(float,convertzhi)>1 加上top,数据是没有10000000 这么多的 把top去掉,就报 从数据类型 varchar 转换为 float 时出错。
declare @t varchar(100)set @t = '我'select CAST(@t as float)
/*
消息 8114,级别 16,状态 5,第 7 行
从数据类型 varchar 转换为 float 时出错。
*/
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'这里面已经把数字都筛选出来了。select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 查询列这里没有问题,但是把convert(float ,sswx.Zhi1)放条件里面就报错了。也就是说:
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
这样没有问题,但是
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where convert(float,sswx.Zhi1)>111
这样就有问题了
试试这个:
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where ISNUMERIC(sswx.Zhi1) = 1 and
convert(float,sswx.Zhi1)>111
试试这个:
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where ISNUMERIC(sswx.Zhi1) = 1 and
convert(float,sswx.Zhi1)>111
一样的问题,而且ISNUMERIC不是来判断纯粹的数字,比如“$12121”,“1/2”它得出的值也是为1
sswx.name1 ,
sswx.zhi1 ,
CONVERT(FLOAT, sswx.Zhi1) AS tt ,
sswx.name2 ,
sswx.zhi2
FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID ,
sswx1.Name AS Name1 ,
sswx1.Zhi AS Zhi1 ,
CONVERT(FLOAT, sswx1.Zhi) AS dd ,
sswx2.Name AS Name2 ,
sswx2.Zhi AS Zhi2
FROM ( SELECT *
FROM SuShang_WX_PaiHaoAttribute
WHERE ISNUMERIC(Zhi1) = 1
) AS sswx1
LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID
WHERE LEN(sswx1.zhi) > 0
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
AND sswx1.Name LIKE '%密度%'
AND sswx2.Name LIKE '%弹性模量%'
) AS sswx
WHERE CONVERT(FLOAT, sswx.Zhi1) > 111
这样呢:
select *
from
(
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from
(
select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where ISNUMERIC(sswx.Zhi1) = 1
)t
where convert(float,Zhi1)>111
sswx.name1 ,
sswx.zhi1 ,
sswx.Zhi1,-- CONVERT(FLOAT, sswx.Zhi1) AS tt ,
sswx.name2 ,
sswx.zhi2
FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID ,
sswx1.Name AS Name1 ,
sswx1.Zhi AS Zhi1 ,
CONVERT(FLOAT, sswx1.Zhi) AS dd ,
sswx2.Name AS Name2 ,
sswx2.Zhi AS Zhi2
FROM ( SELECT *
FROM SuShang_WX_PaiHaoAttribute
WHERE ISNUMERIC(Zhi1) = 1
) AS sswx1
LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID
WHERE LEN(sswx1.zhi) > 0
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
AND sswx1.Name LIKE '%密度%'
AND sswx2.Name LIKE '%弹性模量%'
) AS sswx
WHERE CONVERT(FLOAT, sswx.Zhi1) > 111
sswx.name1 ,
sswx.zhi1 ,
sswx.Zhi1,-- CONVERT(FLOAT, sswx.Zhi1) AS tt ,
sswx.name2 ,
sswx.zhi2
FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID ,
sswx1.Name AS Name1 ,
CONVERT(FLOAT, sswx.Zhi1) --sswx1.Zhi
AS Zhi1 ,
CONVERT(FLOAT, sswx1.Zhi) AS dd ,
sswx2.Name AS Name2 ,
sswx2.Zhi AS Zhi2
FROM ( SELECT *
FROM SuShang_WX_PaiHaoAttribute
WHERE ISNUMERIC(Zhi1) = 1
) AS sswx1
LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID
WHERE LEN(sswx1.zhi) > 0
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
AND sswx1.Name LIKE '%密度%'
AND sswx2.Name LIKE '%弹性模量%'
) AS sswx
WHERE sswx.Zhi1> 111
试了你的方法,现在是报这个sswx.Zhi1>111 的问题,报一样的错误
FROM ( SELECT sswx1.PaiHaoID AS PaiHaoID ,
sswx1.Name AS Name1 ,
CONVERT(FLOAT, sswx.Zhi1) --sswx1.Zhi
AS Zhi1 ,
CONVERT(FLOAT, sswx1.Zhi) AS dd ,
sswx2.Name AS Name2 ,
sswx2.Zhi AS Zhi2
FROM ( SELECT *
FROM SuShang_WX_PaiHaoAttribute
WHERE ISNUMERIC(Zhi1) = 1
) AS sswx1
LEFT JOIN WX_PaiHaoAttribute AS sswx2 ON sswx1.PaiHaoID = sswx2.PaiHaoID
WHERE LEN(sswx1.zhi) > 0
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
AND sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
AND sswx1.Name LIKE '%密度%'
AND sswx2.Name LIKE '%弹性模量%'
) AS sswx
--WHERE sswx.Zhi1> 111
看看到哪里报错
没问题,出来都是正常的值,其实我觉得里面
CONVERT(FLOAT, sswx.Zhi1) AS Zhi1
已经成功执行了,应该没什么问题,但是出来外面再一次筛选,就报错了
select * from (
select top 10000000 *,convert(float,zhi) as convertzhi from WX_PaiHaoAttribute where
name like '%密度%'
and LEN(zhi) > 0
and zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
) as tmp_tb
where convert(float,convertzhi)>1
加上top,数据是没有10000000 这么多的
把top去掉,就报 从数据类型 varchar 转换为 float 时出错。
top 100 percent 就报错