SQL Server 2000 查询

发布于 2024-12-05 06:49:19 字数 935 浏览 2 评论 0原文

我有一个创建的表格，其中包含来自几个用户的多个记录，因此我可以模拟情况。

我创建了以下查询：

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a, calls b , login c 
WHERE a.callid = b.jid 
  AND a.muserid = c.loginid 
  AND b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12 
ORDER BY 
    cel_time ASC

并结果得到以下查询，

545 92  2   hello1  2   2011-09-18 16:32:17.000 phil01  21
546 92  1   hello2  2   2011-09-18 16:42:38.000 phil01  21
547 92  2   hello3  2   2011-09-18 16:59:08.000 danny   16
548 92  1   hello4  2   2011-09-18 20:46:21.000 phil01  21
549 92  1   hello5  2   2011-09-18 20:47:16.000 phil01  21  
550 92  1   hello6  2   2011-09-19 19:32:15.000 phil01  21  
551 92  1   hello7  2   2011-09-19 19:34:14.000 phil01  21

但实际上我希望此结果在muserid上有所不同，并仅返回只返回两个行。

我研究了独特的价值描述，但似乎无法实现这一目标。

我将如何完成？

原文

I have a created a few tables containing multiple records from several users so I can simulate circumstances.

I created the following query:

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a, calls b , login c 
WHERE a.callid = b.jid 
  AND a.muserid = c.loginid 
  AND b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12 
ORDER BY 
    cel_time ASC

and got the following as result

545 92  2   hello1  2   2011-09-18 16:32:17.000 phil01  21
546 92  1   hello2  2   2011-09-18 16:42:38.000 phil01  21
547 92  2   hello3  2   2011-09-18 16:59:08.000 danny   16
548 92  1   hello4  2   2011-09-18 20:46:21.000 phil01  21
549 92  1   hello5  2   2011-09-18 20:47:16.000 phil01  21  
550 92  1   hello6  2   2011-09-19 19:32:15.000 phil01  21  
551 92  1   hello7  2   2011-09-19 19:34:14.000 phil01  21

but I actually want this result to be distinct on muserid and return only return two rows.

I have studied distinct value description but can not seem to get this accomplished.

How would I accomplish this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

み零 2024-12-12 06:49:19

使用此SQL：

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a
JOIN calls b ON a.callid = b.jid 
JOIN login c ON a.muserid = c.loginid 
JOIN 
  (SELECT l2.muserid, MAX(l2.cel_time) as max_time 
   FROM level2 l2 
   GROUP BY l2.muserid) d ON (d.muserid = a.muserid AND a.cel_time = d.max_time)
WHERE b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12

Use this SQL:

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a
JOIN calls b ON a.callid = b.jid 
JOIN login c ON a.muserid = c.loginid 
JOIN 
  (SELECT l2.muserid, MAX(l2.cel_time) as max_time 
   FROM level2 l2 
   GROUP BY l2.muserid) d ON (d.muserid = a.muserid AND a.cel_time = d.max_time)
WHERE b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12

回复收藏 0 原文

~没有更多了~