求sql统计查询语句,该如何解决

求sql统计查询语句
ID FBILLNO num fdate
1 lxm12010001 12 2012-1-1
1 lxm12010001 13 2012-1-1
1 lxm12010001 10 2012-1-1
2 lxm12010002 50 2012-1-5
2 lxm12010002 60 2012-1-5
3 lxm12010003 10 2012-1-6
4 lxm12010004 10 2012-1-7

　　　 id、fbillno、fdate字段都是相对应的，我要查询出fbillno 字段大于2的记录显示出来
希望通过一条查询得到如下结果：
　　　ID FBILLNO num fdate
1 lxm12010001 12 2012-1-1
1 lxm12010001 13 2012-1-1
1 lxm12010001 10 2012-1-1
2 lxm12010002 50 2012-1-5
2 lxm12010002 60 2012-1-5

------解决方案--------------------

SQL code


create table tb
(
  ID int,
  FBILLNO varchar(20),
  num int ,
  fdate varchar(10)
)

insert into tb
select 1,'lxm12010001', 12, '2012-1-1' union all
select 1,'lxm12010001', 13, '2012-1-1' union all  
select 1,'lxm12010001', 10, '2012-1-1' union all
select 2,'lxm12010002', 50, '2012-1-5' union all
select 2,'lxm12010002', 60, '2012-1-5' union all
select 3,'lxm12010003', 10, '2012-1-6' union all
select 4,'lxm12010004', 10, '2012-1-7'

Select * From tb Where FBILLNO In (Select FBILLNO From tb Group By FBILLNO Having Count(*)>1)

ID          FBILLNO              num         fdate
----------- -------------------- ----------- ----------
1           lxm12010001          12          2012-1-1
1           lxm12010001          13          2012-1-1
1           lxm12010001          10          2012-1-1
2           lxm12010002          50          2012-1-5
2           lxm12010002          60          2012-1-5

------解决方案--------------------

SQL code

 select ID,FBILLNO,NUM,fdate from (
Select *,(select COUNT(1) from tb where   FBILLNO=t.FBILLNO   )as co From tb   t 
 )s   where  co>1

求sql统计查询语句,该如何解决

相关推荐