group by having count(*) > 1 的問題

[baiynije]

有如下幾個SQL
1.
select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
當執行以上這條時結果如下
manno newno
----- -----
C618-0800123 230A
C618-0800123 230A
C618-0800123 230A

2.
select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno
當執行這條時結果如下
manno newno
----- -----
C618-0800123 230A

3.
select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count( *) >1

當執行這條時結果也和第2條的結果一樣，不知道是怎么回事？請大家指教

[wwwwb]

将满足having count( *) >1的记录找出来？

[baiynije]

象上面的數據得出的結果應該是沒有記錄，因為manno,newno都是一樣的

[wwwwb]

select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count( *)=1

[baiynije]

manno cardno prosscode newno
----- ------ --------- -----
C618-0800123 001 230 230A
C618-0800123 002 230 230A
C618-0800123 003 230 230A

象這些數據，因為manno和newno 都是一樣的，所以分組後只有一個，所以不應該出來，我要的是同一個manno 對應有兩個以上不同的newno的資料。不知道如何寫呢？

[wwwwb]

select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count( *)=1
试试吧

[aizong]

baiynije :把字段列表加上count(*)看看就知道你的想法对不对了.

[baiynije]

那我如何能得到我想要的數據呢？

[wwwwb]

贴记录及正确结果出来看看

[baiynije]

[Quote=引用 19 楼 wwwwb 的回复:]
select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count( *)=1
试试吧
[/Quote]
這樣出來的是不是manno,newno一對一的數據？
我要的是manno,newno,一對多的數據

[baiynije]

manno cardno prosscode newno
----- ------ --------- -----
C618-0800123 001 230 230A
C618-0800123 002 230 230A
C618-0800123 003 230 230C
如果資料是這樣的，那這種資料就應該出來，因為C618-0800123對應有兩個newno

[wwwwb]

就是说找出
C618-0800123 003 230 230C
这条记录？如是
select a.* from tt a inner join
(select manno,newno
from tt
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count(*)=1 ) b
on a.manno=b.manno and a.newno=b.newno

[baiynije]

不好意思，可能是我沒說清楚，我要的是把這種一個manno對應多個newno記錄的manno找出來，謝謝！

[wwwwb]

select a.* from tt a inner join
(select manno,newno
from tt
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count(*)>=2 ) b
on a.manno=b.manno and a.newno=b.newno

呵呵，要准确表达自己的意思不容易。

[wwwwb]

就是说找出
manno cardno prosscode newno
----- ------ --------- -----
C618-0800123 001 230 230A
C618-0800123 002 230 230A
记录？

[baiynije]

其實就是找出manno與newno就行了，條件就是manno有對應2個以上的newno ，如果一個manno只對應一個newno則不用出來

[baiynije]

也可以說我想得到分組後組數大於1的資料

[wwwwb]

select a.manno,a.newno from tt a inner join
(select manno,newno
from tt
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count(*)>=2 ) b
on a.manno=b.manno and a.newno=b.newno

[WWWWA]

[Quote=引用 29 楼 baiynije 的回复:]
其實就是找出manno與newno就行了，條件就是manno有對應2個以上的newno ，如果一個manno只對應一個newno則不用出來
[/Quote]
select a.manno,a.newno from tt a inner join
(select manno,newno
from tt
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno having count(*)>=2 ) b
on a.manno=b.manno and a.newno=b.newno
用这个试试

[suners]

jnhjnm

[a282501584]

select manno,newno
from y_sfcfsh
where manno ='C618-0800123' and prosscode ='230' and newno <> '' and newno <> null and status='3'
group by manno,newno