## 求高人指点一条sql语句

0xTang 发布于 2012/08/09 22:40

salary表结构和数据如下：

0
```select * from (
select t.*,
case when jan>10000 then 1 else 0 end jan1,
case when feb>10000 then 1 else 0 end feb1,
case when mar>10000 then 1 else 0 end mar1,
case when apr>10000 then 1 else 0 end apr1,
case when may>10000 then 1 else 0 end may1,
case when jun>10000 then 1 else 0 end jun1
from tab1 t
) where jan1+feb1+mar1+apr1+may1+jun1>=3```

0

1写个方法,参数为所有的月份,返回值是这些月份中薪水排第三的值

2.select * from salary a where funciont_xxx(a.jan,a.feb,a.mar,a.apr,a.may,a.jun) > 10000

0

#### 引用来自“hulubo”的答案

```select * from (
select t.*,
case when jan>10000 then 1 else 0 end jan1,
case when feb>10000 then 1 else 0 end feb1,
case when mar>10000 then 1 else 0 end mar1,
case when apr>10000 then 1 else 0 end apr1,
case when may>10000 then 1 else 0 end may1,
case when jun>10000 then 1 else 0 end jun1
from tab1 t
) where jan1+feb1+mar1+apr1+may1+jun1>=3```

```select * from (
select t.*,
case when jan>10000 then 1 else 0 end jan1,
case when feb>10000 then 1 else 0 end feb1,
case when mar>10000 then 1 else 0 end mar1,
case when apr>10000 then 1 else 0 end apr1,
case when may>10000 then 1 else 0 end may1,
case when jun>10000 then 1 else 0 end jun1
from salary t
) as t2 where jan1+feb1+mar1+apr1+may1+jun1>=3```

0

0

oracle:

```select * from salary a where (
floor(length(a.m1)/5) +
floor(length(a.m2)/5) +
floor(length(a.m3)/5) +
floor(length(a.m4)/5) +
floor(length(a.m5)/5) +
floor(length(a.m6)/5)
) >= 3```