--简单排序就可以了嘛! Declare @t Table(CardNo Int,Dates Datetime) Insert @t Select 1234,'2006-01-01' Union all Select 2345,'2006-01-01' Union all Select 1234,'2006-03-04' Union all Select 2345,'2006-05-01' Union all Select 5678,'2006-06-01' Union all Select 2345,'2006-05-01' ---Try Select * From @t Order by 1
借用楼上的数据,这样试试: select * from @t a where exists(select 1 from @t where CardNo = a.CardNo group by CardNo having count(*) > 1) order by CardNo,Dates 或 select * from @t a where (select count(*) from @t where CardNo = a.CardNo ) > 1 order by CardNo,Dates
刚才没看原题,断章取义了:) Declare @t Table(CardNo Int,Dates Datetime) Insert @t Select 1234,'2006-01-01' Union all Select 2345,'2006-01-01' Union all Select 1234,'2006-03-04' Union all Select 2345,'2006-05-01' Union all Select 5678,'2006-06-01' Union all Select 2345,'2006-05-01' ---Try Select * From @t A Where Exists ( Select 1 From @t Where A.CardNo=A.CardNo ) Order By 1
谢谢了,我综合了有的朋友建议:先找出所有记录数大于1的卡号 Select 卡号 From ICMoney where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1 order by 卡号 在对这些卡号分别进行再次查询 Select * From ICMoney where 卡号='0215' and 发生日期>='20060520' and 发生日期<='20060620'
可以得出指定时间内,相同卡号记录数大于1的记录,有点复杂,有没办法可以一次找出?
select * from ICMoney where 卡号 in (Select 卡号 From ICMoney where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1 ) where 发生日期>='20060520' and 发生日期<='20060620'
select * from ICMoney where 卡号 in (Select 卡号 From ICMoney where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1 ) and 发生日期>='20060520' and 发生日期<='20060620'
----创建测试数据 Declare @ICMoney Table(卡号 Int,发生日期 Datetime) Insert @ICMoney Select 1234,'2006-06-01' Union all Select 2345,'2006-06-01' Union all Select 1234,'2006-06-04' Union all Select 2345,'2006-06-11' Union all Select 5678,'2006-06-01' Union all Select 2345,'2006-05-01' ----查询 select * from @ICMoney a where 发生日期 between '20060520' and '20060620' and exists(select 1 from @ICMoney where 发生日期 between '20060520' and '20060620' and 卡号 = a.卡号 group by 卡号 having count(*) > 1) order by 卡号,发生日期 --或 select * from @ICMoney a where 发生日期 between '20060520' and '20060620' and (select count(*) from @ICMoney where 发生日期 between '20060520' and '20060620' and 卡号 = a.卡号 ) > 1 order by 卡号,发生日期
From Table1
Group By ICCardNo
Having Count(1)>1
inner join (
select ICCardNo from cheku group by ICCardNo having count(*)>1)b
on a.ICCardNo=b.ICCardNo
例如:
卡号 发生日期
-------------------------
1234 2006-01-01
1234 2006-03-04
2345 2006-01-01
2345 2006-05-01
2345 2006-06-01
.... .....
Declare @t Table(CardNo Int,Dates Datetime)
Insert @t Select 1234,'2006-01-01'
Union all Select 2345,'2006-01-01'
Union all Select 1234,'2006-03-04'
Union all Select 2345,'2006-05-01'
Union all Select 5678,'2006-06-01'
Union all Select 2345,'2006-05-01'
---Try
Select *
From @t
Order by 1
select * from @t a where exists(select 1 from @t where CardNo = a.CardNo group by CardNo having count(*) > 1) order by CardNo,Dates
或
select * from @t a where (select count(*) from @t where CardNo = a.CardNo ) > 1 order by CardNo,Dates
Declare @t Table(CardNo Int,Dates Datetime)
Insert @t Select 1234,'2006-01-01'
Union all Select 2345,'2006-01-01'
Union all Select 1234,'2006-03-04'
Union all Select 2345,'2006-05-01'
Union all Select 5678,'2006-06-01'
Union all Select 2345,'2006-05-01'
---Try
Select *
From @t A
Where Exists
( Select 1 From @t Where A.CardNo=A.CardNo )
Order By 1
Select 卡号 From ICMoney
where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1
order by 卡号 在对这些卡号分别进行再次查询
Select * From ICMoney
where 卡号='0215' and 发生日期>='20060520' and 发生日期<='20060620'
可以得出指定时间内,相同卡号记录数大于1的记录,有点复杂,有没办法可以一次找出?
where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1 )
where 发生日期>='20060520' and 发生日期<='20060620'
where 发生日期>='20060520' and 发生日期<='20060620' group by 卡号 Having Count(1)>1 )
and 发生日期>='20060520' and 发生日期<='20060620'
Declare @ICMoney Table(卡号 Int,发生日期 Datetime)
Insert @ICMoney Select 1234,'2006-06-01'
Union all Select 2345,'2006-06-01'
Union all Select 1234,'2006-06-04'
Union all Select 2345,'2006-06-11'
Union all Select 5678,'2006-06-01'
Union all Select 2345,'2006-05-01'
----查询
select * from @ICMoney a where
发生日期 between '20060520' and '20060620'
and
exists(select 1 from @ICMoney where 发生日期 between '20060520' and '20060620' and 卡号 = a.卡号 group by 卡号 having count(*) > 1) order by 卡号,发生日期
--或
select * from @ICMoney a where
发生日期 between '20060520' and '20060620'
and
(select count(*) from @ICMoney where 发生日期 between '20060520' and '20060620' and 卡号 = a.卡号 ) > 1 order by 卡号,发生日期