统计时间

 

CREATE TABLE `TEST` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'g3用户ID',
`order_no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单ID',
`type` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '类型 1：交易 2：续费',
`account` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
`pre_account` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '操作前的金额',
`next_account` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '操作后的金额',
`pay_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '支付状态 0待支付 1支付成功 2支付失败 默认0',
`remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`oper_type` int(11) NOT NULL DEFAULT '0' COMMENT '操作类型[用于回调] 1开通 2续费 3VIP服务',
`oper_content` int(11) NOT NULL DEFAULT '0' COMMENT '操作表的内容[用于回调] 开通/续费=>月数 VIP服务=>提升的粉丝数 ',
PRIMARY KEY (`id`),
KEY `user_id` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8 COMMENT='云头条流水表';

SELECT * FROM TEST where create_time+31*24*3600>UNIX_TIMESTAMP()
SELECT DISTINCT(uid) FROM TEST WHERE create_time+31*24*3600>UNIX_TIMESTAMP()

SELECT uid,order_no as "订单号",case type
when 1 then "交易"
when 2 then "续费"
end as "类型",account as "金额",case pay_status
when 0 then "待支付"
when 1 then "支付成功"
when 2 then "支付失败"
end AS "支付状态",remark as "详情",FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s') as "创建时间",'' as "退款金额"
FROM TEST
WHERE uid IN (SELECT uid FROM TEST WHERE create_time+31*24*3600>UNIX_TIMESTAMP()) or 2>1
ORDER BY uid,create_time
;

SELECT MAX(id),uid, FROM_UNIXTIME(create_time+31*24*3600,'%Y-%m-%d %H:%i:%s') as "到期时间" FROM TEST WHERE create_time+31*24*3600>UNIX_TIMESTAMP() GROUP BY uid;

 

SELECT t.*,c.exp AS "到期时间",'' as "退款金额" FROM (
SELECT uid,order_no as "订单号",case type
when 1 then "交易"
when 2 then "续费"
end as "类型",account as "金额",case pay_status
when 0 then "待支付"
when 1 then "支付成功"
when 2 then "支付失败"
end AS "支付状态",remark as "详情",FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s') as "创建时间"
FROM TEST
WHERE uid IN (SELECT uid FROM TEST WHERE create_time+31*24*3600>UNIX_TIMESTAMP())
ORDER BY uid,create_time
) AS t LEFT JOIN
(
SELECT MAX(id), uid, FROM_UNIXTIME(create_time+31*24*3600,'%Y-%m-%d %H:%i:%s') as exp FROM TEST WHERE create_time+31*24*3600>UNIX_TIMESTAMP() GROUP BY uid
) AS c ON t.uid = c.uid;