我在数量库中一个表中存放着每天写入的一条记录,每天只能写入一条,比如说日记,有的天数由于某些原因没有来得及写,就会出现在数量库表中没有今天的记录,现在我想知道一个月中哪些天写了,哪天天没有写,做一个标识,在数量库中执行查询,如果写了的话,那么就select出来1,如果没有写的话,那么就在数量库中select出来0,自己总是写不出来,因为中间有一些天数的日记没有写,所以记录都是空的,如果按我这种思路来做的话,那么要怎么来查询呢?更希望大家提好的意见,如果说要通过每天都请求一次数据库的话,可是可以,不过效率真是太低了,所以不采纳,请高手帮助。表的结构大概就是: 
SQL code
create table logs(
logid int not null, --日记编号
logcontent text not null, --日记内容
logtime nvarchar(10) not null --日记创建时间,形式为2010-01-01的字符串
)
测试数量可以用: 1 dfgsdfgsdf 2010-02-01 
2 dfgsdfgsdf 2010-02-02 
3 dfgsdfgsdf 2010-02-03 
4 dfgsdfgsdf 2010-02-04 
5 dfgsdfgsdf 2010-02-08 
6 dfgsdfgsdf 2010-02-11 
7 dfgsdfgsdf 2010-02-14 
8 dfgsdfgsdf 2010-02-16 
9 dfgsdfgsdf 2010-02-17 

解决方案 »

  1.   

    ----------------------------------------------------------------
    -- Author  :SQL77(只为思齐老)
    -- Date    :2010-02-01 18:33:05
    -- Version:
    --      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
    -- Aug  6 2000 00:57:48 
    -- Copyright (c) 1988-2000 Microsoft Corporation
    -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
    --
    ----------------------------------------------------------------
    --> 测试数据:#tB
    if object_id('tempdb.dbo.#tB') is not null drop table #tB
    go 
    create table #tB([A] int,[C2] varchar(10),[C3] datetime)
    insert #tB
    select 1,'dfgsdfgsdf','2010-02-01' union all
    select 2,'dfgsdfgsdf','2010-02-02' union all
    select 3,'dfgsdfgsdf','2010-02-03' union all
    select 4,'dfgsdfgsdf','2010-02-04' union all
    select 5,'dfgsdfgsdf','2010-02-08' union all
    select 6,'dfgsdfgsdf','2010-02-11' union all
    select 7,'dfgsdfgsdf','2010-02-14' union all
    select 8,'dfgsdfgsdf','2010-02-16' union all
    select 9,'dfgsdfgsdf','2010-02-17'
    --------------开始查询--------------------------select T1.TIME,T.*,
    CASE WHEN C3 IS NULL THEN 0 ELSE 1 END FLAG
     from #tB T RIGHT JOIN (
    SELECT CONVERT(VARCHAR(10),DATEADD(DD,NUMBER,'2010-02-01'),120) TIME 
    FROM MASTER..SPT_VALUES WHERE TYPE='P' AND 
    CONVERT(VARCHAR(10),DATEADD(DD,NUMBER,'2010-02-01'),120)<='2010-02-28')AS T1
    ON T.C3=T1.TIME
    ----------------结果----------------------------
    /* (所影响的行数为 9 行)TIME       A           C2         C3                                                     FLAG        
    ---------- ----------- ---------- ------------------------------------------------------ ----------- 
    2010-02-01 1           dfgsdfgsdf 2010-02-01 00:00:00.000                                1
    2010-02-02 2           dfgsdfgsdf 2010-02-02 00:00:00.000                                1
    2010-02-03 3           dfgsdfgsdf 2010-02-03 00:00:00.000                                1
    2010-02-04 4           dfgsdfgsdf 2010-02-04 00:00:00.000                                1
    2010-02-05 NULL        NULL       NULL                                                   0
    2010-02-06 NULL        NULL       NULL                                                   0
    2010-02-07 NULL        NULL       NULL                                                   0
    2010-02-08 5           dfgsdfgsdf 2010-02-08 00:00:00.000                                1
    2010-02-09 NULL        NULL       NULL                                                   0
    2010-02-10 NULL        NULL       NULL                                                   0
    2010-02-11 6           dfgsdfgsdf 2010-02-11 00:00:00.000                                1
    2010-02-12 NULL        NULL       NULL                                                   0
    2010-02-13 NULL        NULL       NULL                                                   0
    2010-02-14 7           dfgsdfgsdf 2010-02-14 00:00:00.000                                1
    2010-02-15 NULL        NULL       NULL                                                   0
    2010-02-16 8           dfgsdfgsdf 2010-02-16 00:00:00.000                                1
    2010-02-17 9           dfgsdfgsdf 2010-02-17 00:00:00.000                                1
    2010-02-18 NULL        NULL       NULL                                                   0
    2010-02-19 NULL        NULL       NULL                                                   0
    2010-02-20 NULL        NULL       NULL                                                   0
    2010-02-21 NULL        NULL       NULL                                                   0
    2010-02-22 NULL        NULL       NULL                                                   0
    2010-02-23 NULL        NULL       NULL                                                   0
    2010-02-24 NULL        NULL       NULL                                                   0
    2010-02-25 NULL        NULL       NULL                                                   0
    2010-02-26 NULL        NULL       NULL                                                   0
    2010-02-27 NULL        NULL       NULL                                                   0
    2010-02-28 NULL        NULL       NULL                                                   0(所影响的行数为 28 行)
    */
      

  2.   

    上面行得通,不过如果我再在表#tB中加中一个字段做为WHERE 条件的话,right join就又不起作用了
      

  3.   

    找到解决办法了,不过就是太笨了,我将先要WHERE条件的表结果选择出来 再来right join 然后再来select,呵呵,先用着,谢谢大家了