cMain (主表)
------------------------------
ClientID ClientName
------------------------------
0001 sa
0002 li
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在线等待,先谢过大家了:)
SELECT ClientID, ClientName
FROM cMain INNER JOIN cMoney
ON cMain.ClientID = cMoney.ClientID
WHERE DATEDIFF(day,EndDate,当前日期)< 42.
SELECT ClientID, ClientName
FROM cMain INNER JOIN cMoney
ON cMain.ClientID = cMoney.ClientID
WHERE Money<100
3.
SELECT ClientID, ClientName
FROM cMain INNER JOIN cMoney
ON cMain.ClientID = cMoney.ClientID
WHERE DATEDIFF(day,EndDate,当前日期)>89 AND DATEDIFF(day,LastDate,当前日期)<181
select a.ClientID,a.ClientName from cMain a,cMoney b
where a.ClientID=b.ClientID and datediff(day,EndDate,getdate())<=3
select a.ClientID,a.ClientName from cMain a,cMoney b
where a.ClientID=b.ClientID and 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 datediff(day,b.LastDate,getdate())between 90 and 180
from (select ClientID from cMoney where abs(datediff(d,enddate,getdate()))<=3 )a
join cMain c on a.ClientID =c.ClientID
--
select c.*
from (select ClientID from cMoney where Money<100 )a
join cMain c on a.ClientID =c.ClientID
--
select c.*
from (select ClientID from cMoney where Money<100 )a
join cMain c on a.ClientID =c.ClientID
--
select c.*
from (select ClientID from cMoney where Money<100 )a
join cMain c on a.ClientID =c.ClientID
我回复怎么重复了那么多条阿?1--select distinct c.*
from (select ClientID from cMoney where abs(datediff(d,enddate,getdate()))<=3 )a
join cMain c on a.ClientID =c.ClientID2
--
select distinct c.*
from (select ClientID from cMoney where Money<100 )a
join cMain c on a.ClientID =c.ClientID3select 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 cMain.ClientID, ClientName
stevenk2003 和 gc_ding 的方法会产生的重复的名单,重复的原因来自于 cMoney xiaoku 的方法很不错!谢谢!
mack
今天才看到你的短息~~~sorry~~
以后共同学习