sql语句的问题

[wangjinchang]

先有两张表，结构如下：
table1
id ip time
table2
id ipnum time
我想把table1中相同ip在同一天内的数量插入到table2

如下：
table1
id ip time
0 11.11 2005-02-13
1 22.33 2005-02-13
2 11.11 2005-02-13
3 22.33 2005-02-13

table2
id ip ipnum time
0 11.11 2 2005-02-13
1 22.33 2 2005-02-13

请问SQL语句如何实现

[liangCK]

select ip,time,count(*)
from tb1
group by ip,time

[让你望见影子的墙]

insert into tb2
select min(id),ip,count(*),time
from tb1
group by ip,time

[sgtzzc]

update t2
set t2.ipnum=t2.ipnum+isnull(t1.cnt,0)
from 
  table2 t2
left join
  (select ip,convert(varchar(10),[time],120) as [time],count(1) as cnt from table1 group by ip,convert(varchar(10),[time],120)) t1
on
  t1.ip=t2.ip and t1.[time]=convert(varchar(10),t2.[time],120) 

[wangjinchang]

[Quote=引用 1 楼 liangck 的回复:]
select ip,time,count(*)
from tb1
group by ip,time
[/Quote]


不对！

[Rotel-刘志东]

insert into tb2
select min(id),ip,count(*),time from tb1
group by ip,time

[nianran520]

--> 测试数据：table1
if object_id ('table1') is not null drop table table1 
create table table1([id] int,[ip] numeric(4,2),[time] varchar(10))
insert table1
select 0,11.11,'2005-02-13' union all
select 1,22.33,'2005-02-13' union all
select 2,11.11,'2005-02-13' union all
select 3,22.33,'2005-02-13'

--> 测试数据：table2
if object_id ('table2') is not null drop table table2 
create table table2([id] int,[ip] numeric(4,2),[ipnum] int,[time] varchar(10))

insert into table2
select min(id),ip,count(1),time
from table1
group by ip,time

select * from table2
--结果
----------------------
0	11.11	2	2005-02-13
1	22.33	2	2005-02-13

[dawugui]

insert into table2 select min(id) , ip , count(1) , time from table1 group by ip , time

[dawugui]

create table table1 (id int, ip varchar(15), time varchar(10))
insert into table1 values(0  ,  '11.11' ,     '2005-02-13') 
insert into table1 values(1  ,  '22.33' ,     '2005-02-13') 
insert into table1 values(2  ,  '11.11' ,     '2005-02-13') 
insert into table1 values(3  ,  '22.33' ,     '2005-02-13') 
create table table2(id int,ip varchar(10), ipnum int,time varchar(10))
go

insert into table2 select min(id) , ip , count(1) , time from table1 group by ip , time

select * from table2

drop table table1, table2

/*
id          ip         ipnum       time       
----------- ---------- ----------- ---------- 
0           11.11      2           2005-02-13
1           22.33      2           2005-02-13

（所影响的行数为 2 行）

*/

[wangjinchang]

id 是自动增长列啊。老大

[dawugui]

[Quote=引用 9 楼 wangjinchang 的回复:]
id 是自动增长列啊。老大
[/Quote]

insert into table2(ip,ipnum,time ) select ip , count(1) , time from table1 group by ip , time

[renadg]

count(1) 是什么意思....
麻烦解释一下....thank you...

[wangjinchang]

问题已经解决，就差给分了。说能告诉我count(1)是什么意思？我把分全给你

[hzvcan]

路过

[icelovey]

COUNT(1)   功能同于   COUNT(*),COUNT(ID)   --如果ID建了聚集索引的话(指性能上)   
  这里意思是   按   a   分组后，每组的个数.   
  HAVING   COUNT(1)>3   指此组的个数要大于3才被选出  

[icelovey]

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

[hzvcan]

Quote=引用 9 楼 wangjinchang 的回复:]
id 是自动增长列啊。老大
[/Quote]

create table table1 (id int, ip varchar(15), time varchar(10))
insert into table1 values(0  ,  '11.11' ,     '2005-02-13') 
insert into table1 values(1  ,  '22.33' ,     '2005-02-13') 
insert into table1 values(2  ,  '11.11' ,     '2005-02-13') 
insert into table1 values(3  ,  '22.33' ,     '2005-02-13') 
create table table2(id int identity(1,1),ip varchar(10), ipnum int,time varchar(10))



insert into table2(ip,ipnum,time)
select ip,count(ip),time as time from table1 group by ip,time

count(1)应该就是统计的意思，和count(ip),count(time),count(*)作用一样。

[syw_java]

insert into table2(ip,[time],[count])
select ip,[time],count(*) from table1 group by ip, [time]

[wangjinchang]

[Quote=引用 10 楼 dawugui 的回复:]
引用 9 楼 wangjinchang 的回复:
id 是自动增长列啊。老大


SQL codeinsertinto table2(ip,ipnum,time )select ip ,count(1) , timefrom table1groupby ip , time
[/Quote]

count(1)是什么意思？