有个表记录如下
GID PHONENO SERVICEID OBJECTID OBJECTTYPE SUBDATE
1 13800000001 90387001 10279 0 2010-4-1
2 13800000001 90387001 10245 1 2010-4-5
3 13800000002 90387001 10245 1 2010-4-4
4 13800000002 90387001 10279 0 2010-4-6
5 13800000003 90387001 10279 0 2010-4-6要查询出所有手机号码第一次订购的产品类型(OBJECTTYPE)为0 的记录(根据subdate先后判断该号码的首次订购)
结果应该为
1 13800000001 90387001 10279 0 2010-4-1
5 13800000003 90387001 10279 0 2010-4-6
请教各位大侠,该如何写?
谢谢!
GID PHONENO SERVICEID OBJECTID OBJECTTYPE SUBDATE
1 13800000001 90387001 10279 0 2010-4-1
2 13800000001 90387001 10245 1 2010-4-5
3 13800000002 90387001 10245 1 2010-4-4
4 13800000002 90387001 10279 0 2010-4-6
5 13800000003 90387001 10279 0 2010-4-6要查询出所有手机号码第一次订购的产品类型(OBJECTTYPE)为0 的记录(根据subdate先后判断该号码的首次订购)
结果应该为
1 13800000001 90387001 10279 0 2010-4-1
5 13800000003 90387001 10279 0 2010-4-6
请教各位大侠,该如何写?
谢谢!
SQL> select * from phone p1 where p1.objecttype=0
2 and subdate in (select min(subdate) from phone p2 where p2.phoneno=p1.phone
no); GID PHONENO SERVICEID OBJECTID OBJECTTYPE SUBDATE
---------- ----------- ---------- ---------- ---------- --------------
1 13800000001 90387001 10279 0 01-4月 -10
5 13800000003 90387001 10279 0 06-4月 -10
select GID,PHONENO,SERVICEID,OBJECTID,OBJECTTYPE,SUBDATE from
(
select table.*,row_number() over(partition by 13800000001 order by SUBDATE) rn from table
)
where rn = 1 and OBJECTTYPE = 0;
select t.* from tb t where OBJECTTYPE = 0 and not exists (select 1 from tb where PHONENO = t.PHONENO and SUBDATE < t.SUBDATE)
select t.* from tb t where OBJECTTYPE = 0 and not exists (select 1 from tb where OBJECTTYPE = 0 and PHONENO = t.PHONENO and SUBDATE < t.SUBDATE)
SELECT *
FROM order_rec a
WHERE a.OBJECTTYPE = 0 AND
(PHONENO, SUBDATE) IN
(SELECT PHONENO, MIN(SUBDATE) SUBDATE FROM order_rec t GROUP BY t.PHONENO);
--2.
SELECT *
FROM order_rec a
WHERE a.OBJECTTYPE = 0 AND
EXISTS (SELECT 1
FROM (SELECT PHONENO, MIN(SUBDATE) subdate FROM order_rec t GROUP BY t.PHONENO) b
WHERE b.PHONENO = a.PHONENO AND
b.SUBDATE = a.SUBDATE);
--3.
SELECT *
FROM order_rec
WHERE gid IN
(SELECT MIN(GID) KEEP(dense_rank FIRST ORDER BY SUBDATE) FROM order_rec GROUP BY PHONENO) AND
OBJECTTYPE = 0;