我在数量库中一个表中存放着每天写入的一条记录,每天只能写入一条,比如说日记,有的天数由于某些原因没有来得及写,就会出现在数量库表中没有今天的记录,现在我想知道一个月中哪些天写了,哪天天没有写,做一个标识,在数量库中执行查询,如果写了的话,那么就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
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
-- 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 行)
*/