表名T_HISTORYRPID, TRANSTIME, MOBILENO, TRANSTYPE, STATE, INTIME
交易, 交易时间 , 手机号 , 交易类型 , 状态 , 入库时间
PK(RPID,PLATTIME)数据库基于MYSQL数据库统计符合要求的交易笔数
要求是:
在T_HISTORY表里,某个手机号在某个时间(INTIME)段发生的状态STATE为2,并且第一笔交易或最后一笔交易的TRANSTYPE为1的交易 为符合要求交易
交易, 交易时间 , 手机号 , 交易类型 , 状态 , 入库时间
PK(RPID,PLATTIME)数据库基于MYSQL数据库统计符合要求的交易笔数
要求是:
在T_HISTORY表里,某个手机号在某个时间(INTIME)段发生的状态STATE为2,并且第一笔交易或最后一笔交易的TRANSTYPE为1的交易 为符合要求交易
FROM T_HISTORY T1
INNER JOIN (
SELECT MOBILENO FROM T_HISTORY T1
WHERE INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND TRANSTYPE=1
AND NOT EXISTS(
SELECT 1 FROM T_HISTORY T2
WHERE T2.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND T2.TRANSTIME<T1.TRANSTIME
) OR NOT EXISTS(
SELECT 1 FROM T_HISTORY T2
WHERE T2.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND T2.TRANSTIME>T1.TRANSTIME
)
GROUP BY MOBILENO
) T2 ON T2.MOBILENO=T1.MOBILENO
AND T1.STATE=2
AND T1.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
SELECT COUNT(1)
FROM T_HISTORY T1
INNER JOIN (
SELECT MOBILENO FROM T_HISTORY T1
WHERE INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND TRANSTYPE=1
AND (NOT EXISTS(
SELECT 1 FROM T_HISTORY T2
WHERE T2.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND T2.TRANSTIME<T1.TRANSTIME
) OR NOT EXISTS(
SELECT 1 FROM T_HISTORY T2
WHERE T2.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
AND T2.TRANSTIME>T1.TRANSTIME
))
GROUP BY MOBILENO
) T2 ON T2.MOBILENO=T1.MOBILENO
AND T1.STATE=2
AND T1.INTIME BETWEEN '0000-00-00' AND '9999-12-31'
;