ORACLE数据库查询语句

CREATE TABLE test (

usernameVARCHAR(20),

starttime VARCHAR(14),

endtime VARCHAR(14)

)

INSERT INTO test

SELECT 'zhangsan','20130305080000', '20130305100000' FROM dual UNION ALL

SELECT 'zhangsan','20130305070000', '20130305090000' FROM dual UNION ALL

SELECT 'lisi','20130305060000', '20130305110000' FROM dual UNION ALL

SELECT 'lisi','20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五1', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五1', '20130305060000', '20130305080000' FROM dual UNION ALL

SELECT '王五2', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五2', '20130305070000', '20130305080000' FROM dual UNION ALL

SELECT '王五3', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五3', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '王五4', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五4', '20130305070000', '20130305100000' FROM dual UNION ALL

SELECT '赵六1', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六1', '20130305070000', '20130305080000' FROM dual UNION ALL

SELECT '赵六2', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六2', '20130305060000', '20130305080000' FROM dual UNION ALL

SELECT '赵六3', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六3', '20130305070000', '20130305110000' FROM dual UNION ALL

SELECT '赵六4', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六4', '20130305060000', '20130305110000' FROM dual

COLUMN "USERNAME" FORMAT A8

COLUMN "STARTTIME" FORMAT A15

COLUMN "ENDTIME" FORMAT A15

SELECT

*

FROM

test Main

WHERE

EXISTS (

SELECT 1

FROM

test Sub

WHERE

Main.username = Sub.username

AND NOT ( Main.STARTTIME = Sub.STARTTIME AND Main.ENDTIME = Sub.ENDTIME)

AND (

-- 首先判断 第一种情况.

-- 时间段1：----------+=====+-----

-- 时间段2：--------+====+--------

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：----------+====+--------

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：--------+==========+--

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：----------+==========+--

(

Sub.StartTime <= Main.StartTime

AND Sub.EndTime >Main.StartTime

)

OR

-- 然后判断第二种情况.

-- 时间段1：------+=========+-----

-- 时间段2：--------+====+--------

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：------+====+--------

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：--------+==========+--

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：------+==========+--

(Sub.StartTime >= Main.StartTime

AND Sub.StartTime <Main.EndTime

)

)

)

假设设备表名为device_table, 电量表名为snum_table, 可以这样查询:

with s as (

select sum(b.snum) as sum_num, a.clientid

from device_table a

inner join snum_table b on a.deviceid = b.deviceid

group by a.clientid

)

select s0.*

from (

select s.clientid, s.sum_num, rownum as row_number

from s

order by s.sum_num desc

) s0

where s0.row_number <= 3

---