sql语句的问题。高手帮忙进来看看

[wangjinchang]

现在有两个表：
table1如下：
id ip datetime url
2289 219.143.139.241 2009-11-18 09:32:42.640 VFClassroom
2290 219.143.139.241 2009-11-18 09:32:56.043 KL_room
2291 219.143.139.241 2009-11-19 09:34:28.170 KL_room
2292 219.143.139.241 2009-11-19 09:57:20.750 Index.aspx
2293 219.143.139.241 2009-11-19 09:57:27.670 Index.aspx
2294 219.143.139.241 2009-11-19 09:57:32.327 Task
2295 219.143.139.241 2009-11-19 09:58:38.577 Index.aspx
2296 219.143.139.245 2009-11-19 09:58:51.793 Index.aspx
2297 219.143.139.245 2009-11-19 09:58:58.310 Task
2298 219.143.139.245 2009-11-19 09:59:22.170 Index.aspx
table2如下：
id ip num datetime
1 219.143.139.241 2 2009-11-18
2 219.143.139.241 4 2009-11-19
3 219.143.139.245 3 2009-11-19
table2的数据是来自table1得。插入到table2的规律是，如果同样的IP再同一天内出现了多次。就把出现的次数插入到table2中num字段。也就是说table2中数据得到意思是，某个IP在这一天出现的次数。我的问题是：当table2为空表时，从table1查出数据插入table2我会做。但是，当table2不是空表时，该怎么插入。
比如说table2中已经有2009-11-18这一天IP为219.143.139.241出现的次数为2,这时从table1中查出的数据还有这个IP在这天的记录，那么我们就给改变num的值，有一条就加1,有N条 就加N。
请问，当table2为空和不为空的两种情况下，用一个sql语句怎么实现。

[--小F--]

帮顶

[dawugui]

--1插入，你会了。
--2更新

update table2 
set num = num + isnull((select count(1) from table1 where ip = t.ip and datetime = convert(varchar(10),t.datetime,120)),0)
from table2 t

[icelovey]

这个例子你看看吧， 应该差不多

帐号 姓名 余额
1001 A 500
1002 B 300
1003 C 100
帐号 姓名 上次余额 余额 累计积数
1002 B 100 500 1500
1003 C 300 300 300
更新后的结果是：
帐号 姓名 上次余额 余额 累计积数
1001 A 500 500 500
1002 B 500 300 1800
1003 C 300 100 400

declare @tb1 table([帐号] int,[姓名] varchar(1),[余额] int)
insert @tb1
select 1001,'A',500 union all
select 1002,'B',300 union all
select 1003,'C',100


declare @tb2 table([帐号] int,[姓名] varchar(1),[上次余额] int,[余额] int,[累计积数] int)
insert @tb2
select 1002,'B',100,500,1500 union all
select 1003,'C',300,300,300


update t2
set t2.累计积数 = t2.累计积数 + t1.余额,
    t2.余额 = t1.余额
from @tb1 t1 inner join @tb2 t2 on t1.帐号 = t2.帐号

insert into @tb2(帐号, 姓名, 上次余额, 余额, 累计积数)
select t1.帐号, t1.姓名, t1.余额, t1.余额, t1.余额
from @tb1 t1 left join @tb2 t2 on t1.帐号 = t2.帐号
where t2.帐号 is null

select * from @tb2 order by 帐号


/*
帐号          姓名   上次余额        余额          累计积数        
----------- ---- ----------- ----------- ----------- 
1001        A    500         500         500
1002        B    100         300         1800
1003        C    300         100         400

（所影响的行数为 3 行）

[david0927cs2006]

select ip,num, datetime
into #temp2
from table2

select ip, count(*) as num, datetime
into @temp1
from table1
group by ip, datetime

truncate table table2

insert into table2( ip, num, datetime )
select *
  from ( select *
           from #temp1
         union all
         select *
           from temp2
       ) a
group by a.ip, a.datetime

select 

[david0927cs2006]

不好意思,纠正一下

select ip,num, datetime
into #temp2
from table2

select ip, count(*) as num, datetime
into #temp1
from table1
group by ip, datetime

truncate table table2

insert into table2( ip, num, datetime )
select a.ip, 
       sum(a.num) as num, 
       a.datetime
  from ( select *
           from #temp1
         union all
         select *
           from #temp2
       ) a
group by a.ip, a.datetime

[sych888]

先delete表table2，再向表table2中查数据

[wangjinchang]

[Quote=引用 6 楼 sych888 的回复:]
先delete表table2，再向表table2中查数据
[/Quote]

delete table2？？？的数据不能删除。删除也是等table1的数据插入到table2后删除table1的数据

[华夏小卒]

插入前，先判断。有则更新，无则插入

[phl2009]

可以在table2上加个触发器 判断是插入还是更新

[nianran520]

--一句搞不定~
--> 测试数据：@table1
create table  #table1 ([id] int,[ip] varchar(15),[datetime] datetime,[url] varchar(11))
insert into  #table1
select 2289,'219.143.139.241','2009-11-18 09:32:42.640','VFClassroom' union all
select 2290,'219.143.139.241','2009-11-18 09:32:56.043','KL_room' union all
select 2291,'219.143.139.241','2009-11-19 09:34:28.170','KL_room' union all
select 2292,'219.143.139.241','2009-11-19 09:57:20.750','Index.aspx' union all
select 2293,'219.143.139.241','2009-11-19 09:57:27.670','Index.aspx' union all
select 2294,'219.143.139.241','2009-11-19 09:57:32.327','Task' union all
select 2295,'219.143.139.241','2009-11-19 09:58:38.577','Index.aspx' union all
select 2296,'219.143.139.245','2009-11-19 09:58:51.793','Index.aspx' union all
select 2297,'219.143.139.245','2009-11-19 09:58:58.310','Task' union all
select 2298,'219.143.139.245','2009-11-19 09:59:22.170','Index.aspx'
create table  #table2([id] int identity(1,1),[ip] varchar(15),[num] int,[datetime] varchar(10))
insert into #table2
select '219.143.139.241',4,'2009-11-18'

select * into #table3 from #table2
truncate table #table2
insert into #table2
select ip,sum(num) as num,datetime from 
(select ip,count(1) as num,convert(varchar(10),datetime,120) as datetime
from #table1
group by ip,convert(varchar(10),datetime,120)
union
select ip,num,datetime from #table3) r
group by ip,datetime



select * from #table2

drop table #table1,#table2,#table3

[tcm441224375]

学习，我还需努力

[tangtang89522]

insert into #table2
select a.ip,a.num+isnull((select num from #table2 where [datetime]=a.[datetime] and ip=a.ip),0),a.[datetime]
from
(
select ip,count(ip) num,convert(varchar(10),[datetime],120) [datetime]
from #table1
group by convert(varchar(10),[datetime],120),ip
)a
已测试通过。。。

[ChinaJiaBing]

declare @table1 table (id nvarchar(10),ip nvarchar(20),
[datetime] datetime,url nvarchar(20))
insert into @table1 select 2289,'219.143.139.241','2009-11-18 09:32:42.640','VFClassroom'
        union all   select 2290,'219.143.139.241','2009-11-18 09:32:56.043','KL_room'
        union all   select 2291,'219.143.139.241','2009-11-19 09:34:28.170','KL_room'
        union all   select 2292,'219.143.139.241','2009-11-19 09:57:20.750','Index.aspx'
        union all   select 2293,'219.143.139.241','2009-11-19 09:57:27.670','Index.aspx'
        union all   select 2294,'219.143.139.241','2009-11-19 09:57:32.327','Task'
        union all   select 2295,'219.143.139.241','2009-11-19 09:58:38.577','Index.aspx'
        union all   select 2296,'219.143.139.245','2009-11-19 09:58:51.793','Index.aspx'
        union all   select 2297,'219.143.139.245','2009-11-19 09:58:58.310','Task'
        union all   select 2298,'219.143.139.245','2009-11-19 09:59:22.170','Index.aspx'
declare @table2 table( id  int identity(1,1) ,ip nvarchar(20),num int, [datetime] datetime)
select ip,时间=convert(nvarchar(10),datetime,121),num=count(1)
                    into #  from @table1
                     group by ip,convert(nvarchar(10),datetime,121)
select ip,sum(num),[datetime] from
    (select ip,num,[datetime] from @table2
     union all
     select ip,num,时间 from #) tb group by ip,datetime
-----把@table2 清空，把上面的数据插入
go
drop table #



/*


ip                               datetime
-------------------- ----------- -----------------------
219.143.139.241      2           2009-11-18 00:00:00.000
219.143.139.241      5           2009-11-19 00:00:00.000
219.143.139.245      3           2009-11-19 00:00:00.000

(3 行受影响)

*/

[wangjinchang]

[Quote=引用 9 楼 phl2009 的回复:]
可以在table2上加个触发器  判断是插入还是更新
[/Quote]
说的很对！ 但是 不知道怎么用sql语句实现！