这个SQL实在不知道是怎么语发错误,帮忙改改!谢谢！！！！！！！！！！！１１１
SELECT TOP 4 * FROM
(
SELECT D_PROF.*,D_USER.USER_NAME, D_OPEN.UPDATE_DATE FROM D_PROF
INNER JOIN D_USER ON D_PROF.D_USER_ID = D_USER.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1 AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.DEL_FLAG=0
WHERE DERIVEDTBL.D_PROF_ID NOT IN
(SELECT TOP 4 D_PROF.D_PROF_ID FROM D_PROF
INNER JOIN D_USER ON D_USER.D_USER_ID = D_PROF.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.ADMIN_ISOPEN=1 AND D_PROF.DEL_FLAG=0
ORDER BY D_OPEN.UPDATE_DATE desc
)
ORDER BY UPDATE_DATE desc
问题点数：50、回复次数：6Top
1 楼WangZWang（先来）回复于 2004-08-04 18:43:55 得分 0
SELECT TOP 4 * FROM
(
SELECT D_PROF.*,D_USER.USER_NAME, D_OPEN.UPDATE_DATE FROM D_PROF
INNER JOIN D_USER ON D_PROF.D_USER_ID = D_USER.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1 AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.DEL_FLAG=0
WHERE DERIVEDTBL.D_PROF_ID NOT IN --这儿错误 where 改 and 还有 on 上的单条件
(SELECT TOP 4 D_PROF.D_PROF_ID FROM D_PROF --如D_IMAGE_PROF.STATUS = 0最好放在INNER JOIN D_USER ON D_USER.D_USER_ID = D_PROF.D_USER_ID --Where中
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2
where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.ADMIN_ISOPEN=1 AND D_PROF.DEL_FLAG=0
ORDER BY D_OPEN.UPDATE_DATE desc
)
ORDER BY UPDATE_DATE descTop
2 楼zlp321002（Life Is Good,Let's Shine）回复于 2004-08-04 19:05:12 得分 25
报什么错误？Top
3 楼MPU（黑眼圈）回复于 2004-08-05 09:59:16 得分 0
服务器: 消息 156，级别 15，状态 1，行 16
キーワード 'WHERE' 付近に正しくない構文があります。
服务器: 消息 156，级别 15，状态 1，行 30
キーワード 'ORDER' 付近に正しくない構文があります。
Top
4 楼MPU（黑眼圈）回复于 2004-08-05 10:02:31 得分 0
名称 所有者 类型 创建日期
B_D_MANAGE_IP dbo 用户 2004-6-10 14:25:52
B_M_EVENT dbo 用户 2004-5-27 4:50:38
B_M_EVENT_TYPE dbo 用户 2004-5-27 4:50:52
B_M_SITE dbo 用户 2004-5-27 4:51:11
B_M_SITE_SYSTEM dbo 用户 2004-5-27 4:51:33
B_M_SITE_TYPE dbo 用户 2004-5-27 4:51:23
D_ACCESS_COUNT dbo 用户 2003-12-11 12:13:45
D_ADDRESS_BOOK dbo 用户 2003-9-30 10:17:06
D_ADVERTISER_COUNT dbo 用户 2003-9-30 10:17:06
D_ADVERTISER_LOOK dbo 用户 2003-9-30 10:17:06
D_ADVERTISER_MOBILE dbo 用户 2003-9-30 10:17:06
D_BACKNUMBER dbo 用户 2004-2-26 12:37:59
D_BLACK_LIST dbo 用户 2004-7-1 14:52:31
D_BLACK_LIST_USER_ACCESS_LOG dbo 用户 2004-7-1 14:52:31
D_CALENDAR dbo 用户 2004-7-9 16:12:52
D_CAMERA dbo 用户 2004-2-26 12:38:00
D_CAMERA_PIC dbo 用户 2003-12-19 12:16:41
D_CAMERAGROUP dbo 用户 2004-2-26 12:38:00
D_CAMPAIGN dbo 用户 2004-4-26 21:36:27
D_CASH_BACK dbo 用户 2003-11-12 19:40:38
D_CAST dbo 用户 2003-9-30 10:17:06
D_CAST_ACCESS dbo 用户 2003-9-30 10:17:06
D_CHAT dbo 用户 2004-2-26 12:38:00
D_CHAT_OPERATOR dbo 用户 2004-7-21 14:03:29
D_CHAT_OPERATOR_STATUS dbo 用户 2004-7-21 13:59:50
D_CHAT_SCHEDULE dbo 用户 2004-7-23 10:51:09
D_CHAT_SESSION dbo 用户 2004-7-13 17:25:45
D_CHAT_USER dbo 用户 2004-7-27 12:57:35
D_CHAT_WEBCHAT dbo 用户 2004-7-12 11:22:23
D_CHAT_WEBCHAT_ALBUM dbo 用户 2004-7-12 11:35:51
D_CHAT_WEBCHAT_SETTING dbo 用户 2004-7-15 14:16:09
D_CHAT_WEBCHAT_USER dbo 用户 2004-7-12 15:27:35
D_CHATROOM dbo 用户 2004-3-2 14:46:39
D_CHATROOM_SCHEDULE dbo 用户 2004-6-9 14:47:34
D_CLIENT dbo 用户 2003-12-8 17:42:44
D_CLUB dbo 用户 2003-9-30 10:17:06
D_CONFER_ADDRESS_BOOK dbo 用户 2004-4-26 17:08:23
D_CONFER_OPE_MATRIX dbo 用户 2004-5-28 16:23:45
D_CONFER_OPE_RANK dbo 用户 2004-5-10 11:00:36
D_CONFER_OPERATOR dbo 用户 2004-6-22 12:58:46
D_CONFER_RESULT dbo 用户 2004-7-5 17:15:21
D_CONFER_ROOM dbo 用户 2004-6-24 18:10:05
D_CONFER_TOTAL dbo 用户 2004-7-8 11:59:56
D_CONFER_USER dbo 用户 2004-5-17 16:34:48
D_CORRESPOND_MEMO dbo 用户 2004-4-15 20:19:46
D_CUSTOMER dbo 用户 2004-4-6 18:56:08
D_DEMAND_MAIL dbo 用户 2003-9-30 10:17:06
D_DEMAND_SET_MAIL dbo 用户 2003-9-30 10:17:06
D_DEMAND_SET_TEL dbo 用户 2003-9-30 10:17:06
D_EVENT dbo 用户 2004-4-6 10:33:52
D_EVENT_GROUP dbo 用户 2004-5-25 16:51:38
D_EVENT_PAYMENT dbo 用户 2004-4-6 10:34:39
D_EVENT_POINT dbo 用户 2004-4-6 10:35:10
D_EXEC_AUTH dbo 用户 2003-9-30 10:17:06
D_IMAGE_ALBUM dbo 用户 2004-6-3 19:01:18
D_IMAGE_PROF dbo 用户 2004-6-4 18:10:23
D_iPAL_NEWS dbo 用户 2004-1-13 18:41:44
D_LIMIT_DATE dbo 用户 2003-10-10 14:53:29
D_LIMIT_POINT dbo 用户 2004-7-9 14:35:16
D_MANAGE_IP dbo 用户 2003-9-30 10:17:06
D_MEMO dbo 用户 2003-9-30 10:17:06
D_MMAIL dbo 用户 2003-9-30 10:17:06
D_MOBILE_NO dbo 用户 2004-2-26 12:38:00
D_NEW_IMAGE dbo 用户 2004-2-26 12:38:00
D_OPEN dbo 用户 2003-9-30 10:17:06
D_PATRON_CONDITION dbo 用户 2004-6-3 11:01:49
D_PATRON_RANK_CONDITION dbo 用户 2004-6-3 11:01:49
D_PERIOD_POINT_BAT dbo 用户 2003-9-30 10:17:06
D_POINT_LEVEL dbo 用户 2003-9-30 10:17:06
D_PROF dbo 用户 2004-4-23 17:09:58
D_PROFILE_IMAGE_REFERENCE_RANK dbo 用户 2004-6-11 21:18:31
D_REPLACE dbo 用户 2003-9-30 10:17:06
D_SERVICE_USER dbo 用户 2003-12-8 17:51:52
D_SESSION dbo 用户 2004-4-20 16:53:53
D_START_WORD dbo 用户 2004-3-18 18:21:00
D_TABOO dbo 用户 2003-9-30 10:17:06
D_USER dbo 用户 2004-7-6 19:41:14
D_USER_ACCESS dbo 用户 2003-9-30 10:17:06
D_USER_DETAILS dbo 用户 2004-7-8 12:20:40
D_USER_INFO dbo 用户 2003-11-12 19:40:38
D_USER_LIVE dbo 用户 2004-3-31 18:20:02
D_USER_Q dbo 用户 2003-12-9 18:51:38
D_USER_RENTAL dbo 用户 2004-3-15 13:28:54
D_VOICE_PROFILE dbo 用户 2004-6-28 11:56:04
D_VOICE_PROFILE_EVENT dbo 用户 2004-7-6 10:10:11
D_VOICE_PROFILE_RESULT dbo 用户 2004-6-30 13:24:29
D_VOICE_PROGRAM dbo 用户 2004-7-1 16:25:05
D_VOICE_PROGRAM_EVENT dbo 用户 2004-7-6 10:09:09
D_VOICE_PROGRAM_GENRE dbo 用户 2004-7-2 13:41:54
D_VOICE_PROGRAM_RESULT dbo 用户 2004-7-5 15:36:11
D_VOICE_SETTING dbo 用户 2004-6-29 10:27:36
D_WEB_SERVICE_CUSTOMER dbo 用户 2003-12-9 17:21:55
dtproperties dbo 系统 2003-9-30 10:16:38
emsys_oshirase_bat dbo 用户 2004-7-2 20:19:21
errormail dbo 用户 2003-9-30 10:17:08
L_BATCHMAIL_SEND dbo 用户 2003-9-30 10:17:06
L_CHAT dbo 用户 2004-2-26 12:38:00
L_CHAT_WEBCHAT dbo 用户 2004-7-12 11:23:58
L_CHAT_WEBCHAT_IMAGE dbo 用户 2004-7-12 11:44:40
L_CMAIL dbo 用户 2003-9-30 10:17:06
L_CONFER_REQUEST dbo 用户 2004-7-5 13:17:30
L_CONFER_REQUEST_CANCEL dbo 用户 2004-5-13 16:21:50
L_CONFER_RESPONSE dbo 用户 2004-7-5 13:26:14
L_CONFER_ROOM dbo 用户 2004-5-12 18:46:58
L_D_RESERVE_MAIL dbo 用户 2004-4-7 14:44:42
L_DEMAND_CALL_TEL dbo 用户 2003-9-30 10:17:06
L_DEMAND_LOG_MAIL dbo 用户 2003-9-30 10:17:06
L_DEMAND_LOG_MAIL_BAT dbo 用户 2003-9-30 10:17:06
L_DEMAND_SET_MAIL dbo 用户 2003-9-30 10:17:06
L_DEMAND_SET_TEL dbo 用户 2003-9-30 10:17:06
L_DENY_ACCESS dbo 用户 2003-10-2 10:30:32
L_EVENT_LOG dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_CORRECTION dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_DETAILS dbo 用户 2004-3-10 21:45:13
L_EVENT_LOG_DETAILS_CORRECTION dbo 用户 2004-3-18 19:50:55
L_EVENT_LOG_DIV_BAT dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_MONEY dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_MONEY_CORRECTION dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_POINT dbo 用户 2003-9-30 10:17:06
L_EVENT_LOG_POINT_CORRECTION dbo 用户 2003-9-30 10:17:07
L_EVENT_LOG_SUM dbo 用户 2003-9-30 10:17:07
L_EVENT_LOG_SUM_BAT dbo 用户 2003-9-30 10:17:07
L_EVENT_LOG_USER dbo 用户 2003-10-16 14:22:03
L_EVENT_LOG_USER_CORRECTION dbo 用户 2003-10-16 14:21:54
L_iPAB dbo 用户 2004-2-9 14:10:44
L_MAIL_COUNT2 dbo 用户 2004-5-25 9:48:59
L_NEWS dbo 用户 2003-9-30 10:17:07
L_NOTE dbo 用户 2003-9-30 10:17:07
L_NOTE_COMMENT dbo 用户 2003-9-30 10:17:07
L_NOTE_REPLACE dbo 用户 2003-9-30 10:17:07
L_OPEN dbo 用户 2003-9-30 10:17:07
L_OPEN_WRITE_LOG dbo 用户 2003-12-11 13:18:05
L_PAO_BATCHMAIL_SEARCH dbo 用户 2003-9-30 10:17:07
L_PATRON_EVENT_LOG dbo 用户 2004-6-3 11:01:49
L_PERIOD_POINT dbo 用户 2003-9-30 10:17:07
L_PERIOD_POINT_BAT dbo 用户 2003-9-30 10:17:07
L_PROF dbo 用户 2004-6-15 16:03:26
L_SETTLE dbo 用户 2004-6-28 12:55:24
L_SETTLE_CREDIT dbo 用户 2003-10-9 16:46:41
L_SETTLE_iPAB dbo 用户 2003-9-30 10:17:07
L_SETTLE_iPAP dbo 用户 2003-9-30 10:17:07
L_TRACE_LOG dbo 用户 2004-7-7 13:31:55
L_USER_CHANGE dbo 用户 2003-9-30 10:17:07
L_USER_CHANGE_BAT dbo 用户 2003-9-30 10:17:07
L_USER_CHANGE_M_USER_LEVEL dbo 用户 2003-9-30 10:17:07
L_USER_CHANGE_M_USER_STAT dbo 用户 2003-9-30 10:17:07
L_USER_CHANGE_M_USER_TYPE dbo 用户 2003-9-30 10:17:07
L_USER_REG dbo 用户 2003-9-30 10:17:07Top
5 楼Cityfire（青团子）回复于 2004-08-05 10:09:46 得分 25
SELECT TOP 4 * FROM
(
SELECT D_PROF.*,D_USER.USER_NAME, D_OPEN.UPDATE_DATE FROM D_PROF
INNER JOIN D_USER ON D_PROF.D_USER_ID = D_USER.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1 AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.DEL_FLAG=0
where DERIVEDTBL.D_PROF_ID NOT IN -------------这里where应该为and
(SELECT TOP 4 D_PROF.D_PROF_ID FROM D_PROF
INNER JOIN D_USER ON D_USER.D_USER_ID = D_PROF.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID
AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in
(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF
ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID
AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0
AND D_PROF.ISOPEN=1 AND D_PROF.ADMIN_ISOPEN=1 AND D_PROF.DEL_FLAG=0
ORDER BY D_OPEN.UPDATE_DATE desc
)
) a ------这里缺少括号应该加上") 别名"
ORDER BY UPDATE_DATE desc
Top
6 楼MPU（黑眼圈）回复于 2004-08-05 19:01:48 得分 0
SELECT TOP 4 D_PROF.*,D_USER.USER_NAME, D_OPEN.UPDATE_DATE ,D_IMAGE_PROF.STATUS,D_IMAGE_PROF.D_IMAGE_PROF_ID,D_OPEN.CREATE_DATE OPEN_DATE,D_OPEN.SUBJECT,D_OPEN.CONTENT
FROM D_PROF
INNER JOIN D_USER ON D_PROF.D_USER_ID = D_USER.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1 AND D_OPEN.UPDATE_DATE<getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID=D2.D_USER_ID))
D_IMAGE_PROF ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0 AND D_PROF.ISOPEN=1
AND D_PROF.DEL_FLAG=0
AND D_PROF.D_PROF_ID NOT IN
(
SELECT TOP 4 D_PROF.D_PROF_ID
FROM D_PROF
INNER JOIN D_USER ON D_USER.D_USER_ID = D_PROF.D_USER_ID
INNER JOIN D_OPEN ON D_PROF.D_PROF_ID = D_OPEN.D_PROF_ID AND D_OPEN.ISOPEN= 1 AND D_OPEN.ADMIN_ISOPEN= 1AND D_OPEN.UPDATE_DATE < getdate()
INNER JOIN
(SELECT * FROM D_IMAGE_PROF D1 where D_IMAGE_PROF_ID in(SELECT top 1 D_IMAGE_PROF_ID FROM D_IMAGE_PROF D2 where D1.D_USER_ID = D2.D_USER_ID))
D_IMAGE_PROF ON D_USER.D_USER_ID = D_IMAGE_PROF.D_USER_ID AND D_IMAGE_PROF.DEL_FLAG = 0 AND D_IMAGE_PROF.STATUS = 0
WHERE D_USER.DEL_FLAG=0 AND D_PROF.ISOPEN=1 AND D_PROF.ADMIN_ISOPEN=1 AND D_PROF.DEL_FLAG=0
ORDER BY D_OPEN.UPDATE_DATE desc
)Top
