SELECT *
FROM (SELECT *
FROM (SELECT c. * , r. *
FROM RIGHT_TEST AS c
LEFT JOIN authorize_table AS r ON r.TABLE_NO =1
AND c.TABLE_ID = r.TABLE_REC_ID
AND (
(
PUB_IN_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR (
PUB_out_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
)
ORDER BY r.PRI DESC
) AS z
GROUP BY z.TABLE_ID
ORDER BY z.TABLE_ID ASC
) AS s
WHERE (
PUB_IN_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR (
PUB_OUT_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
OR (
PUB_IN_RIGHT_TYPE <= -10
AND PEOPLE_SET_TYPE =2
AND PEOPLE_SET_ID1 =1
AND REC_RIGHT_TYPE >0
)
OR (
PUB_IN_RIGHT_TYPE <= -11
AND PEOPLE_SET_TYPE IS NULL
) 这句SQL在MYSQL 与ORACLE 运行没问题。而是在server系列服务器上运行出现问题。
比如
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
和
选择列表中的列 'z.NAME' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。我主要目的想过滤没指定的数据根据ID来过滤。
FROM (SELECT *
FROM (SELECT c. * , r. *
FROM RIGHT_TEST AS c
LEFT JOIN authorize_table AS r ON r.TABLE_NO =1
AND c.TABLE_ID = r.TABLE_REC_ID
AND (
(
PUB_IN_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR (
PUB_out_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
)
ORDER BY r.PRI DESC
) AS z
GROUP BY z.TABLE_ID
ORDER BY z.TABLE_ID ASC
) AS s
WHERE (
PUB_IN_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR (
PUB_OUT_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
OR (
PUB_IN_RIGHT_TYPE <= -10
AND PEOPLE_SET_TYPE =2
AND PEOPLE_SET_ID1 =1
AND REC_RIGHT_TYPE >0
)
OR (
PUB_IN_RIGHT_TYPE <= -11
AND PEOPLE_SET_TYPE IS NULL
) 这句SQL在MYSQL 与ORACLE 运行没问题。而是在server系列服务器上运行出现问题。
比如
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
和
选择列表中的列 'z.NAME' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。我主要目的想过滤没指定的数据根据ID来过滤。
张三 1 21 2 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -11 0 2 1 1 NULL NULL NULL NULL 4 GG 1 1 2 1 NULL NULL NULL 1 NULL NULL 4
张三 1 21 2 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -11 0 2 1 1 NULL NULL NULL NULL 2 Sr 1 1 2 1 NULL NULL NULL 1 NULL NULL 2
张三 1 21 2 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -11 0 2 1 1 NULL NULL NULL NULL 1 Mr 1 1 2 1 NULL NULL NULL 1 NULL NULL 1
王五 1 19 2 3 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -11 0 2 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
换成not(not a and not b and not c)的形式
错误说的很清楚了。
只是格式化一下:SELECT *
FROM (SELECT *
FROM (SELECT c.*
,r.*
FROM RIGHT_TEST AS c
LEFT JOIN authorize_table AS r
ON r.TABLE_NO = 1
AND c.TABLE_ID = r.TABLE_REC_ID
AND ((PUB_IN_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE = 2
AND PUC_DEPART_ID1 = 1)
OR (PUB_out_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE = 2
AND PUC_DEPART_ID1 != 1))
ORDER BY r.PRI DESC) AS z
GROUP BY z.TABLE_ID
ORDER BY z.TABLE_ID ASC) AS s
WHERE (PUB_IN_RIGHT_TYPE > 0
AND PUC_DEPART_TYPE = 2
AND PUC_DEPART_ID1 = 1)
OR (PUB_OUT_RIGHT_TYPE > 0
AND PUC_DEPART_TYPE = 2
AND PUC_DEPART_ID1 != 1)
OR (PUB_IN_RIGHT_TYPE <= -10
AND PEOPLE_SET_TYPE = 2
AND PEOPLE_SET_ID1 = 1
AND REC_RIGHT_TYPE > 0)
OR (PUB_IN_RIGHT_TYPE <= -11
AND PEOPLE_SET_TYPE IS NULL )
呵呵, 我使用的是server2005
SELECT *
FROM
( SELECT z.TABLE_ID,
z.[NAME] ,
z.*
FROM
( SELECT c. * ,
r. *
FROM RIGHT_TEST AS c
LEFT JOIN authorize_table AS r
ON r.TABLE_NO =1
AND c.TABLE_ID = r.TABLE_REC_ID
AND
(
(
PUB_IN_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR
(
PUB_out_RIGHT_TYPE <= -10
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
)
) AS z
GROUP BY z.TABLE_ID,
z.[NAME]
) AS s
WHERE
(
PUB_IN_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 =1
)
OR
(
PUB_OUT_RIGHT_TYPE >0
AND PUC_DEPART_TYPE =2
AND PUC_DEPART_ID1 !=1
)
OR
(
PUB_IN_RIGHT_TYPE <= -10
AND PEOPLE_SET_TYPE =2
AND PEOPLE_SET_ID1 =1
AND REC_RIGHT_TYPE >0
)
OR
(
PUB_IN_RIGHT_TYPE <= -11
AND PEOPLE_SET_TYPE IS NULL
)
ORDER BY PRI DESC,
TABLE_ID ASC类似这样吧,不太清楚你的table structure,不过group by不能与*连用吧