求一条查询汇总的SQL语句，很急急急急，在线等

[g505149841]

有如下一张表
badge cardid term inout mache
IHH101611 0009013528 2010-06-10 08:17:00 0 IHH-GATEWAY-04
IHH101611 0009013528 2010-06-10 08:17:00 0 IHH-GATEWAY-04
IHH102258 0015746851 2010-06-10 08:17:00 0 IHH-GATEWAY-04
IHH102258 0015746851 2010-06-10 09:18:00 1 IHH-GATEWAY-04
IHH102842 0002114050 2010-06-10 08:18:00 0 IHH-GATEWAY-04
IHH102579 0002114403 2010-06-13 08:34:00 1 IHH-GATEWAY-04
IHH102579 0002114403 2010-06-10 08:18:00 0 IHH-GATEWAY-04
IHH102579 0002114403 2010-06-10 20:18:00 1 IHH-GATEWAY-04

现在想去掉里面所有内容相同的行，然后再算出字段badge内容相同的行的总数，要求得出的表如下
badge cardid term inout mache totle
IHH101611 0009013528 2010-06-10 08:17:00 0 IHH-GATEWAY-04 1
IHH102258 0015746851 2010-06-10 08:17:00 0 IHH-GATEWAY-04 2
IHH102258 0015746851 2010-06-10 09:18:00 1 IHH-GATEWAY-04 2
IHH102842 0002114050 2010-06-10 08:18:00 0 IHH-GATEWAY-04 1
IHH102579 0002114403 2010-06-13 08:34:00 1 IHH-GATEWAY-04 3
IHH102579 0002114403 2010-06-10 08:18:00 0 IHH-GATEWAY-04 3
IHH102579 0002114403 2010-06-10 20:18:00 1 IHH-GATEWAY-04 3

[dawugui]

select badge ,cardid, term ,inout ,mache, count(1) totle
from tb
group by badge ,cardid, term ,inout ,mache

[dawugui]

select badge ,cardid, term ,inout ,mache, count(1) totle
from (select distinct * from tb) t
group by badge ,cardid, term ,inout ,mache

[--小F--]

select
 badge ,cardid, term ,inout ,mache, count(1) as totle
from
 (select distinct * from tb) t
group by
 badge ,cardid, term ,inout ,mache

[dawugui]

create table tb(badge varchar(20),cardid varchar(20),term datetime,inout int,mache varchar(20))
insert into tb values('IHH101611', '0009013528', '2010-06-10 08:17:00', 0 ,'IHH-GATEWAY-04')
insert into tb values('IHH101611', '0009013528', '2010-06-10 08:17:00', 0 ,'IHH-GATEWAY-04')
insert into tb values('IHH102258', '0015746851', '2010-06-10 08:17:00', 0 ,'IHH-GATEWAY-04')
insert into tb values('IHH102258', '0015746851', '2010-06-10 09:18:00', 1 ,'IHH-GATEWAY-04')
insert into tb values('IHH102842', '0002114050', '2010-06-10 08:18:00', 0 ,'IHH-GATEWAY-04')
insert into tb values('IHH102579', '0002114403', '2010-06-13 08:34:00', 1 ,'IHH-GATEWAY-04')
insert into tb values('IHH102579', '0002114403', '2010-06-10 08:18:00', 0 ,'IHH-GATEWAY-04')
insert into tb values('IHH102579', '0002114403', '2010-06-10 20:18:00', 1 ,'IHH-GATEWAY-04')
go

select m.*, (select count(1) from 
(select distinct * from tb) n where n.badge = m.badge) totle
from (select distinct * from tb) m
order by badge

drop table tb

/*
badge                cardid               term                                                   inout       mache                totle       
-------------------- -------------------- ------------------------------------------------------ ----------- -------------------- ----------- 
IHH101611            0009013528           2010-06-10 08:17:00.000                                0           IHH-GATEWAY-04       1
IHH102258            0015746851           2010-06-10 08:17:00.000                                0           IHH-GATEWAY-04       2
IHH102258            0015746851           2010-06-10 09:18:00.000                                1           IHH-GATEWAY-04       2
IHH102579            0002114403           2010-06-10 08:18:00.000                                0           IHH-GATEWAY-04       3
IHH102579            0002114403           2010-06-10 20:18:00.000                                1           IHH-GATEWAY-04       3
IHH102579            0002114403           2010-06-13 08:34:00.000                                1           IHH-GATEWAY-04       3
IHH102842            0002114050           2010-06-10 08:18:00.000                                0           IHH-GATEWAY-04       1

（所影响的行数为 7 行）
*/

[g505149841]

感谢dawugui的解答，但我希望只用一张表，就实现这个功能，不知道有没有办法

[dawugui]

[Quote=引用 5 楼 g505149841 的回复:]
感谢dawugui的解答，但我希望只用一张表，就实现这个功能，不知道有没有办法
[/Quote]
那就是一张表,只不过用了子查询而已.

[g505149841]

非常感谢dawugui，问题解决了，给分

[g505149841]

[Quote=引用 6 楼 dawugui 的回复:]
引用 5 楼 g505149841 的回复:
感谢dawugui的解答，但我希望只用一张表，就实现这个功能，不知道有没有办法

那就是一张表,只不过用了子查询而已.
[/Quote]

请问我还想从别的表中加2个字段进来，怎么加啊，如a表中的name，c变中的age，这三张表中都有badge字段
，还请帮下忙，谢谢。。