1:注册用户表account 字段:注册时间 inserttime Id: account_id2:冲值表: payorder 字段:冲值时间:paytime 外键:account_id3:冲值表2:mpayorder 字段:冲值时间:mpaytime 外键:account_id
三表连联查询
1:要以inserttime 排序这条简单.2:要以paytime排序.但是payorder 和要mpayorder 的冲值时间对比.取最小值.就是先冲值的时间排序3:要以mpaytime排序.但是rmpayorder 和payorde要的冲值时间对比.取最小值.就是先冲值的时间排序
三表连联查询
1:要以inserttime 排序这条简单.2:要以paytime排序.但是payorder 和要mpayorder 的冲值时间对比.取最小值.就是先冲值的时间排序3:要以mpaytime排序.但是rmpayorder 和payorde要的冲值时间对比.取最小值.就是先冲值的时间排序
select t1.* , t2.* , t3.*
from account t1 , payorder t2 , mpayorder t3
where t1.account_id = t2.account_id and t1.account_id = t3.account_id
order by t1.inserttime2:要以paytime排序.但是payorder 和要mpayorder 的冲值时间对比.取最小值.就是先冲值的时间排序
select t1.* , t2.* , t3.*
from account t1 , payorder t2 , mpayorder t3
where t1.account_id = t2.account_id and t1.account_id = t3.account_id
order by t1.account_id, t2.paytime3:要以mpaytime排序.但是rmpayorder 和payorde要的冲值时间对比.取最小值.就是先冲值的时间排序
select t1.* , t2.* , t3.*
from account t1 , payorder t2 , mpayorder t3
where t1.account_id = t2.account_id and t1.account_id = t3.account_id
order by t1.account_id, t3.mpaytime
要以paytime排序.但是payorder 和要mpayorder 的冲值时间对比.取最小值.就是先冲值的时间排序
需要取两个时间的最小值么?
insert into account(10002,'csdn','2011-8-31 12:13:14')insert into payorder values('2011-08-10 12:12:12',10001)
insert into payorder values('2011-08-10 13:15:15',10001)
insert into payorder values('2011-08-10 13:15:08',10001)insert into mpayorder values('2011-08-10 12:5:12',10002)
insert into mpayorder values('2011-08-10 13:6:15',10002)
select a.account_id,a.inserttime,b.paytime,c.mpaytime,
case when b.paytime>c.mpaytime then c.mpaytime
when b.paytime<=c.mpaytime then b.paytime end as min_pay_time
from [注册用户表] a left join [冲值表] b on a.account_id=b.account_id
left join [冲值表2] c on a.account_id=c.account_id
order by 2,5
10002 csdn 2011-8-31 12:13:14 2011-08-10 13:15:08 2011-08-10 13:15:15
10001 paul 2011-6-30 12:12:12 2011-08-10 12:5:12 2011-08-10 13:6:15
10001 paul 2011-6-30 12:12:12 2011-08-10 12:5:12 2011-08-10 13:6:15
10002 csdn 2011-8-31 12:13:14 2011-08-10 13:15:08 2011-08-10 13:15:15
10002 csdn 2011-8-31 12:13:14 2011-08-10 13:15:08 2011-08-10 13:15:15
10001 paul 2011-6-30 12:12:12 2011-08-10 12:5:12 2011-08-10 13:6:15
(select min(paytime) from [冲值表] b where a.account_id=b.account_id) as paytime,
(select min(paytime) from [冲值表2] c where a.account_id=c.account_id) as mypaytime,
from [注册用户表] a
10002 csdn 2011-8-31 12:13:14 2011-08-10 13:15:08 2011-08-10 13:15:15 10001 paul 2011-6-30 12:12:12 2011-08-10 12:5:12 2011-08-10 13:6:15
任何一种排序时间都是以倒序排列的.
from [注册用户表] a
select a.account_id,a.inserttime,
(select max(paytime) from [冲值表] b where a.account_id=b.account_id) as paytime,
(select max(paytime) from [冲值表2] c where a.account_id=c.account_id) as mypaytime
from [注册用户表] a
order by inserttime desc
select a.account_id,a.inserttime,
(select min(paytime) from (select paytime,account_id from [冲值表]
union select mypaytime,account_id from [冲值表2]) b where a.account_id=b.account_id) as first_paytime,
(select max(paytime) from (select paytime,account_id from [冲值表]
union select mypaytime,account_id from [冲值表2]) c where a.account_id=c.account_id) as last_paytime
from [注册用户表] a
order by inserttime desc
case when b.paytime>c.mpaytime then c.mpaytime
when b.paytime<=c.mpaytime then b.paytime end as min_pay_time
from [注册用户表] a left join [冲值表] b on a.account_id=b.account_id
left join [冲值表2] c on a.account_id=c.account_id
order by 2,5
在后面跟着 and first_paytime<='' and first_paytime >'' 不行噢
select aa.* from
(select a.account_id,a.inserttime,
(select min(paytime) from (select paytime,account_id from [冲值表]
union select mypaytime,account_id from [冲值表2]) b where a.account_id=b.account_id) as first_paytime,
(select max(paytime) from (select paytime,account_id from [冲值表]
union select mypaytime,account_id from [冲值表2]) c where a.account_id=c.account_id) as last_paytime
from [注册用户表] a
) aa
where aa.first_paytime<='' and aafirst_paytime >''
order by aa.inserttime desc