with tt as( select to_date(rq,'yyyy-mm-dd')rq,max(num)maxnum from tb group by to_date(rq,'yyyy-mm-dd') )select tb.rq from tb join tt on to_date(tb.rq,'yyyy-mm-dd')=tt.rq and tb.num=tt.maxnum
小弟追问高人:这个with前面是什么啊?
--1. SELECT * FROM t a WHERE NOT EXISTS (SELECT 1 FROM t b WHERE a.cid = b.cid AND trunc(a.rq) = trunc(b.rq) AND b.num > a.num); --2. SELECT * FROM (SELECT t.* row_number() over (PARTITION BY cid, trunc(rq) ORDER BY num DESC) rn FROM t) WHERE rn = 1;
select to_date(rq,'yyyy-mm-dd')rq,max(num)maxnum from tb
group by to_date(rq,'yyyy-mm-dd')
)select tb.rq from tb join tt on to_date(tb.rq,'yyyy-mm-dd')=tt.rq
and tb.num=tt.maxnum
小弟追问高人:这个with前面是什么啊?
SELECT *
FROM t a
WHERE NOT EXISTS (SELECT 1
FROM t b
WHERE a.cid = b.cid
AND trunc(a.rq) = trunc(b.rq)
AND b.num > a.num);
--2.
SELECT *
FROM (SELECT t.* row_number() over (PARTITION BY cid, trunc(rq) ORDER BY num DESC) rn
FROM t)
WHERE rn = 1;
对于with的用法与函义的话。最好是去www.baidu.com了。。它的作用就相当于建立了一个临时表
嗯嗯,了解了。但是这样子只查询到所有记录中最大的num值,而且num值为最大的都被查询出来了,有重复的数据。
大哥,有num重复,重复的数据咋去掉啊?
大哥,我错了,是我错了,忘了有个cid……
oracle CTE简介