用户表
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的记录和
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的记录和
from
(select id,count(*) cnt
from 推送表
where sendtime < to_date(to_char(sysdate,'yyyymm')||'15','yyyymmdd')
group by id
having count(*) < 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)这个就是“当前月15号之前每个用户的推送条数小于10的记录和”
from
(select id,count(*) over(partition by id) cnt
from 推送表
where sendtime < to_date(to_char(sysdate,'yyyymm')||'15','yyyymmdd')
)a
where cnt < 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