高分求一个SQL 语句---高手进

[姓庄滴]

有一个表如下:

pid fip ftime

aa 192.168.0.1 2006-12-05
aa 192.168.0.1 2006-12-05
aa 192.168.0.2 2006-12-05
bb 192.168.0.2 2006-12-05
bb 192.168.0.1 2006-12-05
bb 192.168.0.1 2006-12-05

计算:
2006-12-05 这天,各pid分别有多少个fip

它的结果,应该是这样的:

ftime pid nn

2006-12-05 aa 2
2006-12-05 bb 2


请问,它的SQL怎么写?

请给完整的SQL语句,谢谢各们大哥!~

[子陌红尘]

select ftime,pid,count(distinct fip) as nn from 表 group by ftime,pid

[姓庄滴]

补充一下
2006-12-05 这天,各pid分别有多少个fip (fip重复的不算)

[子陌红尘]

declare @t table(pid varchar(10),fip varchar(20),ftime varchar(10))
insert into @t select 'aa','192.168.0.1','2006-12-05'
insert into @t select 'aa','192.168.0.1','2006-12-05'
insert into @t select 'aa','192.168.0.2','2006-12-05'
insert into @t select 'bb','192.168.0.2','2006-12-05'
insert into @t select 'bb','192.168.0.1','2006-12-05'
insert into @t select 'bb','192.168.0.1','2006-12-05'


select ftime,pid,count(distinct fip) as nn from @t group by ftime,pid

/*
ftime pid nn
---------- ---------- -----------
2006-12-05 aa 2
2006-12-05 bb 2
*/

[bugchen888]

SELECT ftime,pid,COUNT(*) AS nn
FROM table1
WHERE DATEDIFF (day,ftime,'2006-12-05')=0
GROUP BY pid

[bugchen888]

或者
SELECT ftime,pid,COUNT(DISTINCT fid) AS nn
FROM table1
WHERE DATEDIFF (day,ftime,'2006-12-05')=0
GROUP BY pid

[姓庄滴]

我是楼主!

还有,记得要加 日期判断条件

[子陌红尘]

declare @t table(pid varchar(10),fip varchar(20),ftime varchar(10))
insert into @t select 'aa','192.168.0.1','2006-12-05'
insert into @t select 'aa','192.168.0.1','2006-12-05'
insert into @t select 'aa','192.168.0.2','2006-12-05'
insert into @t select 'bb','192.168.0.2','2006-12-05'
insert into @t select 'bb','192.168.0.1','2006-12-05'
insert into @t select 'bb','192.168.0.1','2006-12-05'


select ftime,pid,count(distinct fip) as nn from @t where ftime='2006-12-05' group by ftime,pid

/*
ftime pid nn
---------- ---------- -----------
2006-12-05 aa 2
2006-12-05 bb 2
*/

[lovcal]

补充一下
2006-12-05 这天,各pid分别有多少个fip (fip重复的不算)
-----------------


select pid,count(distinct fip) as nn from 表
where ftime='2006-12-05'
group by pid

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
pid varchar(20),
fip varchar(20),
ftime datetime
)

insert into tb(pid,fip,ftime) values('aa', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('aa', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('aa', '192.168.0.2', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.2', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.1', '2006-12-05')

select convert(varchar(10),ftime,120) as ftime, pid , count(distinct fip) as nn
from tb
where convert(varchar(10),ftime,120) = '2006-12-05'
group by convert(varchar(10),ftime,120) , pid
drop table tb

ftime pid nn
---------- -------------------- -----------
2006-12-05 aa 2
2006-12-05 bb 2

（所影响的行数为 2 行）

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
pid varchar(20),
fip varchar(20),
ftime datetime
)

insert into tb(pid,fip,ftime) values('aa', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('aa', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('aa', '192.168.0.2', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.2', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.1', '2006-12-05')
insert into tb(pid,fip,ftime) values('bb', '192.168.0.1', '2006-12-05')
go
declare @mydatetime as datetime
set @mydatetime = '2006-12-05'
select convert(varchar(10),ftime,120) as ftime, pid , count(distinct fip) as nn
from tb
where convert(varchar(10),ftime,120) = @mydatetime
group by convert(varchar(10),ftime,120) , pid
drop table tb

ftime pid nn
---------- -------------------- -----------
2006-12-05 aa 2
2006-12-05 bb 2

（所影响的行数为 2 行）

[marco08]

create table T1(pid char(2), fip varchar(20), ftime datetime)
insert T1 select 'aa', '192.168.0.1', '2006-12-05'
union all select 'aa', '192.168.0.1', '2006-12-05'
union all select 'aa', '192.168.0.2', '2006-12-05'
union all select 'bb', '192.168.0.2', '2006-12-05'
union all select 'bb', '192.168.0.1', '2006-12-05'
union all select 'bb', '192.168.0.1', '2006-12-05'


select convert(char(10), ftime, 120) as ftime, pid, count(distinct fip) as nn
from T1
group by
convert(char(10), ftime, 120), pid

--result
ftime pid nn
---------- ---- -----------
2006-12-05 aa 2
2006-12-05 bb 2

(2 row(s) affected)

[marco08]

--加日期判断
select convert(char(10), ftime, 120) as ftime, pid, count(distinct fip) as nn
from T1
where convert(char(10), ftime, 120)='2006-12-05'
group by
convert(char(10), ftime, 120), pid

[姓庄滴]

感谢大虾们帮忙.采用你们的方式,终于解决了问题!~~

人人有分.