先有两张表,结构如下:
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-13table2
id ip ipnum time
0 11.11 2 2005-02-13
1 22.33 2 2005-02-13请问SQL语句如何实现
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-13table2
id ip ipnum time
0 11.11 2 2005-02-13
1 22.33 2 2005-02-13请问SQL语句如何实现
解决方案 »
- sql2000和sql2005已经可以共存,问题是?
- 联合查询总数怎样使查询结果为一条数据
- 求出每个人的总数
- 数据类型 text 和 varchar 在 equal to 运算符中不兼容。 怎么解决啊
- insert into select
- 在表 s(sname char(8),age int) 中, 怎样选出名字只有两个汉字(一般情况下人名都有三个字)的行?
- 字段值的更改
- 怎样用存储过程实现不定表查询
- 请问使用SQL Server编程,当服务器的数据变化时,客户端如何能够得知变化的内容?
- 怎样写一个存储过程执行只能插入一次数据的操作。
- 如何下载sql server 2005
- 在一个表里对同一条记录根据状态进行查询
from tb1
group by ip,time
select min(id),ip,count(*),time
from tb1
group by ip,time
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)
select min(id),ip,count(*),time from tb1
group by ip,time
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,timeselect * from table2
--结果
----------------------
0 11.11 2 2005-02-13
1 22.33 2 2005-02-13
insert into table2 select min(id) , ip , count(1) , time from table1 group by ip , time
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))
goinsert into table2 select min(id) , ip , count(1) , time from table1 group by ip , timeselect * from table2drop table table1, table2/*
id ip ipnum time
----------- ---------- ----------- ----------
0 11.11 2 2005-02-13
1 22.33 2 2005-02-13(所影响的行数为 2 行)*/
insert into table2(ip,ipnum,time ) select ip , count(1) , time from table1 group by ip , time
麻烦解释一下....thank you...
这里意思是 按 a 分组后,每组的个数.
HAVING COUNT(1)>3 指此组的个数要大于3才被选出
如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?),用count(*),sql会帮你完成优化的
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(*)作用一样。
insert into table2(ip,[time],[count])
select ip,[time],count(*) from table1 group by ip, [time]