表结构:
客户表account,联系人表contact
字段:
account.account(客户名称),contact.createdate(联系人创建时间)
关联:
account.accountid=contact.accountid
关系:
account:contact=1对多(一个客户对应多个联系人)需求:
用一个sql语句查出所有30天未新建联系人的客户名称及最后一次创建联系人时间。分析:
首先要获得每个客户的最后一次新建联系人的创建时间(contact.createdate),再在这个查询结果中获得创建时间在30天以前的客户id(contact.accountid),最后关联客户表查出客户名称(account.account)和该客户最后一次创建联系人时间(contact.createdate)
客户表account,联系人表contact
字段:
account.account(客户名称),contact.createdate(联系人创建时间)
关联:
account.accountid=contact.accountid
关系:
account:contact=1对多(一个客户对应多个联系人)需求:
用一个sql语句查出所有30天未新建联系人的客户名称及最后一次创建联系人时间。分析:
首先要获得每个客户的最后一次新建联系人的创建时间(contact.createdate),再在这个查询结果中获得创建时间在30天以前的客户id(contact.accountid),最后关联客户表查出客户名称(account.account)和该客户最后一次创建联系人时间(contact.createdate)
Select
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Where Not Exists(Select accountid From contact Where accountid = A.accountid And DateDiff(dd, createdate, GetDate()) <= 30)
Select
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Inner Join
(Select accountid From contact Group By accountid Having DateDiff(dd, Max(createdate), GetDate()) > 30) C
On A.accountid = C.accountid
select accountid, max(createdate) from contact group by accountid--所有30天未新建联系人的客户名称
select * from contact a where not exists (select 1 from contact where accountid = a.accountid and abs(datediff(day, createdate, a.createdate)) <= 30)
select a.account,max(a.createdate) as createdate
from contact a
where not exists(select 1 from contact where accountid = a.accountid and datediff(dd, createdate,'要查询的日期') <= 30)
group by a.account
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Where Not Exists(Select accountid From contact Where accountid = A.accountid And DateDiff(dd, createdate, GetDate()) <= 30)
Group By
A.account--或者Select
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Inner Join
(Select accountid From contact Group By accountid Having DateDiff(dd, Max(createdate), GetDate()) > 30) C
On A.accountid = C.accountid
Group By
A.account
例如:select a.account,max(b.createdate),只能是select a.account,b.createdate多谢各位了!!!!!!!!!
各位,还有一个要求,聚合函数不能出现在select语句中
例如:select a.account,max(b.createdate),只能是select a.account,b.createdate多谢各位了!!!!!!!!!
----------我掉了group by,加上即可
Select
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Where Not Exists(Select accountid From contact Where accountid = A.accountid And DateDiff(dd, createdate, GetDate()) <= 30)
Group By
A.account--或者Select
A.account,
Max(B.createdate) As createdate
From
account A
Inner Join
contact B
On A.accountid = B.accountid
Inner Join
(Select accountid From contact Group By accountid Having DateDiff(dd, Max(createdate), GetDate()) > 30) C
On A.accountid = C.accountid
Group By
A.account
SELECT a.accountid,a.name,b.name,max(b.createdate) FROM account a inner join contact b on a.accountid=b.accountid where datediff(day,b.createdate,getdate())>30 group by a.name
不用MAX,一句话写不出来吧。。偶这个是效率最高的
SELECT a.accountid,a.name,b.name,max(b.createdate) FROM account a inner join contact b on a.accountid=b.accountid where datediff(day,b.createdate,getdate())>30 group by a.name------------------
效率是高,但是邏輯不怎麼正確。注意是最後的創建時間在30天以前的。
这个sql需要在客户端软件中拼出来,客户端软件只能定义where中的语句,select中只能直接选择字段名称,无法加max。所以,还是那个要求,在select中不用MAX,where中可以用,如何实现啊!!
-------------
只能填select和where部分,那能不能給表指定別名?
这个sql需要在客户端软件中拼出来,客户端软件只能定义where中的语句,select中只能直接选择字段名称,无法加max。所以,还是那个要求,在select中不用MAX,where中可以用,如何实现啊!!
----------------這麼寫吧Select
account.account, contact.createdate
From
account,
contact
Where account.accountid = contact.accountid
And account.accountid Not In (Select Distinct accountid From contact Where DateDiff(dd, createdate, GetDate()) <= 30)
And createdate = (Select Max(createdate) From contact Where accountid = account.accountid)在select部分填入account.account, contact.createdate
在表名部分填入account, contact
在where部分填入
account.accountid = contact.accountid
And account.accountid Not In (Select Distinct accountid From contact Where DateDiff(dd, createdate, GetDate()) <= 30)
And createdate = (Select Max(createdate) From contact Where accountid = account.accountid)語句的效率不高,但是能實現你的要求。估計表的別名也是不能用的。你的限制太多了,只好這麼寫。