oracle 查询语句:
例如:
例如: id name age
1 小明 18
2 王刚 20
3 李丽 16
4 张娜 21
5 金泰 13
条件就是age>=18
显示的结果为:
1 小明 18
2 王刚 20
3 李丽
4 张娜 21
5 金泰
求各位大侠帮忙
例如:
例如: id name age
1 小明 18
2 王刚 20
3 李丽 16
4 张娜 21
5 金泰 13
条件就是age>=18
显示的结果为:
1 小明 18
2 王刚 20
3 李丽
4 张娜 21
5 金泰
求各位大侠帮忙
from student;
select p.devicecodeS ,z.nodename ,z.nidename ,z.direction ,z.roadtype ,p.status from
(select r.linkid as devicecodeS,count(r.status) as status from ROADSTATUSHISTORY r
where '2012-07-11 00:00:00'<to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and '2012-07-12 00:00:00'>to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and
r.status<=10 group by r.linkid) p,zlinks z
where z.linkid=p.devicecodeS order by p.devicecodeS asc
nuion select id,name,null age from student where age<=18;
union select id,name,null age from student where age<=18;
from student;
SELECT 1 id,'小明' name,18 age FROM DUAL
UNION ALL
SELECT 2, '王刚', 20 FROM DUAL
UNION ALL
SELECT 3, '李丽' ,16 FROM DUAL
UNION ALL
SELECT 4, '张娜' ,21 FROM DUAL
UNION ALL
SELECT 5, '金泰', 13 FROM DUAL
)
SELECT ID,NAME,DECODE(SIGN(AGE-18),-1,NULL,AGE) AGE FROM CTE;
结果如下:
ID NAME AGE
---------------------- ---- ----------------------------------------
1 小明 18
2 王刚 20
3 李丽
4 张娜 21
5 金泰 5 rows selected
(
id number,
name varchar2(20),
age number
)
insert into test values (5,'金泰','13');
select * from TEST
1 1 小明 18
2 2 王刚 20
3 3 李丽 16
4 4 张娜 21
5 5 金泰 13select id,
name,
case
when age > 18 then
age
else
null
end case
from test
1 1 小明
2 2 王刚 20
3 3 李丽
4 4 张娜 21
5 5 金泰
SELECT END IF;IF AGE IS NOT NULL THEN
SELECT ID,NAME,AGE FROM CET WHERE AGE>=18;
END IF;
select p.devicecodeS ,z.nodename ,z.nidename ,z.direction ,z.roadtype ,p.status from
(select r.linkid as devicecodeS,count(r.status) as status from ROADSTATUSHISTORY r
where '2012-07-11 00:00:00'<to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and '2012-07-12 00:00:00'>to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and
r.status<=10 group by r.linkid) p,zlinks z
where z.linkid=p.devicecodeS order by p.devicecodeS asc
首先这样做:
z.linkid=p.devicecodeS(+)
好像编号不能全部显示
SELECT z.linkid 把z.linkid加到select条件里面去
能不能按顺序显示
ORDER BY z.linkid
(select r.linkid as devicecodeS,count(r.status) as status from ROADSTATUSHISTORY r
where '2012-07-11 00:00:00'<to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and '2012-07-12 00:00:00'>to_char(r.timetag,'yyyy-MM-dd hh24:mi:ss') and
r.status<=10 group by r.linkid) p,zlinks z
where z.linkid=p.devicecodeS(+) order by z.linkid ,p.devicecodeS asc