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)
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)
解决方案 »
- 水晶报表Basic公式中判断一个字段的值是不是数字,我该怎么判断?
- 给输入的字符加 分隔符
- 关于平均数 精确度 的问题
- 问了几次都没解决的问题~~~~~~~
- 在asp.net如何让浏览器支持下载中文文件名?
- 高分求助
- 高分求解!!!asp.net网站上传以后的问题,急!急!急!
- web services调用的问题,请大家帮忙看看!
- 那里有oracle9.2 for windows2000的下载!!急
- 请推荐几本ASP.NET的好书!
- linkbuttion在有些机子上不能执行
- 用的是.NET2005自带的水晶报表,组轴列太多,导致显示的字段重叠,请问有什么办法解决,最好是能把组轴的文字的显示角度,如竖排
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)
where (a.nameA,a.sex) in (select nameA,sex from tableA group by nameA,sex having count(*) > 1)没写错字符?还有什么原因?
--如果没有自增字段 将上句改为 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
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)
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
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)
之后为:select * from member a
where a.nameA in (select nameA from member group by nameA,age having count(*) > 1) and a.age in(select age from member group by nameA,age having count(*) > 1)
不知道对不对?
这种语句你应该能理解吧, 不从tb表中查数据,只产生一个列,列值为常量, 当表中有记录时才能产生这个常量列.
表中无记录时,就不会产生行.接下来select 1 from tb where id>5用了个过滤,
也能理解吧
即只有表中存在 id>5的记录时,才会产生列值为1的行.这里写1,跟你写你的字段名并无区别, 而上面回贴中的语句 就是指只要子查询的where条件满足,就能产生行的存在, 那么对于外部查询,它的where 后面的表达式的结果就为 true
where (a.nameA,a.age) in (select nameA,age from member group by nameA,age having count(*) > 1)
::::::会不会是因为你的age字段中有空值,以前我碰到过。你用sql profile监视一下传到sql server 的sql 语句,看看有没有空的age字段。
http://hi.baidu.com/850317
进来学习的
占个座
等 级:
发表于:2007-12-13 14:43:2629楼 得分:0
明白了,谢了!
-------------------
楼主极不厚道。。有了正确答案不结帖
强烈建议将楼主拉进黑名单。以后不答此人的贴。