想查询某表里4个字段数据完全一样的记录信息?该信息应该怎么查? 如表:t_jc_szcg_process 有字段:tasknum,recid,logid,eventstateid,dbversionid unitid 等
想查询tasknum,recid,logid,eventstateid 字段相同的记录怎么查?
select * from t_jc_szcg_process where (tasknum,recid,logid,eventstateid) in
(select tasknum,recid,logid,eventstateid from t_jc_szcg_process
group by tasknum,recid,logid,eventstateid having count(tasknum)>1) 这样查 没有查出我想要的效果。
想查询tasknum,recid,logid,eventstateid 字段相同的记录怎么查?
select * from t_jc_szcg_process where (tasknum,recid,logid,eventstateid) in
(select tasknum,recid,logid,eventstateid from t_jc_szcg_process
group by tasknum,recid,logid,eventstateid having count(tasknum)>1) 这样查 没有查出我想要的效果。
and logid=eventstateid
select * from(select a.*,count(1)over(partition by tasknum,recid,logid,eventstateid)c
from t_jc_szcg_process a)
where c>1
正解,不过 count(1)over 这里有点不明白。
(select tasknum,recid,logid,eventstateid from t_jc_szcg_process group by tasknum,recid,logid,eventstateid having count(1) > 1) n
where tasknum = m.tasknum and recid = m.recid and logid = m.logid and eventstateid = m.eventstateid)
但不等于count(colname)因为字段值可能为空,空值不加入统计