现有一表Tb…其中含有字段Xm,csrq(姓名,出生日期,都是字符型),现在想将姓名和出生日期重复的记录找出来…不知道怎么写…求教…谢谢…我写了个Ms不对Select * from tb where xm in (select xm from tb group by xm having count(xm)>1) and csrq in (select csrq from tb group by csrq having count(csrq)>1) order by csrq
调试欢乐多
select xm,csrq,count(*) from tb group by xm,csrq having(count(*)>0);
--呵呵 上面错了,是>1
select * from tb group by xm,csrq having(count(*)>1);
with tab as (
select t.*, count(1) over (partition by xm,csrq) cnt from tb t
)
select * from tab where cnt>1;
SELECT Xm,csrq,COUNT(*)OVER(PARTITION BY Xm,csrq) R FROM TAB
) XX
WHERE XX.R>1
where (select count(*) from tb b where a.xm=b.xm and a.csrq=b.csrq)>1Select * from tb where (xm,csrq) in(select (xm,csrq) from tb group by xm,csrq having count(*)>1)
orselect * from (
select a.*,count(*) over(partition by xm,csrq order by 1) cnt from tb a) where cnt>1