集团表:
集团编号: group_id
集团名称: gounp_name
集团经理编号:mgr_id集团成员通话表:
集团编号:group_id
号码:no
通话时间:phone_time集团成员资料表:
成员编号:e_id
号码:no
姓名:name问:
3 成员编号为’332536’的集团2008年10月的成员通话数量
4 集团编号为’17300’,2008年10月,除2008-10-3一天通话成员的姓名
5 集团编号为’17330’ 的,2006年6月每天通话数量
集团编号: group_id
集团名称: gounp_name
集团经理编号:mgr_id集团成员通话表:
集团编号:group_id
号码:no
通话时间:phone_time集团成员资料表:
成员编号:e_id
号码:no
姓名:name问:
3 成员编号为’332536’的集团2008年10月的成员通话数量
4 集团编号为’17300’,2008年10月,除2008-10-3一天通话成员的姓名
5 集团编号为’17330’ 的,2006年6月每天通话数量
select count(*) from 集团成员通话表
where group_id = (select a.group_id from 集团表 a,集团成员通话表 b,集团成员资料表 c where a.group_id = b.group_id and b.no = c.no and c.e_id = '332536')
and phone_time between to_date('2008-10-01','YYYY-MM-DD') and to_date('2008-10-31','YYYY-MM-DD');4
select name from 集团成员资料表 where no in(select no from 集团成员通话表 where group_id = '17300' and phone_time between to_date('2008-10-01','YYYY-MM-DD') and to_date('2008-10-31','YYYY-MM-DD') and phone_time != to_date('2008-10-03','YYYY-MM-DD'));5
select extract(day from phone_time),count(*)
group by extract(day from phone_time)
from 集团成员通话表
where group_id = '17330' and trunc(sysdate,'month') = to_date('2006-06-01','YYYY-MM-DD');
--3
select count(*) 通话数量
from 集团表 a,集团成员通话表 b,集团成员资料表 c
where a.group_id=b.group_id and b.no=c.no and c.e_id='332536' and to_char(phone_time,'yyyy-mm')='2008-10'--4
select name
from 集团成员资料表 a ,(select no,phone_time
from 集团成员通话表
where group_id='17300' and phone_time!='2008-10-3') b
where a.no=b.no and to_char(b.phone_time,'yyyy-mm')='2008-10'--5
select to_char(b.phone_time,'yyyy-mm-dd'),count(*) 每天通话数量
from 集团成员通话表
where group_id='17330'
group by to_char(b.phone_time,'yyyy-mm-dd')
order by count(*)
--cy为集团成员资料表, jtcy为集团成员通话表:select count(*)
from cy c, jtcy j
where c.no = j.no and e_id = '332536' and j.trunc(phone_time, 'mm') = to_date('2008-10', 'yyyy-mm');select name
from cy c, jtcy j
where c.no = j.no and j.group_id = '17300' and trunc(j.phone_time, 'mm') = to_date('2008-10', 'yyyy-mm') and j.phone_time <> to_date('2008-10-03', 'yyyy-mm-dd');select count(*)
from jtcy j
where j.group_id='17330' and trunc(j.phone_time, 'mm') = to_date('2008-06', 'yyyy-mm')
group by to_char(phone_time, 'yyyy-mm-dd');
WHERE A.NO = B.NO
AND B.GROUP_ID = C.GROUP_ID
AND B.PHONE_NAME BETWEEN TO_DATE('2008-10-01','YYYY-MM-DD')
AND TO_DATE('2008-10-31','YYYY-MM-DD')
AND A.E_ID = '332536';
SELECT NAME FROM JTCYZL A WHERE EXISTS (SELECT * FROM JTCYTH B
WHERE A.NO = B.NO
AND B.PHONE_TIME BETWEEN TO_DATE('2008-10-01','YYYY-MM-DD')
AND TO_DATE('2008-10-31','YYYY-MM-DD')
AND B.PHONE_TIME != TO_DATE('2008-10-03','YYYY-MM-DD')
AND B.GROUP_ID = '17330');
SELECT B.PHONE_TIME,COUNT(1) FROM JTCYTH B
WHERE B.PHONE_TIME BETWEEN TO_DATE('2008-06-01','YYYY-MM-DD')
AND TO_DATE('2008-06-30','YYYY-MM-DD')
AND B.GRPUP_ID = '17330'
GROUP BY B.PHONE_TIME;
SELECT COUNT(1) FROM JTCYZL A, JTCYTH B, JTB C
WHERE A.NO = B.NO
AND B.GROUP_ID = C.GROUP_ID
AND B.PHONE_NAME BETWEEN TO_DATE('2008-10-01','YYYY-MM-DD')
AND TO_DATE('2008-10-31','YYYY-MM-DD')
AND A.E_ID = '332536';
SELECT NAME FROM JTCYZL A WHERE EXISTS (SELECT * FROM JTCYTH B
WHERE A.NO = B.NO
AND B.PHONE_TIME BETWEEN TO_DATE('2008-10-01','YYYY-MM-DD')
AND TO_DATE('2008-10-31','YYYY-MM-DD')
AND B.PHONE_TIME != TO_DATE('2008-10-03','YYYY-MM-DD')
AND B.GROUP_ID = '17330');
SELECT B.PHONE_TIME,COUNT(1) FROM JTCYTH B
WHERE B.PHONE_TIME BETWEEN TO_DATE('2008-06-01','YYYY-MM-DD')
AND TO_DATE('2008-06-30','YYYY-MM-DD')
AND B.GRPUP_ID = '17330'
GROUP BY B.PHONE_TIME;
--3.
SELECT COUNT(0) FROM 集团成员通话表 A, 集团成员资料表 B
WHERE A.NO = B.NO
AND B.E_ID = '332536'
AND TO_CHAR(A.PHONE_TIME, 'YYYYMM') = '200810'--4.
SELECT B.NAME FROM 集团成员通话表 A, 集团成员资料表 B
WHERE A.NO = B.NO
AND A.GROUP_ID = '17300'
AND TO_CHAR(A.PHONE_TIME, 'YYYYMM') = '200810'
AND TO_CHAR(A.PHONE_TIME, 'YYYYMMDD') <> '20081003'--5.
SELECT PHONE_NAME, COUNT(0) FROM 集团成员通话表
WHERE GROUP_ID = '17300'
AND TO_CHAR(PHONE_TIME, 'YYYYMM') = '200606'
GROUP BY PHONE_TIME