请教 类似于银行的 睡眠、小额、到期 帐户语句,在结等待ing
cMain (主表)
------------------------------
ClientIDClientName
------------------------------
0001sa
0002li
0003 ha
cMoney (被关联的表)
------------------------------------------------------
ClientID Type Money EndDate LastDate
------------------------------------------------------
0001 AA 200 2007-12-30 2005-09-30
0001 BB 50 2007-01-24 2007-01-02
0002 AA 0 2008-01-01 2006-08-02
0002 DD 800 2008-01-01 2007-01-30
0003 aa 900 2007-01-25 2006-10-30
0003 cc 120 2009-05-20 2007-01-24
0003 DD 520 2010-03-09 2007-01-08
输出1: 得出 EndDate 距当前日期最近 3 天的名单
------------------------------
ClientID ClientName
------------------------------
0003 ha
0001 sa
输出2: 得出 Money < 100 名单
------------------------------
ClientID ClientName
------------------------------
0002 li
输出3: 得出 LastDate 距当前日期最近 90 ~ 180 天的名单
------------------------------
ClientID ClientName
------------------------------
0002 li
0003 ha
在线等待,先谢过大家了:)
[解决办法]
--------
我回复怎么重复了那么多条阿?
1
--
select distinct c.*
from (select ClientID from cMoney where abs(datediff(d,enddate,getdate())) <=3 )a
join cMain c on a.ClientID =c.ClientID
2
--
select distinct c.*
from (select ClientID from cMoney where Money <100 )a
join cMain c on a.ClientID =c.ClientID
3
select distinct c.*
from (select ClientID from cMoney where abs(datediff(d,LastDate,getdate()))> =90 and abs(datediff(d,LastDate,getdate())) <=180)a
join cMain c on a.ClientID =c.ClientID
[解决办法]
##### 输出的名单不能重复哦!!! #####
------------------------------------------------------
难道cMain (主表)会有重复ClientID和ClientName？如果没有的话，这样就可以了
--输出1
select a.ClientID,a.ClientName from cMain a,cMoney b
where a.ClientID=b.ClientID and abs(datediff(day,b.EndDate,getdate())) <=3
--输出2
select a.ClientID,a.ClientName from cMain a,cMoney b
where a.ClientID=b.ClientID and b.money <100
--输出3
select a.ClientID,a.ClientName from cMain a,cMoney b
where a.ClientID=b.ClientID and abs(datediff(day,b.LastDate,getdate())) between 90 and 180
[解决办法]
SELECT ClientID, ClientName要改为
SELECT cMain.ClientID, ClientName
[解决办法]
mark
