用SQL语句查询重复值与差异值。(100)

用SQL语句查询重复值和差异值。给分100，请快速回复！现有一工时表,Table Name:gongshi。有三列数据，分别为partname、price和work。拟列如下10条记录：partname price work1049246 1.50 刨床1412524 0.37 坡口1412524 0.18 坡口1633729 0.25 焊接1633729 0.18 小弯1935894 1.35 下料1935894 1.35 下料2417385 6.60 下料2417389 3.50 下料1049246 1.50 刨床要示用SQL 语句分别实现2条查询1.查询重复值，即partname、price、work完全一致的所有记录。执行结果如下：partname price work1049246 1.50 刨床1049246 1.50 刨床1935894 1.35 下料1935894 1.35 下料2.查询差异值值，即partname、work两列相同但price不一致的所有记录。执行结果如下：partname price work1412524 0.37 坡口1412524 0.18 坡口请尽快回复，执行成功后立即散分。感谢关注。

抛砖引玉1、select a.partname, a.price, a.work from gongshi aright join (select partname, price, work, count(*) as iCount from gongshi group by partname, price, work) b on a.partname = b.partnamewhere b.icount > 1order by partname;

2、select a.partname, a.price, a.work from gongshi aright join (select partname, work, count(*) as iCount from gongshi group by partname, work) b on a.partname = b.partnamewhere b.icount > 1and a.work not in(select distinct a.work from gongshi aright join (select partname, price, work, count(*) as iCount from gongshi group by partname, price, work) b on a.partname = b.partnamewhere b.icount > 1)order by partname;--写这个语句才发现，partname与work并不是一一对应的啊

程序还应修正。[]执行出错！服务器: 消息 209，级别 16，状态 1，行 1列名 '图号' 不明确。请修改，立即散分。Davis Wang

Very Good!Thanks.