初学者求SQL代码怎么写，仅需几行而已

Question

现有一个表名为table1，列名分别是 交易日期，客户名称，交易柜员，xxxx等等30个字段，现需要筛选出单个客户 同一交易日期通过同一交易柜员办理业务超过5笔的所有记录，包括每笔交易明细的所有记录，求指点！！！谢谢

Answer 1

create table #t
(
	tdate	datetime,
	account	varchar(100),
	counter	varchar(100)
)

insert into #t
select '2017-4-1','A1','C1' UNION ALL
select '2017-4-1','A1','C1' UNION ALL
select '2017-4-1','A1','C1' UNION ALL
select '2017-4-2','A1','C1' UNION ALL
select '2017-4-2','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' UNION ALL
select '2017-4-3','A1','C1' 

select * from #t t1,(
select tdate,account,counter,count(*) as cnt from #t 
group by tdate,account,counter
having count(*)>5
) t2
where t1.account=t2.account and t1.counter=t2.counter and t1.tdate=t2.tdate

---

family as water

Answer 2

你好，

你也可以参考下面的代码。

;WITH CTE AS
(
	SELECT *
		, COUNT(1) OVER (PARTITION BY [交易日期], [客户名称], [交易柜员]) AS [cnt]
	FROM table1
)
SELECT *
FROM CTE
WHERE [cnt] > 5

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].