SQL判断字段的值是否为整数？

[bl_song]

比如说数据库表a有一个字段为Height，有以下记录，现在想用SQL获得小于或等于某个数的最大Height值，且Height值为整数
Height
41
42
43
43.81
44
45
46
46.52
47
48

现在我给一个数43.67，sql语句查出来值应该为43；给一个数46.89，sql语句sql语句查出来值应该为46；
给一个数46，sql语句sql语句查出来值应该为46；

[SQL77]

SELECT FLOOR(HEIGHT) FROM TB

[东那个升]

Height=cast(Height as int)

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([Height] numeric(4,2))
go
insert [tb]
select 41 union all
select 42 union all
select 43 union all
select 43.81 union all
select 44 union all
select 45 union all
select 46 union all
select 46.52 union all
select 47 union all
select 48

select max(Height) from [tb]
where cast(Height as int) = Height
and Height <= 43.67

---------------------------------------
43.00

(1 行受影响)

[--小F--]

select
  cast(max(height) as int)
from
  tb
group by
   height
having
   cast(max(height) as int)<'xx'

[SQL77]

SELECT * FROM TB where HEIGHT=FLOOR(43.86)

[东那个升]

create table #tb(Height float)

insert #tb select 41
insert #tb select 42
insert #tb select 43
insert #tb select 43.81
insert #tb select 44
insert #tb select 45
insert #tb select 46
insert #tb select 46.52
insert #tb select 47
insert #tb select 48


select max(Height)
from #tb 
where Height=cast (Height as int)
--------------------
                    48

(1 行受影响)

[bl_song]

[Quote=引用 1 楼 sql77 的回复:]

SELECT FLOOR(HEIGHT) FROM TB
[/Quote]
不对。

[asharpnet]

[Quote=引用 5 楼 sql77 的回复:]
SELECT * FROM TB where HEIGHT=FLOOR(43.86)
[/Quote]
楼上正解

[htl258_Tony]

where floor(col)=col and ceiling(col)=col

[htl258_Tony]

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-27 15:06:36
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([Height] DECIMAL(18,2))
INSERT [tb]
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 43.81 UNION ALL
SELECT 44 UNION ALL
SELECT 45 UNION ALL
SELECT 46 UNION ALL
SELECT 46.52 UNION ALL
SELECT 47 UNION ALL
SELECT 48
GO
--SELECT * FROM [tb]

-->SQL查询如下:
select [Height],FLOOR([Height]) h1,CEILING([Height]) h2 from tb
/*
Height                                  h1                                      h2
--------------------------------------- --------------------------------------- ---------------------------------------
41.00                                   41                                      41
42.00                                   42                                      42
43.00                                   43                                      43
43.81                                   43                                      44
44.00                                   44                                      44
45.00                                   45                                      45
46.00                                   46                                      46
46.52                                   46                                      47
47.00                                   47                                      47
48.00                                   48                                      48

(10 行受影响)
*/

看你要取哪一个

[bl_song]

[Quote=引用 4 楼 fredrickhu 的回复:]

SQL code
select
cast(max(height) as int)
from
tb
group by
height
having
cast(max(height) as int)<'xx'
[/Quote]
这个出来结果是一堆，不是最大的

[bl_song]

总结是 3，5，6，10正确！
1.

select max(Height) from [tb]
where cast(Height as int) = Height
and Height <= 43.67

2.

SELECT Height  FROM TB where HEIGHT=FLOOR(43.86)

3.

select max(Height)
from #tb 
where Height=cast (Height as int)

[零点的小白]

有点晕

[bl_song]

[Quote=引用 13 楼 macheol10 的回复:]

有点晕
[/Quote]
休息休息！

[bashen1101]

if OBJECT_ID('[test]')is not null drop table [test]
go
create table test (Height float)
insert into test 
	select 41    union all
	select 42    union all
	select 43    union all
	select 43.81 union all
	select 44    union all
	select 45    union all
	select 46    union all
	select 46.52 union all
	select 47    union all
	select 48  
go
select * from test
go
alter proc sp_a
	@a float
as
begin
	select top 1 * from
	(select * from test
	where CHARINDEX('.',cast(Height as varchar))=0) a
	where Height<=@a
	order by Height desc
end
go

select * from test
exec sp_a 43.99