MSSQL语句---一个通过时间差进行查询取得对应ID的问题 select * from A表 a join B表 b on 关联条件 where a.类型=? and datediff(second, b.时间OPT, getdate()) [ > | < | <> | = ] ? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from B where datediff( d, opt, getdate() ) = ?? and exists( select 1 from A where id = B.id and 类型 = ?? ) 不是很明白 楼主的数据结构,A B 表之间有什么关系,A 表的类型列 和 B 表好像关系比较不清晰的啊 A表 DROP TABLE [T_ServerInOut] CREATE TABLE [T_ServerInOut] ( [IONUM] [varchar] (25) NOT NULL , [CRFLAG] [int] NOT NULL , [IOFLAG] [int] NOT NULL , [IODATE] [datetime] NOT NULL , [PRONUM] [varchar] (12) NOT NULL , [DNUM] [int] NOT NULL , [DMAN] [varchar] (10) NULL , [NOTE] [varchar] (250) NULL , [OPT] [datetime] NOT NULL , [OID] [int] NOT NULL , [USERNAME] [varchar] (20) NOT NULL ) ALTER TABLE [T_ServerInOut] WITH NOCHECK ADD CONSTRAINT [DF_T_ServerInOut_IOFLAG] DEFAULT (1) FOR [IOFLAG],CONSTRAINT [DF_T_ServerInOut_OPT] DEFAULT (getdate()) FOR [OPT] INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , 0 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:27:28.717' , 1 , 'admin' ) INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227394218' , 0 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:27:51.107' , 1 , 'admin' ) INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151228042031' , 1 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:28:08.310' , 1 , 'admin' ) INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , 1 , 1 , '2008-05-01 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 13:15:44.793' , 1 , 'admin' )B表 DROP TABLE [T_ServerInOutDetail] CREATE TABLE [T_ServerInOutDetail] ( [IONUM] [varchar] (25) NOT NULL , [PID] [varchar] (25) NOT NULL , [MC] [varchar] (100) NOT NULL , [SL] [int] NOT NULL , [QCSL] [int] NOT NULL , [QMSL] [int] NOT NULL , [NOTE] [varchar] (250) NULL , [OPT] [datetime] NOT NULL , [OID] [int] NOT NULL , [USERNAME] [varchar] (20) NOT NULL ) ALTER TABLE [T_ServerInOutDetail] WITH NOCHECK ADD CONSTRAINT [DF_T_ServerInOutDetail_SL] DEFAULT (0) FOR [SL],CONSTRAINT [DF_T_ServerInOutDetail_QCSL] DEFAULT (0) FOR [QCSL],CONSTRAINT [DF_T_ServerInOutDetail_QMSL] DEFAULT (0) FOR [QMSL],CONSTRAINT [DF_T_ServerInOutDetail_OPT] DEFAULT (getdate()) FOR [OPT] INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , '101002001' , '小 箱 C型' , 0 , 1000 , 1000 , '2008-05-15 12:27:28.717' , 1 , 'admin' ) INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , '101002001' , '小 箱 C型' , 1000 , 1000 , 2000 , '2008-05-15 12:27:31.983' , 1 , 'admin' ) INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227394218' , '101002001' , '小 箱 C型' , 2000 , -200 , 1800 , '2008-05-15 12:27:51.107' , 1 , 'admin' ) INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151228042031' , '101002001' , '小 箱 C型' , 0 , -200 , -200 , '2008-05-15 12:28:08.357' , 1 , 'admin' ) INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , '101002001' , '小 箱 C型' , -200 , 100 , -100 , '2008-05-15 13:16:28.000' , 1 , 'admin' ) INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , '101002001' , '小 箱 C型' , 1800 , 100 , 1900 , '2008-05-15 13:16:44.793' , 1 , 'admin' ) 现在我需要用一个当前时间的函数getdate()来和B表中所有的OPT进行比较(就相当于相减,再通过A表中的类型列来取得对应这个类型的B表中的相关行数据,应该怎么得到呢? -----------------------------------这句话念了半天也没理解楼主的意思顶。 关于sql存储过程连接符 错误提示 sql server2000不支持用列别名运算吗? 请教:当前日期的前一个月,用SQL怎么写啊,很急,谢谢 请教统计的问题 在sql server中怎样求本月天数? 如果我的表名是个变量,改怎么办哪~ 配置发布,顶略服务器 安装SQLSERVER2000出现"许可证模式的选择"应如何设置. 请教:C++Builder访问SQL Server sql语句合并 如何给下表设置联合主键
from B
where datediff( d, opt, getdate() ) = ??
and exists( select 1 from A where id = B.id and 类型 = ?? )
DROP TABLE [T_ServerInOut]
CREATE TABLE [T_ServerInOut] ( [IONUM] [varchar] (25) NOT NULL , [CRFLAG] [int] NOT NULL , [IOFLAG] [int] NOT NULL , [IODATE] [datetime] NOT NULL , [PRONUM] [varchar] (12) NOT NULL , [DNUM] [int] NOT NULL , [DMAN] [varchar] (10) NULL , [NOTE] [varchar] (250) NULL , [OPT] [datetime] NOT NULL , [OID] [int] NOT NULL , [USERNAME] [varchar] (20) NOT NULL )
ALTER TABLE [T_ServerInOut] WITH NOCHECK ADD CONSTRAINT [DF_T_ServerInOut_IOFLAG] DEFAULT (1) FOR [IOFLAG],CONSTRAINT [DF_T_ServerInOut_OPT] DEFAULT (getdate()) FOR [OPT] INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , 0 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:27:28.717' , 1 , 'admin' )
INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227394218' , 0 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:27:51.107' , 1 , 'admin' )
INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151228042031' , 1 , 1 , '2008-05-14 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 12:28:08.310' , 1 , 'admin' )
INSERT [T_ServerInOut] ( [IONUM] , [CRFLAG] , [IOFLAG] , [IODATE] , [PRONUM] , [DNUM] , [DMAN] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , 1 , 1 , '2008-05-01 00:00:00.000' , '001' , 2 , '覃有铭' , '2008-05-15 13:15:44.793' , 1 , 'admin' )B表 DROP TABLE [T_ServerInOutDetail]
CREATE TABLE [T_ServerInOutDetail] ( [IONUM] [varchar] (25) NOT NULL , [PID] [varchar] (25) NOT NULL , [MC] [varchar] (100) NOT NULL , [SL] [int] NOT NULL , [QCSL] [int] NOT NULL , [QMSL] [int] NOT NULL , [NOTE] [varchar] (250) NULL , [OPT] [datetime] NOT NULL , [OID] [int] NOT NULL , [USERNAME] [varchar] (20) NOT NULL )
ALTER TABLE [T_ServerInOutDetail] WITH NOCHECK ADD CONSTRAINT [DF_T_ServerInOutDetail_SL] DEFAULT (0) FOR [SL],CONSTRAINT [DF_T_ServerInOutDetail_QCSL] DEFAULT (0) FOR [QCSL],CONSTRAINT [DF_T_ServerInOutDetail_QMSL] DEFAULT (0) FOR [QMSL],CONSTRAINT [DF_T_ServerInOutDetail_OPT] DEFAULT (getdate()) FOR [OPT] INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , '101002001' , '小 箱 C型' , 0 , 1000 , 1000 , '2008-05-15 12:27:28.717' , 1 , 'admin' )
INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227169218' , '101002001' , '小 箱 C型' , 1000 , 1000 , 2000 , '2008-05-15 12:27:31.983' , 1 , 'admin' )
INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151227394218' , '101002001' , '小 箱 C型' , 2000 , -200 , 1800 , '2008-05-15 12:27:51.107' , 1 , 'admin' )
INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151228042031' , '101002001' , '小 箱 C型' , 0 , -200 , -200 , '2008-05-15 12:28:08.357' , 1 , 'admin' )
INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , '101002001' , '小 箱 C型' , -200 , 100 , -100 , '2008-05-15 13:16:28.000' , 1 , 'admin' )
INSERT [T_ServerInOutDetail] ( [IONUM] , [PID] , [MC] , [SL] , [QCSL] , [QMSL] , [OPT] , [OID] , [USERNAME] ) VALUES ( '200805151315332812' , '101002001' , '小 箱 C型' , 1800 , 100 , 1900 , '2008-05-15 13:16:44.793' , 1 , 'admin' )
-----------------------------------
这句话念了半天也没理解楼主的意思顶。