select count(*) from 表 A inner join (select serial from 表 where validate = 0 group by serial having count(*) = 2) B on A.serial = B.serial where A.result > 3
是这意思吗:select count(*) from (select serial from (select * from 表 where result>3 and validate=0) group by serial having (count(*)=2)) T
回复人: scmail81(freedom) ( ) 信誉:100 2005-12-03 08:31:00 得分: 0 正是这个逻辑 我写的 select count(*) from (select serial from (select * from paper where result>3 and validate=0) group by serial having (count(*)=2)) 不让通过,在sql server中说group附近有语法错误 好像不允许from (select * from paper where result>3 and validate=0) 这样的写法
哦 !! 是这样! select count(*) from (select serial from (select * from 表 where result>3 and validate=0) W group by serial having (count(*)=2)) T
这张表 (select * from paper where result>3 and validate=0) 差个别名同时 (select serial from (select * from paper where result>3 and validate=0) group by serial having (count(*)=2)) 也少个别名
如scmail81(freedom)所写的 select count(*) from (select serial from (select * from paper where result>3 and validate=0)M group by serial having (count(*)=2)) 要加个别名M,这样才能把集合当表用
select count(*) from (select serial from (select * from 表 where result>3 and validate=0 union select * from 表 where result<2 and validate=0) W group by serial having (count(*)=2)) T
select count(*)
from 表 A inner join (select serial from 表 where validate = 0 group by serial having count(*) = 2) B
on A.serial = B.serial
where A.result > 3
回复人: lw1a2(一刀 [email protected]) ( ) 信誉:100
搜索的是所有记录
要求的是 validate的serial出现两次算一次啊
结果不对啊
(select serial from (select * from 表 where result>3 and validate=0) group by serial having (count(*)=2)) T
正是这个逻辑 我写的
select count(*) from (select serial from (select * from paper where result>3 and validate=0) group by serial having (count(*)=2))
不让通过,在sql server中说group附近有语法错误 好像不允许from (select * from paper where result>3 and validate=0) 这样的写法
是这样!
select count(*) from
(select serial from (select * from 表 where result>3 and validate=0) W group by serial having (count(*)=2)) T
也少个别名
select count(*) from (select serial from (select * from paper where result>3 and validate=0)M group by serial having (count(*)=2))
要加个别名M,这样才能把集合当表用
还有个问题,就是这两个serial的,要搜索一个>3的,一个<2的,而不是两个都>3,该怎么写?
解决了马上结贴 :)
(select serial from (select * from 表 where result>3 and validate=0
union
select * from 表 where result<2 and validate=0) W group by serial having (count(*)=2)) T