oracle里group by 和having count的问题
答案:3 悬赏:60
解决时间 2021-03-06 10:19
- 提问者网友:刺鸟
- 2021-03-06 04:19
oracle里group by 和having count的问题
最佳答案
- 二级知识专家网友:夜余生
- 2021-03-06 05:29
--1,查询hm有重复的记录
select hm,count(*) from a group by hm having count(*)>1
--2,查询hm和xm都有重复
select hm,xm count(*) from a group by hm,xm having count(*)>1
追问:我还要其他字段
追答:--1,查询hm有重复的记录
select a.* from a,(select hm,count(*) from a group by hm having count(*)>1) b where a.hm=b.hm
--2,查询hm和xm都有重复
select a.* from a,(select hm,xm count(*) from a group by hm,xm having count(*)>1) b where a.hm=b.hm and a.xm=b.xm
select hm,count(*) from a group by hm having count(*)>1
--2,查询hm和xm都有重复
select hm,xm count(*) from a group by hm,xm having count(*)>1
追问:我还要其他字段
追答:--1,查询hm有重复的记录
select a.* from a,(select hm,count(*) from a group by hm having count(*)>1) b where a.hm=b.hm
--2,查询hm和xm都有重复
select a.* from a,(select hm,xm count(*) from a group by hm,xm having count(*)>1) b where a.hm=b.hm and a.xm=b.xm
全部回答
- 1楼网友:旧脸谱
- 2021-03-06 07:28
select后面除了组函数的查询项都得放到group by子句后面,having 子句的作用是筛选满足条件的组,即在分组之后过滤数据.语句这样写:
select xh,xm,hm from a group by xh,hm,xm having count(hm)>1
select xh,xm,hm from a group by xh,hm,xm having count(hm)>1
- 2楼网友:西岸风
- 2021-03-06 07:01
hm有重复 是group by hm having count(hm)>1
但它返回的是去重的hm 例如
hm有两个重复值 10 但查询它只返回一个10
所以要查xh,xm,hm必须 使用
select xh,xm,hm from a where hm in(select hm from a group by hm having count(hm)>1)
追问:关键是我查的时候一条记录都返回不了我举得例子只是把我的代码简化了 其实还有很多关联 比如你的括号里的hm是很多关联后取到的字段
追答:最好把你的语句简要贴出来,这样才好给你解释
但它返回的是去重的hm 例如
hm有两个重复值 10 但查询它只返回一个10
所以要查xh,xm,hm必须 使用
select xh,xm,hm from a where hm in(select hm from a group by hm having count(hm)>1)
追问:关键是我查的时候一条记录都返回不了我举得例子只是把我的代码简化了 其实还有很多关联 比如你的括号里的hm是很多关联后取到的字段
追答:最好把你的语句简要贴出来,这样才好给你解释
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯