求一SQL语句,实在写不出来了

倾斜的水瓶座 2010-10-08 11:57:57
表信息:

表A1:id,cname,userid,depid

表A2:cid,ptime

表A3:cid,gtime

字段说明:
表A1中userid是跟进人的id,depid是部门id

表A2记录每个cname的批注时间,表A3记录每个cname跟进时间

要实现的功能:列出某个部门下的所有cname,userid,ptime,gtime

我自己写了一个SQL语句,但是有重复记录,因为表A2或A3中有多次的批注时间或跟进时间


SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime FROM a1 LEFT OUTER JOIN a2 ON a1.id = a2.compid LEFT OUTER JOIN a3 ON a1.id = a3.compid WHERE (c.depid = 18)


求一SQL语句能达到功能要求
...全文
180 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
「已注销」 2010-10-08
  • 打赏
  • 举报
回复
SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime 
FROM a1
LEFT OUTER JOIN a2
ON (a1.id = a2.compid
and not exists(select 1 from a2 a where a2.cid=a.cid and a2.ptime<a.ptime))
LEFT OUTER JOIN a3
ON (a1.id = a3.compid
and not exists(select 1 from a3 b where a3.cid=b.cid and a3.gtime<b.gtime ))
WHERE c.depid = 18


)

juge001 2010-10-08
  • 打赏
  • 举报
回复
WHERE ( c.depid = 18 )
改成 a.depid = 18
SQLCenter 2010-10-08
  • 打赏
  • 举报
回复
SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime FROM a1 LEFT OUTER JOIN a2 ON a1.id = a2.compid and not exists (select 1 from a2 aa2 where aa2.compid=a2.compid and aa2.ptime>a2.ptime) LEFT OUTER JOIN a3 ON a1.id = a3.compid and not exists (select 1 from a3 aa3 where aa3.compid=a3.compid and aa3.gtime>a3.gtime) WHERE (a1.depid = 18)
SQL2088 2010-10-08
  • 打赏
  • 举报
回复

SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime
FROM a1
LEFT OUTER JOIN a2 ON a1.id = a2.compid
LEFT OUTER JOIN a3 ON a1.id = a3.compid
WHERE (c.depid = 18
and not exists(select 1 from a2 a where a2.cid=a.cid and a2.ptime<a.ptime)
and not exists(select 1 from a3 b where a3.cid=b.cid and a3.gtime<b.gtime )
)
yuanxuegang 2010-10-08
  • 打赏
  • 举报
回复
"因为表A2或A3中有多次的批注时间或跟进时间"
那你要随便选一条?
倾斜的水瓶座 2010-10-08
  • 打赏
  • 举报
回复
郁闷了。搞不定,是不是不能这样整啊
倾斜的水瓶座 2010-10-08
  • 打赏
  • 举报
回复
SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime
FROM a1
LEFT OUTER JOIN a2 ON a1.id = a2.compid
LEFT OUTER JOIN a3 ON a1.id = a3.compid
WHERE (c.depid = 18
and not exists(select 1 from a2 a where a2.cid=a.cid and a2.ptime<a.ptime)
and not exists(select 1 from a3 b where a3.cid=b.cid and a3.gtime<b.gtime )
)


SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime
FROM a1
LEFT OUTER JOIN a2
ON (a1.id = a2.compid
and not exists(select 1 from a2 a where a2.cid=a.cid and a2.ptime<a.ptime))
LEFT OUTER JOIN a3
ON (a1.id = a3.compid
and not exists(select 1 from a3 b where a3.cid=b.cid and a3.gtime<b.gtime ))
WHERE c.depid = 18

可以查询出来正确数据,但是最后一条记录会出现两次。没明白原因是什么
kevn 2010-10-08
  • 打赏
  • 举报
回复
distinct?
wangkun520 2010-10-08
  • 打赏
  • 举报
回复
SELECT a1.id, a1.cname, a1.depid, a1.userid, a2.ptime, a3.gtime
FROM a1
LEFT OUTER JOIN a2
ON (a1.id = a2.compid
and not exists(select 1 from a2 a where a2.cid=a.cid and a2.ptime<a.ptime))
LEFT OUTER JOIN a3
ON (a1.id = a3.compid
and not exists(select 1 from a3 b where a3.cid=b.cid and a3.gtime<b.gtime ))
WHERE c.depid = 18
同意

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧