3个表
umUserAccount
UserID
Name
pmMemo
MemoID
UserID
Title
BodyText
BeginTime
EndTime
MemoPlace
Modified
Created
AlertTimeSpace
MemoTypeID
IsRead
pmMemoSendObject
ID
MemoID
SendUserID
AcceptUserID
isread
我想要的结果就是:
begintime-getdate()<=alerttimespace并且begintime>=getdate()并且(acceptuserid=1或者userid=1)并且同时满足上述语句中的其他条件的纪录得到的错误结果是
(acceptuserid=1或者userid=1)并且同时满足上述语句中的其他条件的纪录
就是上面的时间判断没有起作用
umUserAccount
UserID
Name
pmMemo
MemoID
UserID
Title
BodyText
BeginTime
EndTime
MemoPlace
Modified
Created
AlertTimeSpace
MemoTypeID
IsRead
pmMemoSendObject
ID
MemoID
SendUserID
AcceptUserID
isread
我想要的结果就是:
begintime-getdate()<=alerttimespace并且begintime>=getdate()并且(acceptuserid=1或者userid=1)并且同时满足上述语句中的其他条件的纪录得到的错误结果是
(acceptuserid=1或者userid=1)并且同时满足上述语句中的其他条件的纪录
就是上面的时间判断没有起作用
'自己' AS SendName,MemoID, UserID, Title,
BodyText, BeginTime, EndTime, MemoPlace,
Modified, Created, AlertTimeSpace, MemoTypeID,IsRead
FROM pmMemo
WHERE (UserID = 1)
AND (BeginTime - GETDATE() <= AlertTimeSpace)
AND (BeginTime >= GETDATE())
AND(
(UserID = 1)
OR (IsRead = 0) )
Union all
SELECT umUserAccount.Name AS SendName, pmMemo.MemoID, pmMemo.UserID, pmMemo.Title,
pmMemo.BodyText, pmMemo.BeginTime, pmMemo.EndTime, pmMemo.MemoPlace,
pmMemo.Modified, pmMemo.Created, pmMemo.AlertTimeSpace, pmMemo.MemoTypeID, pmMemoSendObject.isRead AS IsRead
FROM pmMemo
INNER JOIN pmMemoSendObject
ON pmMemo.MemoID = pmMemoSendObject.MemoID
INNER JOIN umUserAccount
ON pmMemoSendObject.SendUserID = umUserAccount.UserID
WHERE (pmMemoSendObject.AcceptUserID = 1)
AND (pmMemo.BeginTime - GETDATE() <= pmMemo.AlertTimeSpace)
AND (pmMemo.BeginTime >= GETDATE())
AND(
(pmMemoSendObject.AcceptUserID = 1)
OR (pmMemoSendObject.isRead = 0))
或者isRead=0 or UserID=1我要的或者是acceptuserid=1 or UserID=1
'自己' AS SendName,MemoID, UserID, Title,
BodyText, BeginTime, EndTime, MemoPlace,
Modified, Created, AlertTimeSpace, MemoTypeID,IsRead
FROM pmMemo
WHERE (UserID = 1)
AND (BeginTime - GETDATE() <= AlertTimeSpace)
AND (BeginTime >= GETDATE())
OR (UserID = 1)
AND (IsRead = 0) )
Union all
SELECT umUserAccount.Name AS SendName, pmMemo.MemoID, pmMemo.UserID, pmMemo.Title,
pmMemo.BodyText, pmMemo.BeginTime, pmMemo.EndTime, pmMemo.MemoPlace,
pmMemo.Modified, pmMemo.Created, pmMemo.AlertTimeSpace, pmMemo.MemoTypeID, pmMemoSendObject.isRead AS IsRead
FROM pmMemo
INNER JOIN pmMemoSendObject
ON pmMemo.MemoID = pmMemoSendObject.MemoID
INNER JOIN umUserAccount
ON pmMemoSendObject.SendUserID = umUserAccount.UserID
WHERE (pmMemoSendObject.AcceptUserID = 1)
AND (pmMemo.BeginTime - GETDATE() <= pmMemo.AlertTimeSpace)
AND (pmMemo.BeginTime >= GETDATE())
AND(
(pmMemoSendObject.AcceptUserID = 1)
OR (pmMemoSendObject.isRead = 0))