多表复杂查询解决思路

表1 添加新闻的表id mid     start_time              name1   1   03 30 2012 4:00AM           A 2   1   03 30 2012 5:00AM           A3   1   03 30 2012 6:37AM           A4   2   03 31 2012 4:00AM           B5   2   03 31 2012 5:00AM           B6   2   03 31 2012 6:00AM           B 表2 新闻名称表 mid  name 1    A 2    B表3 已删除新闻的表id  mid   start_time               end_time           name1   1   03 30 2012 7:00AM       03 30 2012 12:00AM      A 2   1   03 30 2012 8:00AM       03 30 2012 12:00AM      A3   1   03 30 2012 9:37AM       03 30 2012 12:00AM      A4   2   03 31 2012 7:00AM       03 31 2012 12:00AM      B5   2   03 31 2012 8:00AM       03 31 2012 12:00AM      B6   2   03 31 2012 9:00AM       03 31 2012 12:00AM      B

问题1：如何查询每天新添加的新闻名称以及新闻数量想得到结果是mid   name  (数量) 1     A      6 2     B      6在3月30号添加新闻名为A的新闻6条,新闻名为B的新闻6条语句1：select mid,COUNT(id)        from  表1              where DATEDIFF(DAY,convert(varchar(10),start_time ,120),GETDATE())=20 group by (id)；结果mid   (数量)1       3只能查询出 当天添加的新闻和数量 而当天已经删除了的新闻和新闻名没法查询出来请大家帮助下问题2 查询出每天删除的新闻名称,以及新闻数量想得到结果是mid   name  (数量) 1     A      3 2     B      3语句2：select mid,count(id)        form 表3            where DATEDIFF(DAY,convert(varchar(10),start_time ,120),GETDATE())=20 group by (id)；结果mid   (数量)1      32      3

IF OBJECT_ID('NewsAdd') IS NOT NULL    DROP TABLE NewsAdd;IF OBJECT_ID('NewsDel') IS NOT NULL    DROP TABLE NewsDel;IF OBJECT_ID('NewsItems') IS NOT NULL    DROP TABLE NewsItems;GOCREATE TABLE NewsAdd(id int, mid int, start_time datetime                    , Tname nvarchar(16), Iname nvarchar(16), Tid int, Iid int);CREATE TABLE NewsDel(id int, mid int, start_time datetime, end_time datetime                    , Tname nvarchar(16), Iname nvarchar(16), Tid int, Iid int);CREATE TABLE NewsItems( tid int, Tname nvarchar(16));GOINSERT INTO NewsAdd(id, mid, start_time, Tname, Iname, Tid, Iid)select 1,   1, '03 30 2012 6:00AM', '时事新闻', '地铁出轨',         1, 1 union allselect 2,   2, '03 30 2012 7:00AM', '时事新闻', '火车出轨',         1, 2 union allselect 3,   5, '03 31 2012 6:00AM', '军事新闻', '朝鲜发射火箭',     2, 5 union allselect 4,   6, '03 31 2012 7:00AM', '军事新闻', '朝鲜火箭发射失败', 2, 6;INSERT INTO NewsDel(id, mid, start_time, end_time, Tname, Iname, Iid, Tid)select 1,   3, '03 30 2012 8:00AM', '03 30 2012 12:00AM', '时事新闻', '汽车相撞',   3, 1 union allselect 2,   4, '03 30 2012 9:00AM', '03 30 2012 12:00AM', '时事新闻', '自行车相撞', 4, 1 union allselect 3,   7, '03 30 2012 8:00AM', '03 31 2012 12:00AM', '军事新闻', '美伊问题 ',  7, 2 union allselect 4,   8, '03 31 2012 9:00AM', '03 31 2012 12:00AM', '军事新闻', '叙利亚问题', 8, 2;INSERT INTO NewsItems(tid,  Tname)select 1,   '时事新闻' union allselect 2,   '军事新闻';--select  * from NewsAdd;--select  * from NewsDel;--select  * from NewsItems;select  n.Tname, b.Numfrom (  select tid, COUNT(1) as Num        from  ( select tid, start_time from NewsAdd                 union all                 select tid, start_time from NewsDel               ) a         where start_time between '2012-03-30' and '2012-03-31 23:59'         -- 这个条件语句你可以按下面改，因为这里没有数据，按下面改就没有结果了，        -- 所以暂时按上面的临时写法        --where start_time >= DATEADD(DD, -1, convert(varchar(10), GETDATE(), 120))         --        and start_time < convert(varchar(10), GETDATE(), 120)        group by tid        ) bjoin NewsItems n on b.tid = n.tidselect  n.Tname, b.Numfrom (        select tid, count(1) as Num        from NewsDel        where start_time between '2012-03-30' and '2012-03-31 23:59'        -- 这里按上面一样改就好了        group by tid     ) bjoin NewsItems n on b.tid = n.tid/*Tname            Num---------------- -----------时事新闻             4军事新闻             4Tname            Num---------------- -----------时事新闻             2军事新闻             2*/