请问下大家,比如说刷选出重复记录了
语句如下 select a.* from Sheet1$ a inner join
(select huzhbiha,huwubiha from Sheet1$ group by huzhbiha,huwubiha having count(*)>1) b
on a.huzhbiha=b.huzhbiha and a.huwubiha=b.huwubiha
where a.huzhbiha='SUNX01' and a.huwubiha='1314-0002-02'
然后我想在这个结果集的基础上在去除重复记录,重复的记录是不能删除的
也就是相当于刷选出的数据,我只要1条,还是以huzhbiha 和huwubiha 这2个字段刷选
条件,请问高手如何写啊~!
语句如下 select a.* from Sheet1$ a inner join
(select huzhbiha,huwubiha from Sheet1$ group by huzhbiha,huwubiha having count(*)>1) b
on a.huzhbiha=b.huzhbiha and a.huwubiha=b.huwubiha
where a.huzhbiha='SUNX01' and a.huwubiha='1314-0002-02'
然后我想在这个结果集的基础上在去除重复记录,重复的记录是不能删除的
也就是相当于刷选出的数据,我只要1条,还是以huzhbiha 和huwubiha 这2个字段刷选
条件,请问高手如何写啊~!
解决方案 »
- 一张表中某几个字段重复,则保留其中一条,删除剩余的
- 求存储过程的写法(对数据库了解多的应该觉得比较简单)
- 求一个SQL语句
- 有关SQLSEVER安装文件
- Microsoft OLE DB Provider for SQL Server 错误 '80040e14' 第 1 行: '=' 附近有语法错误。
- 简单的属性设置(关于图形的)?
- 作业权限????
- 急,如何取得某个数据库中所有表名(MSSQL Server)
- [讨论] 如何设置sql server2000里两张表的有关联的触发器呢,多谢了!(这种情况非常有用)
- 大哥,看一下我的题目吧!表a中有400条记录,然后用一个查询得到该表列信息。
- 数据库字段设置
- 请教sql语句
with ct1 as
(
select a.* from Sheet1$ a inner join
(select huzhbiha,huwubiha from Sheet1$ group by huzhbiha,huwubiha having count(*)>1) b
on a.huzhbiha=b.huzhbiha and a.huwubiha=b.huwubiha
where a.huzhbiha='SUNX01' and a.huwubiha='1314-0002-02'
)select *
from ct1 t
where not exists (select 1 from ct1 where huzhbiha = t.huzhbiha and huwubiha = t.huwubiha and id > t.id) --或者日期什么的
select * from
(
select row_number() over (partition by huzhbiha,huwubiha order by huzhbiha) as rowindex,*
(
select a.* from Sheet1$ a inner join
(select huzhbiha,huwubiha from Sheet1$ group by huzhbiha,huwubiha having count(*)>1) b
on a.huzhbiha=b.huzhbiha and a.huwubiha=b.huwubiha
where a.huzhbiha='SUNX01' and a.huwubiha='1314-0002-02'
)t1
)t2
where rowindex = 1
(
select a.* from Sheet1$ a inner join
(select huzhbiha,huwubiha from Sheet1$ group by huzhbiha,huwubiha having count(*)>1) b
on a.huzhbiha=b.huzhbiha and a.huwubiha=b.huwubiha
where a.huzhbiha='SUNX01' and a.huwubiha='1314-0002-02'
)select
*
from
f t
where
id=(select max(id) from f where huzhbiha = t.huzhbiha and huwubiha = t.huwubiha ) --或者日期什么的