select a.* from 旅客住店数据表 a,旅客住店数据表 b where a.liveintime=b.liveintime and a.leavetime=b.leavetime and a.guestname<>b.guestname
加入ORACLE群吧!群号是:19312711
select c.* from (select count(*) as cs,papertype,papercode from tab group by papertype,papercode having count(*) >2) a, (select count(*) as cs,papertype,papercode from tab a group by a.papertype,a.papercode having count(*) >2) b, tab c where a.cs=b.cs --出入次数相同 and (c.liveintime,c.leavetime) in (select distinct o.liveintime,o.leavetime from (--同时出入的时间次数 >2是为了体现多家旅店 (select z.liveintime,z.leavetime,count(*) from tab z where z.papertype=a.papertype and z.papercode=a.papercode group z.liveintime,z.leavetime having count(*)>2) intersect (select x.liveintime,x.leavetime,count(*) from tab x where x.papertype=b.papertype and x.papercode=b.papercode group by x.liveintime,x.leavetime having count(*) >2) ) o )试试看吧。
SELECT papertype || papercode FROM table1 WHERE (hotelname, liveintime, leavetime) IN (SELECT hotelname, liveintime, leavetime FROM table1 GROUP BY hotelname, liveintime, leavetime HAVING COUNT(DISTINCT papertype || papercode) > 1) GROUP papertype || papercode HAVING COUNT(DISTINCT hotelname) > 1
and a.leavetime=b.leavetime and a.guestname<>b.guestname
from
(select count(*) as cs,papertype,papercode from tab
group by papertype,papercode
having count(*) >2) a,
(select count(*) as cs,papertype,papercode from tab a
group by a.papertype,a.papercode
having count(*) >2) b,
tab c
where a.cs=b.cs --出入次数相同
and (c.liveintime,c.leavetime) in (select distinct o.liveintime,o.leavetime from
(--同时出入的时间次数 >2是为了体现多家旅店
(select z.liveintime,z.leavetime,count(*) from tab z
where z.papertype=a.papertype
and z.papercode=a.papercode
group z.liveintime,z.leavetime
having count(*)>2)
intersect
(select x.liveintime,x.leavetime,count(*) from tab x
where x.papertype=b.papertype
and x.papercode=b.papercode
group by x.liveintime,x.leavetime
having count(*) >2)
) o
)试试看吧。
FROM table1
WHERE (hotelname, liveintime, leavetime) IN
(SELECT hotelname, liveintime, leavetime
FROM table1
GROUP BY hotelname, liveintime, leavetime
HAVING COUNT(DISTINCT papertype || papercode) > 1)
GROUP papertype || papercode
HAVING COUNT(DISTINCT hotelname) > 1