多表复杂查询

Question

表1 添加新闻的表
id mid     start_time              name
1   1   03 30 2012 4:00AM           A 
2   1   03 30 2012 5:00AM           A
3   1   03 30 2012 6:37AM           A
4   2   03 31 2012 4:00AM           B
5   2   03 31 2012 5:00AM           B
6   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      A
3   1   03 30 2012 9:37AM       03 30 2012 12:00AM      A
4   2   03 31 2012 7:00AM       03 31 2012 12:00AM      B
5   2   03 31 2012 8:00AM       03 31 2012 12:00AM      B
6   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      3
2      3请求教下 这样的语句该怎么写 还有就是 根据时间查询数据的语句是否有问题

Answer 1

看下面的是不是你想要的，另外你想把name也列出来，但是你又是按mid分组的，难道一天中的新闻名称都一样吗？如果不一样，列出那条新闻的名字呢？
--第一问
select mid, COUNT(1)
from  ( select mid, start_time from 表1 
union all 
select mid, start_time from 表3
   ) a 
where start_time = DATEDIFF(DAY, -20, GETDATE())  --这样写能利用上索引
group by mid;
--第二问
select mid,count(1)
from 表3
where start_time = DATEDIFF(DAY, -20, GETDATE())
group by mid;

Answer 2

新闻名是一样的  里面有个新闻子标题 跟子标题ID 抱歉少写了目前就是想查出
每天添加的新闻大标题,总共添加的数量 以及新闻的mid mid   name  (数量)
 1     A      6
 2     B      6

Answer 3

--第一问
select  b.mid, 表2.name, b.数量
from ( select mid, COUNT(1) as 数量
from  ( select mid, start_time from 表1 
union all 
select mid, start_time from 表3
   ) a 
where start_time = DATEDIFF(DAY, -20, GETDATE())  --这样写能利用上索引
group by mid
) b
join 表2 on b.mid = 表2.mid--第二问
select  b.mid, 表2.name, b.数量
from (
select mid, count(1) as 数量
from 表3
where start_time = DATEDIFF(DAY, -20, GETDATE())
group by mid
 ) b
join 表2 on b.mid = 表2.mid

Answer 4

 你写的这个条件语句时什么意思啊？
DATEDIFF(DAY,convert(varchar(10),start_time ,120),GETDATE())=20

Answer 5

IF OBJECT_ID('表1') IS NOT NULL
    DROP TABLE 表1;
IF OBJECT_ID('表2') IS NOT NULL
    DROP TABLE 表2;
IF OBJECT_ID('表3') IS NOT NULL
    DROP TABLE 表3;
GO
CREATE TABLE 表1(id int, mid int, start_time datetime, name varchar(8))
CREATE TABLE 表2( mid int, name varchar(8))
CREATE TABLE 表3(id int, mid int, start_time datetime, end_time datetime, name varchar(8))
GO
INSERT INTO 表1
select 1,   1, '03 30 2012 4:00AM', 'A' union all
select 2,   1, '03 30 2012 5:00AM', 'A' union all
select 3,   1, '03 30 2012 6:37AM', 'A' union all
select 4,   2, '03 31 2012 4:00AM', 'B' union all
select 5,   2, '03 31 2012 5:00AM', 'B' union all
select 6,   2, '03 31 2012 6:00AM', 'B';INSERT INTO 表2
select 1, 'A' union all
select 2, 'B';INSERT INTO 表3
select 1,   1,   '03 30 2012 7:00AM',       '03 30 2012 12:00AM',     'A' union all 
select 2,   1,   '03 30 2012 8:00AM',       '03 30 2012 12:00AM',     'A' union all
select 3,   1,   '03 30 2012 9:37AM',       '03 30 2012 12:00AM',     'A' union all
select 4,   2,   '03 31 2012 7:00AM',       '03 31 2012 12:00AM',     'B' union all
select 5,   2,   '03 31 2012 8:00AM',       '03 31 2012 12:00AM',     'B' union all
select 6,   2,   '03 31 2012 9:00AM',       '03 31 2012 12:00AM',     'B';--第一问
select  b.mid, 表2.name, b.数量
from ( select mid, COUNT(1) as 数量
from  ( select mid, start_time from 表1 
union all 
select mid, start_time from 表3
   ) a 
where start_time between '2012-03-30' and '2012-03-31 23:59' 
group by mid
) b
join 表2 on b.mid = 表2.mid--第二问
select  b.mid, 表2.name, b.数量
from (
select mid, count(1) as 数量
from 表3
where start_time between '2012-03-30' and '2012-03-31 23:59'
group by mid
 ) b
join 表2 on b.mid = 表2.mid/*
mid         name     数量
----------- -------- -----------
1           A        6
2           B        6mid         name     数量
----------- -------- -----------
1           A        3
2           B        3
*/

Answer 6

从今天前推20天 也就是3月30号 where start_time between '2012-03-30' and '2012-03-31 23:59'
这个时间是固定的 
这SQL要是写成存储过程 每天执行 查询前一天的数据 你这样写 不行吧

Answer 7

那你把where条件语句稍微改一下就好了啊，你前面是说查当天，但语句又不是那个意思，所以没有明白你的意思。
where start_time between dateadd(dd, -20, getdate()) and (getdate()+1)

Answer 8

不好意思 可能表达有误 dateadd(dd, -20, getdate()) and (getdate()+1)
 +1是什么意思 能解释下么
select  b.mid, 表2.name, b.数量
from (    select mid, COUNT(1) as 数量
        from  ( select mid, start_time from 表1 
                union all 
                select mid, start_time from 表3
               ) a 
        where start_time between '2012-03-30' and '2012-03-31 23:59' 
        group by mid
        ) b
join 表2 on b.mid = 表2.mid
这句有点不明白 
这里的a跟b各带表什么意思？
count(1) 这里怎么会写1？ 请解释下 不好意思 sql语句就会点简单的查询 感谢你了

Answer 9

哦，前面多写了，不要加1 就好了。
a,b都代表前面括弧的子查询。
count(1)就是数据的行数了，你写count(*)或者count(mid)也可以

Answer 10

表1 新闻添加表
    mid   start_time             Tname        Iname                  Tid      Iid
     1    03 30 2012 6:00AM      时事新闻     地铁出轨                 1        1
     2    03 30 2012 7:00AM      时事新闻     火车出轨                 1        2
     5    03 31 2012 6:00AM      军事新闻     朝鲜发射火箭             2        5
     6    03 31 2012 7:00AM      军事新闻     朝鲜火箭发射失败         2        6表2 新闻删除表 
     id  mid   start_time               end_time              Tname          Iname           Iid     1   3   03 30 2012 8:00AM       03 30 2012 12:00AM      时事新闻        汽车相撞         3
     2   4   03 30 2012 9:00AM       03 30 2012 12:00AM      时事新闻        自行车相撞       4
     3   7   03 30 2012 8:00AM       03 31 2012 12:00AM      军事新闻        美伊问题         7
     4   8   03 31 2012 9:00AM       03 31 2012 12:00AM      军事新闻        叙利亚问题       8表3 新闻表
    tid  Tname
     1   时事新闻
     2   军事新闻
查询3月30号这天添加多少新闻
显示为    Tname     数量
   时事新闻    4
   军事新闻    4
30月30号这天删除的新闻   Tname       数量
   时事新闻     2
   军事新闻     2我重新建立数据库表 按照您说的方法写了 但报错
select b.tid, 表3.Tname,b.数量 from
(select tid,COUNT (mid) as 数量  from 
(select tid,mid,start_time from 表1
union all
select tid, start_time from 表2)a 
where DATEDIFF(DD,convert(varchar(10),start_time,120),GETDATE())=21 group by(tid))b
join 表3 on b.tid=表3.tid
   感觉是语句的问题 不知道哪里出错了 

Answer 11

设计有点问题：表1 和表3重复了，完全只用表3就可以代替表1 了。
当 end_time is not null 表示删除数据，这样维护方便多了！   

Answer 12

回复10楼：
你的union 语句写错了，连接的两个查询字段数不一样，第二个少了一列 mid
select tid,mid,start_time from 表1
union all
select tid, start_time from 表2
应该改为如下：
select tid, mid, start_time from 表1
union all
select tid, mid, start_time from 表3
你复制我在5楼的脚本运行，肯定是没有问题，你自己如果要改动，注意语法不要改错了。
另外你的“从今天前推20天”的where条件语句是没办法利用 start_time 字段的索引的，建议你按下面的改：
where start_time >= DATEADD(DD, -20, convert(varchar(10), GETDATE(), 120)) 
and start_time < DATEADD(DD, -19, convert(varchar(10), GETDATE(), 120))

Answer 13

谢谢大哥了 我想了想
select tid,count(mid) from 表1 as 数量 where  DATEDIFF(DD,convert(varchar(10),start_time,120),GETDATE())=21 group by(tid)
tid  数量
 1    2
 2    2
select tid,count(mid) from 表2 as 数量 where  DATEDIFF(DD,convert(varchar(10),start_time,120),GETDATE())=21 group by(tid)
tid 数量
 1   2
 2   2
select Tname tid from 表3
tid tname  
1    时事新闻
2    军事新闻我要的效果 应该是这三个语句联合查询出来的吧？ 
就是按照您5楼写的 没错吧？
对了 表2里也有 新闻标题tid的

Answer 14

表1 新闻添加表
  id  mid   start_time             Tname        Iname                  Tid      Iid
   1   1    03 30 2012 6:00AM      时事新闻     地铁出轨                 1        1
   2   2    03 30 2012 7:00AM      时事新闻     火车出轨                 1        2
   3   5    03 31 2012 6:00AM      军事新闻     朝鲜发射火箭             2        5
   4   6    03 31 2012 7:00AM      军事新闻     朝鲜火箭发射失败         2        6
表2 新闻删除表 
id  mid   start_time        end_time         Tname       Iname        Iid  tid1   3   03 30 2012 8:00AM   03 30 2012 12:00AM 时事新闻  汽车相撞        3    1
2   4   03 30 2012 9:00AM   03 30 2012 12:00AM 时事新闻  自行车相撞      4    1
3   7   03 30 2012 8:00AM   03 31 2012 12:00AM 军事新闻  美伊问题        7    2
4   8   03 31 2012 9:00AM   03 31 2012 12:00AM 军事新闻  叙利亚问题      8    2表3 新闻表
    tid  Tname
    1   时事新闻
     2   军事新闻
查询3月30号这天添加多少新闻
显示为    Tname     数量
   时事新闻    4
   军事新闻    4
30月30号这天删除的新闻   Tname       数量
   时事新闻     2
   军事新闻     2
  
大哥 不好意思 麻烦您了
1 每次执行的时候 查询出前一天 所添加的新闻 
2 每次执行的时候查询出前一天 删除的新闻

Answer 15

这个不需要了 不过到时候真要查之前很久的数据话 把时间改下不就行了？
where start_time >= DATEADD(DD, -20, convert(varchar(10), GETDATE(), 120)) 
    and start_time < DATEADD(DD, -19, convert(varchar(10), GETDATE(), 120))把-20 跟-19 改下 应该没问题吧

Answer 16

看看满足你要求不？
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;
GO
CREATE 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));GO
INSERT INTO NewsAdd(id, mid, start_time, Tname, Iname, Tid, Iid)
select 1,   1, '03 30 2012 6:00AM', '时事新闻', '地铁出轨',         1, 1 union all
select 2,   2, '03 30 2012 7:00AM', '时事新闻', '火车出轨',         1, 2 union all
select 3,   5, '03 31 2012 6:00AM', '军事新闻', '朝鲜发射火箭',     2, 5 union all
select 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 all
select 2,   4, '03 30 2012 9:00AM', '03 30 2012 12:00AM', '时事新闻', '自行车相撞', 4, 1 union all
select 3,   7, '03 30 2012 8:00AM', '03 31 2012 12:00AM', '军事新闻', '美伊问题 ',  7, 2 union all
select 4,   8, '03 31 2012 9:00AM', '03 31 2012 12:00AM', '军事新闻', '叙利亚问题', 8, 2;INSERT INTO NewsItems(tid,  Tname)
select 1,   '时事新闻' union all
select 2,   '军事新闻';--select  * from NewsAdd;
--select  * from NewsDel;
--select  * from NewsItems;select  n.Tname, b.Num
from (  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
        ) b
join NewsItems n on b.tid = n.tidselect  n.Tname, b.Num
from (
        select tid, count(1) as Num
        from NewsDel
        where start_time between '2012-03-30' and '2012-03-31 23:59'
-- 这里按上面一样改就好了
        group by tid
     ) b
join NewsItems n on b.tid = n.tid/*
Tname            Num
---------------- -----------
时事新闻             4
军事新闻             4Tname            Num
---------------- -----------
时事新闻             2
军事新闻             2
*/