日期:2014-05-18 浏览次数:20587 次
create table tableA(Id int,Name varchar(10)) insert into tablea values(1 ,'A') insert into tablea values(2 ,'B') insert into tablea values(3 ,'C') create table tableC(Id int,TypeName varchar(10),Time datetime) insert into tablec values(1 ,'A', '2011-07-01 07:30:30') insert into tablec values(2 ,'A', '2011-07-02 07:30:30') insert into tablec values(3 ,'B', '2011-07-03 07:30:30') insert into tablec values(4 ,'A', '2011-07-08 07:30:30') insert into tablec values(5 ,'B', '2011-07-11 07:30:30') insert into tablec values(6 ,'C', '2011-07-16 07:30:30') go --如果对应的NAME都存在,用俩表关联。 select m.name , count(1) cnt from tablea m , tablec n where m.name = n.typename and convert(varchar(10),n.time,120) between '2011-06-28' and '2011-07-28' group by m.name /* name cnt ---------- ----------- A 3 B 2 C 1 (所影响的行数为 3 行) */ --如果可能存在NAME在符合条件的情况在tablec中不存在,需要用0显示,则使用如下的子查询 select m.name , cnt = (select count(1) from tablec n where m.name = n.typename and convert(varchar(10),n.time,120) between '2011-06-28' and '2011-07-28') from tablea m /* name cnt ---------- ----------- A 3 B 2 C 1 (所影响的行数为 3 行) */ drop table tablea , tablec