SQL语句错在那里？

[wss1801]

select * from member a
where (a.nameA,a.age) in (select nameA,age from member group by nameA,age having count(*) > 1)

报错：',' 附近有语法错误。

当我按下面写时没有问题，就多了一个字段，可别人说能行，不明白？

select * from member a
where (a.nameA) in (select nameA from member group by nameA having count(*) > 1)

[wuhq030710914]

select * from member a       
where a.nameA in (select nameA from member group by nameA having count(*) > 1) and a.age in(select age from member group by age having count(*) > 1)

[wss1801]

不是吧，我怎么看网上别人给我写的有多个字段？不明白？.....?

[zahhb]

可能是你的字符','打错了，语句没有问题。
你切换下输入法看看

[wss1801]

7楼的大哥的是可以的，但这样写不是太麻烦呀？

[wss1801]

select * from tableA a  
where (a.nameA,a.sex) in (select   nameA,sex   from   tableA   group  by   nameA,sex     having   count(*)   >   1)

没写错字符？还有什么原因？

[菜鸟笔记公众号]

学习哈..呵呵

[leaohong]

谁说这样能用啦，上面说能用的用来试试？

多条件限制应该用exists

[yqlvcxl_2007]

如果是你没有写错字段名或则是字符写错了(如：用了全角之类）的话，你的sql没有问题

[yqlvcxl_2007]

忘了提醒，该语句在oracle中好使，但是在sqlserver中不好使

[伴老思源]

还是一个一个yin 吧

[badnews]

把 in 改成 exists

[地下室小红叔]

可以用临时表实现 示例如下：

select autoId+1 as newAutoId,* into #temp from member  --这里假设你的member表里已经有一个自增字段
--如果没有自增字段 将上句改为 select identity(int,1,1) as newAutoId,* into #temp from member
select min(newAutoId) as newAutoId into #temp2 from #temp group by nameA,age having count(*)>1
select * from #temp a,#temp2 b where a.newAutoId=b.newAutoId --这里选中的是重复行的每一条记录 多了一列newAutoId 当然你可将其过滤掉。
--然后就可以按你的要求写自已的sql了 这里会了吧
drop table #temp
drop table #temp2

[pt1314917]

select * from  member a where exists(select 1  from  member where nameA=a.nameA and age=a.age  group  by  nameA,age having  count(*)>1) 

[wss1801]

哈哈。

[fcuandy]

写法太多,随便写出几种,不一一列举了.

根具不同的索引和数据匹配率,不同的语句效率也不一致.

受索引和匹配率影响较小,且在各种情况下效率排前的写法,推荐 e,f,g. 没索引的话,c,d最快. exists写法受匹配率制约,在有索引的情况下,最快也是界于内连和in之间.

--a
SELECT * FROM member a
	WHERE EXISTS(SELECT 1 FROM member GROUP BY namea,age 
			WHERE namea=a.namea AND age=a.age
			GROUP BY namea,age
			HAVING COUNT(*)>1
			)
--b
SELECT * FROM member a
	WHERE EXISTS(SELECT 1 FROM member GROUP BY namea,age 
			WHERE CHECKSUM(namea,age)=CHECKSUM(a.namea,a.age)
			GROUP BY namea,age
			HAVING COUNT(*)>1
			)
--c
SELECT * FROM member
	WHERE CHECKSUM(namea,age) IN
	(
		SELECT CHECKSUM(namea,age) FROM member GROUP BY namea,age 
			GROUP BY namea,age
			HAVING COUNT(*)>1
	)
--d
SELECT * FROM member
	WHERE RTRIM(namea) + ',' + RTRIM(age) IN
	(
		SELECT RTRIM(namea) + ',' + RTRIM(age) FROM member GROUP BY namea,age 
			GROUP BY namea,age
			HAVING COUNT(*)>1
	)
--e
SELECT a.* FROM member a
INNER JOIN
	(
		SELECT CHECKSUM(namea,age) cm FROM member GROUP BY namea,age 
			GROUP BY namea,age
			HAVING COUNT(*)>1
	) b
	ON CHECKSUM(namea,age)=cm
--f
SELECT a.* FROM member a
INNER JOIN
	(
		SELECT namea,age FROM member GROUP BY namea,age 
			GROUP BY namea,age
			HAVING COUNT(*)>1
	) b
	ON a.namea=b.namea AND a.age=b.age
--g
SELECT a.* FROM member a
INNER JION
	(
		SELECT RTRIM(namea) + ',' + RTRIM(age) nameAge FROM member GROUP BY namea,age 
			GROUP BY namea,age
			HAVING COUNT(*)>1
	) b
	ON  RTRIM(namea) + ',' + RTRIM(age)=nameAge

[地下室小红叔]

16楼的可以

[地下室小红叔]

18楼的丰富

[dianlongliu]

mark

[likevs]

看到楼主的错误提示，如果不是“，”号在中文模式下输入，那就是楼上几位朋友所说的，IN前不能带两个字段。

换成=ANY(子查询)，不成就用21楼的吧，不然SQL里设AND这个逻辑与运算就没什么用了！！

[pt1314917]

wss1801

等 级：
发表于：2007-12-13 14:43:2629楼 得分:0
明白了，谢了！
-------------------
楼主极不厚道。。有了正确答案不结帖。。。
强烈建议将楼主拉进黑名单。以后不答此人的贴。