SQL:选择组中包含的行
I'm working on a query for SQL Server and I'm wondering if anyone can give me some tips on selecting individual rows that make up a group (where the group's based on an aggregate function, COUNT in this case)
我正在研究SQL Server的查询,我想知道是否有人可以给我一些关于选择构成组的单个行的提示(其中组基于聚合函数,在这种情况下为COUNT)
So, as a simplified example, if I have a table of billings, as below, I want to select all billings for each client where they have 2 or more billings after a certain date.
因此,作为一个简化的例子,如果我有一个账单表,如下所示,我想为每个客户选择在特定日期之后有2个或更多账单的所有账单。
ClaimID ClaimDate ClientName
101 May 5, 2010 Jim
102 June 19, 2010 Jim
103 August 5, 2008 Jim
104 January 1, 2011 Mary
105 May 8, 2009 Mary
106 November 4, 2010 Mary
107 October 6, 2010 Mary
108 April 4, 2010 Bob
109 April 29, 2009 Bob
110 July 7, 2006 Joe
So if I execute
所以,如果我执行
SELECT ClientName, COUNT(ClaimID) FROM Billings
WHERE ClaimDate > '2010'
Group By ClientName
Having COUNT(ClaimID) > 1
I'd get:
我得到:
Jim 2
Mary 3
Which is good, it finds all clients who have 2 or more billings in the time frame, but I want to actually list what those billings are. So I want this:
哪个好,它找到所有在时间范围内有2个或更多账单的客户,但我想列出那些账单的内容。所以我想要这个:
ClaimID ClientName Count
101 Jim 2
102 Jim 2
104 Mary 3
106 Mary 3
107 Mary 3
What do you think is the best way to accomplish this?
你认为最好的方法是什么?
Thanks.
谢谢。
3 个解决方案
#1
9
You join that back to the main table.
您将其加入主表。
SELECT B.ClaimID, B.ClaimDate, B.ClientName, G.ClaimCount
FROM
(
SELECT ClientName, COUNT(ClaimID) ClaimCount
FROM Billings
WHERE ClaimDate > '2010'
Group By ClientName
Having COUNT(ClaimID) > 1
) G
INNER JOIN Billings B on B.ClientName = G.ClientName
WHERE B.ClaimDate > '2010'
更多相关文章
- PL\SQL 客户端配置 windows 64 ORACLE 提示:无法检测到对应的数
- MySQL图形界面客户端
- 直播技术(从服务端到客户端)一
- Android Studio精彩案例(一)《ActionBar和 ViewPager版仿网易新
- C# PC客户端与Android服务端的Socket同步通信(USB)
- 新浪微博开放平台开发-android客户端(1)
- 4.腾讯微博Android客户端开发——获取未授权的Request Token
- android OSChina 客户端源代码剖析
- 虎扑体育客户端zen源码学习笔记