用户表
ID NUMBER(8,0) NOT NULL,
ORDERDATE DATE,
CANCELDATE DATE,
STATUS NUMBER(1,0)
推送表(一条记录对应推送一次)(数据量很大)
ID NUMBER NOT NULL,
CONTENT VARCHAR2(255),
FEEPHONENO VARCHAR2(12),----------(对应用户表id)
SENDTIME DATE
如何统计出来当前月15号之前每个用户的推送条数小于10的记录和
...全文
1437打赏收藏
问个查询问题
用户表 ID NUMBER(8,0) NOT NULL, ORDERDATE DATE, CANCELDATE DATE, STATUS NUMBER(1,0) 推送表(一条记录对应推送一次)(数据量很大) ID NUMBER NOT NULL, CONTENT VARCHAR2(255), FEEPHONENO VARCHAR2(12),----------(对应用户表id) SENDTIME DATE 如何统计出来当前月15号之前每个用户的推送条数小于10的记录和
SELECT B.*, A.AMOUNT FROM
(SELECT FEEPHONENO, COUNT(*) AS AMOUNT
FROM 推送表
WHERE SENDTIME BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '01', 'yyyymmdd') AND TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '15', 'yyyymmdd')
GROUP BY FEEPHONENO
HAVING COUNT(*) < 10) A
INNER JOIN
用户表 B
ON A.FEEPHONENO = B.ID
select distinct id,sum(cnt) over (partiton by id)
from
(select id,count(*) over(partition by id) cnt
from 推送表
where sendtime < to_date(to_char(sysdate,'yyyymm')||'15','yyyymmdd')
)a
where cnt < 10
用户表
ID NUMBER(8,0) NOT NULL,
ORDERDATE DATE,
CANCELDATE DATE,
STATUS NUMBER(1,0)
推送表(一条记录对应推送一次)(数据量很大)
ID NUMBER NOT NULL,
CONTENT VARCHAR2(255),
FEEPHONENO VARCHAR2(12),----------(对应用户表id)
SENDTIME DATE
如何统计出来当前月15号之前每个用户的推送条数小于10的记录和
SELECT FEEPHONENO, COUNT(*) AS AMOUNT
FROM 推送表
WHERE SENDTIME BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '01', 'yyyymmdd') AND TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '15', 'yyyymmdd')
GROUP BY FEEPHONENO
HAVING COUNT(*) < 10
以上是当前月15号之前推送条数小于10的用户及其推送条数
SELECT COUNT(*) FROM
(SELECT FEEPHONENO
FROM 推送表
WHERE SENDTIME BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '01', 'yyyymmdd') AND TO_DATE(TO_CHAR(SYSDATE, 'yyyymm') || '15', 'yyyymmdd')
GROUP BY FEEPHONENO
HAVING COUNT(*) < 10)
select sum(cnt)
from
(select id,count(*) cnt
from 推送表
where sendtime < to_date(to_char(sysdate,'yyyymm')||'15','yyyymmdd')
group by id
having count(*) < 10);