----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-05 11:15:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([NUM] int,[AET] numeric(4,2))
insert [huang]
select 1,13.11 union all
select 2,1.00 union all
select 3,4.34 union all
select 5,7.0 union all
select 6,9.0
--------------生成数据--------------------------select * from [huang]
WHERE SUBSTRING(CAST(aet AS VARCHAR) ,CHARINDEX('.',CAST(aet AS VARCHAR))+1,LEN(CAST(aet AS VARCHAR)))<>0
----------------结果----------------------------
/*
NUM AET
----------- ---------------------------------------
1 13.11
3 4.34
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-05 11:15:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([NUM] int,[AET] numeric(4,2))
insert [huang]
select 1,13.11 union all
select 2,1.00 union all
select 3,4.34 union all
select 5,7.0 union all
select 6,9.0
--------------生成数据--------------------------select * from [huang]
WHERE SUBSTRING(CAST(aet AS VARCHAR) ,CHARINDEX('.',CAST(aet AS VARCHAR))+1,LEN(CAST(aet AS VARCHAR)))<>0
----------------结果----------------------------
/*
NUM AET
----------- ---------------------------------------
1 13.11
3 4.34
*/
insert into tb
select 1,13.11 union all
select 2,1.00 union all
select 3,4.34 union all
select 5,7.0 union all
select 6,9.0
go
select * from tb where CEILING(aet)!=aet
/*
NUM AET
----------- ---------------------------------------
1 13.11
3 4.34(2 行受影响)*/
go
drop table tb