select * from mmsp213 a where a.mtr_no in (select mtr_no from mmsp213 group by mtr_no having count(mtr_no)>1) and price_date='2011-5-1' 这样查的 mtr_no 这一列没有一个是重复的 是怎么回事??
select * from mmsp213 a where a.price_date='2011-5-1' and a.mtr_no in (select mtr_no from mmsp213 where price_date='2011-5-1' group by mtr_no having count(mtr_no)>1)
SELECT * from (select *,COUNT(mtr_no)OVER(PARTITION BY mtr_no) AS con from mmsp213 where price_date='2011-5-1' )t WHERE con>1
先执行下面这句看看结果,where条件先不要加 select * from mmsp213 a where a.mtr_no in (select mtr_no from mmsp213 group by mtr_no having count(mtr_no)>1)
不加 where price_date='2011-5-1' 查出来很多条,加这个条件查出来21条 但是有几条日期不是2011-5-1
select * from mmsp213 a where a.mtr_no in (select mtr_no from mmsp213
where price_date='2011-5-1'
group by mtr_no having count(mtr_no)>1)
from mmsp213 a
where a.mtr_no in
(select mtr_no from mmsp213 group by mtr_no having count(mtr_no)=1) and price_date='2011-5-1'
FROM (SELECT *,ROW_NUMBER()over(PARTITION BY mtr_no ORDER BY mtr_no) AS row FROM mmsp213 WHERE price_date='2011-5-1')t WHERE row=1
price_date='2011-5-1'
--找重复记录的语句没错,估计是这里需要转换下,convert(datetime,'2011-5-1')
select * from mmsp213 a where a.price_date='2011-5-1' and a.mtr_no in (select mtr_no from mmsp213
where price_date='2011-5-1'
group by mtr_no having count(mtr_no)>1)
from
(select *,COUNT(mtr_no)OVER(PARTITION BY mtr_no) AS con from mmsp213 where price_date='2011-5-1' )t
WHERE con>1
select * from mmsp213 a where a.mtr_no in (select mtr_no from mmsp213 group by mtr_no having count(mtr_no)>1)