SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM < 54
改为:
select * from
(SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE () )
t WHERE ROWNUM < 54
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM < 54
改为:
select * from
(SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE () )
t WHERE ROWNUM < 54
delete zy_fymx002 a
where exists(条件)条件:
select * from(
SELECT *
FROM (SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM < 54
) b where a.mxid00=b.mxid00--********************************
delete zy_fymx002 a
where exists(条件)在这一句中,只有一个exists条件.只要该条件存在(成立),系统就会删除表zy_fymx002中的全部数据,而与你的条件中的rownum<54无关,也就是说,rownum<54只是条件,只要第一句中的 exists(条件存在) where exists(条件) 就成了where 1=1 这样条件总是为真(TRUE),那么
delete zy_fymx002 a where exists(条件) 只要条件存在(成立),系统就会删除表中的所有数据,而不是rownum<54 行.
where exists(
select * from(
SELECT *
FROM (SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM < 54) b where a.mxid00=b.mxid00)
WHERE EXISTS (
SELECT *
FROM (SELECT *
FROM zy_fymx002
WHERE ROWNUM < 54 AND xmbh00 = 124
ORDER BY SYS_GUID ()) b
WHERE a.mxid00 = b.mxid00)
where exists(
select * from(
SELECT *
FROM (SELECT *
FROM zy_fymx002
WHERE xmbh00 = 124
ORDER BY SYS_GUID())
WHERE ROWNUM < 54) b where a.mxid00=b.mxid00)
----> 只要该条件存在(成立),系统就会删除表zy_fymx002中的全部数据,<-------而与你的条件中的rownum<54无关,也就是说,rownum<54只是条件,只要第一句中的 exists(条件存在) where exists(条件) 就成了where 1=1 这样条件总是为真(TRUE),那么
delete zy_fymx002 a where exists(条件) 只要条件存在(成立),系统就会删除表中的所有数据,而不是rownum<54 行.也就是说,你这句程序并不能达到随机删除多少行的目的.
delete zy_fymx002 a where exists(条件) 这一句,只要条件成立,就将执行:
delete zy_fymx002
我明白您的意思了,您的意思是说,如果用EXISTS语句是无法达到我所需要的目的。只能用IN,对不????