2表更新

tb1
name date time invoice
张三 2011-5-1 10:00 10001
李四 2011-5-1 10:00 10001
张三 2011-5-1 10:00 10001
张三 2011-5-1 10:00 10001
张三 2011-5-1 10:00 10002
张三 2011-5-1 11:00 10003
李四 2011-5-1 11:00 10002
李四 2011-5-1 11:00 10002
李四 2011-5-1 11:00 10003

tb2
name date time count_distinct_invoice
张三 2011-5-1 10:00  
张三 2011-5-1 11:00  
李四 2011-5-1 10:00
李四 2011-5-1 11:00

要求，根据tb1表更新tb2表，正确的更新结果如下：
tb2
name date time count_distinct_invoice
张三 2011-5-1 10:00 2
张三 2011-5-1 11:00 1
李四 2011-5-1 10:00 1
李四 2011-5-1 11:00 2

谢谢大家！

Update tb2
set count_distinct_invoice = isnull(B.cnt,0)
from tb2
left join
(
select name ,date, time, count(distinct invoice) as cnt
from tb1 
group by name ,date, time
) B
on tb2.name=B.name
and tb2.date=B.date
and tb2.time=B.time

SQL code

update tb2
set count_distinct_invoice=
(select count(distinct invoice) from tb1 where tb1.name=tb2.name and tb1.date=tb2.date and tb1.time=tb2.time)

SQL code

declare @tb1 table 
(name varchar(4),date varchar(10),time varchar(10),invoice int)
insert into @tb1
select '张三','2011-5-1','10:00',10001 union all
select '李四','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10002 union all
select '张三','2011-5-1','11:00',10003 union all
select '李四','2011-5-1','11:00',10002 union all
select '李四','2011-5-1','11:00',10002 union all
select '李四','2011-5-1','11:00',10003

declare @tb2 table 
(name varchar(4),date varchar(10),time varchar(10),count_distinct_invoice int)
insert into @tb2
select '张三','2011-5-1','10:00',null union all
select '张三','2011-5-1','11:00',null union all
select '李四','2011-5-1','10:00',null union all
select '李四','2011-5-1','11:00',null

select name,date,time,count(*) as mcount from (
select a.* from @tb2 a
left join @tb1 b on a.name=b.name 
and a.date=b.date and a.time=b.time
) aa group by name,date,time
/*
name date       time       mcount
---- ---------- ---------- -----------
李四   2011-5-1   10:00      1
李四   2011-5-1   11:00      3
张三   2011-5-1   10:00      4
张三   2011-5-1   11:00      1
*/

结果不相同？

SQL code

if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([name] varchar(4),[date] varchar(9),[time] varchar(6),[invoice] int)
insert [tb1]
select '张三','2011-5-1','10:00',10001 union all
select '李四','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10001 union all
select '张三','2011-5-1','10:00',10002 union all
select '张三','2011-5-1','11:00',10003 union all
select '李四','2011-5-1','11:00',10002 union all
select '李四','2011-5-1','11:00',10002 union all
select '李四','2011-5-1','11:00',10003
go
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([name] varchar(4),[date] varchar(9),[time] varchar(6),[count_distinct_invoice] int)
insert [tb2]
select '张三','2011-5-1','10:00',null union all
select '张三','2011-5-1','11:00',null union all
select '李四','2011-5-1','10:00',null union all
select '李四','2011-5-1','11:00',null
go

update tb2
set count_distinct_invoice=
(select count(distinct invoice) from tb1 where tb1.name=tb2.name and tb1.date=tb2.date and tb1.time=tb2.time)

select * from tb2
/**
name date      time   count_distinct_invoice
---- --------- ------ ----------------------
张三   2011-5-1  10:00  2
张三   2011-5-1  11:00  1
李四   2011-5-1  10:00  1
李四   2011-5-1  11:00  2

(4 行受影响)
**/

+1

SQL code

Update
 tb2 a
set
 count_distinct_invoice = isnull(b.num,0)
from
 tb2 a
left join
(select name ,date, time, count(distinct invoice) as num from tb1 group by name ,date, time)b
 on
 a.name=b.name
and
 a.date=b.date
and
 a.time=b.time

SQL code

UpDate Tb2    
    Set TB2.count_distinct_invoice = ISNULL(Count(T.count_distinct_invoice),0)
From Tb2 ,(Select name, date, time, Count(invoice) As count_distinct_invoice
From tb1) T 
Where TB2.name = T.name And TB2.date = T.date And TB2.time = T.time