为何在sql中会报错
日期：2014-05-18 浏览次数：20556 次
为什么在sql中会报错
下面语句是找出cardtran中重复的记录，我在oracle中可以运行，但在sql2k中一直报错，不知道错在什么地方，请各位大虾指教了。
SELECT *
FROM cardtran
WHERE (cardno, balance) IN
(SELECT cardno, balance
FROM cardtran
GROUP BY (cardno, balance)
HAVING COUNT(*) > 1
------解决方案--------------------
SELECT *
FROM cardtran
WHERE (cardno, balance) IN
(SELECT cardno, balance
FROM cardtran
GROUP BY (cardno, balance)
HAVING COUNT(*) > 1)
不能这样IN的。
下面语句是找出cardtran中重复的记录，我在oracle中可以运行，但在sql2k中一直报错，不知道错在什么地方，请各位大虾指教了。
SELECT *
FROM cardtran
WHERE (cardno, balance) IN
(SELECT cardno, balance
FROM cardtran
GROUP BY (cardno, balance)
HAVING COUNT(*) > 1
------解决方案--------------------
SELECT *
FROM cardtran
WHERE (cardno, balance) IN
(SELECT cardno, balance
FROM cardtran
GROUP BY (cardno, balance)
HAVING COUNT(*) > 1)
不能这样IN的。
- SQL code
SELECT a.*
FROM cardtran a join
(SELECT cardno, balance
FROM cardtran
GROUP BY (cardno, balance)
HAVING COUNT(*) > 1) b on a.cardno = b.cardno and a.balance = b.balance
------解决方案--------------------
- SQL code
select t1.*
from cardtran t1
where exists (
select 1 from cardtran t2
where t1.cardno=t2.cardno and t1.balance=t2.balance
group by t2.cardno,t2.balance
having count(1)>1
)
------解决方案--------------------
其實二樓的改一點就可以了
- SQL code
SELECT a.*
FROM cardtran a join
(SELECT cardno, balance
FROM cardtran
GROUP BY cardno, balance--去掉括號
HAVING COUNT(*) > 1) b on a.cardno = b.cardno and a.balance = b.balance
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
