如表mytable
里面两个列，一个ID，一个为数字（双精度型）
ID Floor
1 -2
2 -1
3 -2.1
4 1
5 2
6 1.2
7 2.3
...
现在我要写个查询语句
select XX as 地上层数, XX as 地下层数, XX as 总层数, XX as 楼上最大值值为
form mytable
（查询规则为：所有小数都取整，比如2.3只能算是2，-2.1算-2
现在上面例子结果应该是:地上2层，地下2层，总共4层，楼上最大值为2（就是2.3取整））
请问大家，语句如何写啊
------解决思路----------------------
Select ID,
FLOOR(Case When MAX(Floors) <= 0 Then 0 Else MAX(Floors) End) As 地上层数,
FLOOR(Case When MIN(Floors) >= 0 Then 0 Else 0 -MIN(Floors) End) As 地下层数,
FLOOR(Case When MAX(Floors) <= 0 Then 0 Else MAX(Floors) End) + FLOOR(Case When MIN(Floors) >= 0 Then 0 Else 0 -MIN(Floors) End) As 总层数
From @MyTable
Group By ID
------解决思路----------------------
SELECT CAST(MAX(CASE WHEN[Floor]>0 THEN[Floor]END)AS INT)AS[地上层数]
,-CAST(MIN(CASE WHEN[Floor]<0 THEN[Floor]END)AS INT)AS[地下层数]
,CAST(MAX(CASE WHEN[Floor]>0 THEN[Floor]END)AS INT)-CAST(MIN(CASE WHEN[Floor]<0 THEN[Floor]END)AS INT)AS[总层数]
,CAST(MAX(CASE WHEN[Floor]>0 THEN[Floor]END)AS INT)AS[楼上最大值值为]
FROM mytable
------解决思路----------------------
create table mytable
(ID int,[Floor] decimal(5,1))
insert into mytable
select 1,-2 union all
select 2,-1 union all
select 3,-2.1 union all
select 4,1 union all
select 5,2 union all
select 6,1.2 union all
select 7,2.3
select count(distinct case when [Floor]>0 then round([Floor],0) else null end) '地上层数',
count(distinct case when [Floor]<0 then round([Floor],0) else null end) '地下层数',
count(distinct round([Floor],0)) '总层数',
cast(max(distinct case when [Floor]>0 then round([Floor],0) else null end) as int) '楼上最大值值'
from mytable
/*
地上层数 地下层数 总层数 楼上最大值值
----------- ----------- ----------- -----------
2 2 4 2
(1 row(s) affected)
*/