呵呵 厉害,小弟的问题仍然没有解决,现把我的语句贴上select * from table_feeinfo_log t1 where l_Handset not in(Select l_handset from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) and datediff(month, l_datetime, getdate()) = 0 order by l_datetime此条语句实现的是,从表中取出当月的,并且字段l_Handset和l_servicetype重复3条以上过滤后的结果 并且,需要过滤的结果不显示我就是要从这些结果里面再次过滤,把字段l_Handset和l_servicetype重复的数据只显示其中的一条,以避免取出重复的l_Handset导致出现其他问题,各位麻烦了
你的语句本身有问题。你想排除的是l_handset和l_servicetype重复超过三条的数据,结果确是排除了l_handset可能重复没有达到3条的数据,举个例子 l_handset和l_servicetype 1 2 1 2 1 2 1 1 这句话连那个l_servicetype=1的也会被排除 select * from table_feeinfo_log t1 where l_Handset not exists (select * from (Select l_handset,servicetype from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) t2 where t1.l_handset = t2.l_handset and t1.l_servicetype = t2.l_serivcetype ) and datediff(month, l_datetime, getdate()) = 0 order by l_datetime
-------------------------------- select a, b, min(c), min(d) from tbl group by a, b having count(*) >= 1 -------------------------------- 其中 a, b 是重复字段.试一下
select a, b, min(c), min(d), min(....) from tbl group by a, b having count(*) = 1
a c c d... --------------1, 1, 1 1, 1, 2 1, 1, 3 1, 2, 1 1, 2, 3 2, 2, 2 2, 2, 3 3, 3, 3 -----------------如果是 having count(*) >= 1 则结果如下 a b c ------------------------------------- 1 1 1 1 2 1 2 2 2如果是 having count(*) = 1 则结果如下: a b c -------------------------------------- 3 3 3能不能满足要求?
终于解决了 小弟的语句是这么写的select * from table_feeinfo_log where l_ID in (select min(l_ID) from table_feeinfo_log group by l_handset,l_servicetype) and l_ID not in(Select min(l_ID) from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) and datediff(month, l_datetime, getdate()) = 0 order by l_Handset最后实现的功能为,比如有10条数据,l_ID l_Handset l_servicetype l_datetime 1 1000000 ax 2005-1-1 0:0:0 2 1000000 ax 2005-1-1 0:0:0 3 1000000 ax 2005-1-1 0:0:0 4 1000000 gx 2005-1-1 0:0:0 5 2000000 bx 2005-1-1 0:0:0 6 2000000 bx 2005-1-1 0:0:0 7 2000000 ex 2005-1-1 0:0:0 8 3000000 cx 2005-1-1 0:0:0 9 3000000 fx 2005-1-1 0:0:0 10 4000000 dx 2005-1-1 0:0:0执行的结果为 l_ID l_Handset l_servicetype l_datetime 4 1000000 gx 2005-1-1 0:0:0 5 2000000 bx 2005-1-1 0:0:0 7 2000000 ex 2005-1-1 0:0:0 8 3000000 cx 2005-1-1 0:0:0 9 3000000 fx 2005-1-1 0:0:0 10 4000000 dx 2005-1-1 0:0:0 谢谢大家的帮忙
T.*
from
table T,
(select a,b,max(c) as c from table group by a,b) V
where
T.a = V.a and T.b = V.b and T.c = V.c
---如果a,b重复则显示第一条
select * from #t
where id not in (select min(id) from #t group by a,b
如果不是,可能就得不到楼主的结果
楼主可以根据实际情况,沿着这个思路做
厉害,小弟的问题仍然没有解决,现把我的语句贴上select * from table_feeinfo_log t1 where l_Handset not in(Select l_handset from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) and datediff(month, l_datetime, getdate()) = 0 order by l_datetime此条语句实现的是,从表中取出当月的,并且字段l_Handset和l_servicetype重复3条以上过滤后的结果
并且,需要过滤的结果不显示我就是要从这些结果里面再次过滤,把字段l_Handset和l_servicetype重复的数据只显示其中的一条,以避免取出重复的l_Handset导致出现其他问题,各位麻烦了
l_handset和l_servicetype
1 2
1 2
1 2
1 1
这句话连那个l_servicetype=1的也会被排除
select *
from table_feeinfo_log t1
where l_Handset not exists (select * from (Select l_handset,servicetype from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) t2
where t1.l_handset = t2.l_handset and t1.l_servicetype = t2.l_serivcetype ) and datediff(month, l_datetime, getdate()) = 0 order by l_datetime
select a, b, min(c), min(d)
from tbl
group by a, b
having count(*) >= 1
--------------------------------
其中 a, b 是重复字段.试一下
from tbl
group by a, b
having count(*) = 1
a c c d...
--------------1, 1, 1
1, 1, 2
1, 1, 3
1, 2, 1
1, 2, 3
2, 2, 2
2, 2, 3
3, 3, 3
-----------------如果是 having count(*) >= 1 则结果如下
a b c
-------------------------------------
1 1 1
1 2 1
2 2 2如果是 having count(*) = 1 则结果如下:
a b c
--------------------------------------
3 3 3能不能满足要求?
小弟的语句是这么写的select * from table_feeinfo_log where l_ID in (select min(l_ID) from table_feeinfo_log group by l_handset,l_servicetype) and
l_ID not in(Select min(l_ID) from table_feeinfo_log group by l_handset,l_servicetype having count(1)>2) and
datediff(month, l_datetime, getdate()) = 0 order by l_Handset最后实现的功能为,比如有10条数据,l_ID l_Handset l_servicetype l_datetime
1 1000000 ax 2005-1-1 0:0:0
2 1000000 ax 2005-1-1 0:0:0
3 1000000 ax 2005-1-1 0:0:0
4 1000000 gx 2005-1-1 0:0:0
5 2000000 bx 2005-1-1 0:0:0
6 2000000 bx 2005-1-1 0:0:0
7 2000000 ex 2005-1-1 0:0:0
8 3000000 cx 2005-1-1 0:0:0
9 3000000 fx 2005-1-1 0:0:0
10 4000000 dx 2005-1-1 0:0:0执行的结果为
l_ID l_Handset l_servicetype l_datetime
4 1000000 gx 2005-1-1 0:0:0
5 2000000 bx 2005-1-1 0:0:0
7 2000000 ex 2005-1-1 0:0:0
8 3000000 cx 2005-1-1 0:0:0
9 3000000 fx 2005-1-1 0:0:0
10 4000000 dx 2005-1-1 0:0:0
谢谢大家的帮忙