有一个表A有两个字段Sno,Cno.表的结构以下: Sno Cno
95001 1000
95001 1000
95001 1002
95002 1003现在要得到以下的数据: Sno Cno
95001 1002
95002 1003怎样用SQL语句完成这个任务?
95001 1000
95001 1000
95001 1002
95002 1003现在要得到以下的数据: Sno Cno
95001 1002
95002 1003怎样用SQL语句完成这个任务?
a , (select Sno,max(Cno) as cno from a group by sno) as b
where a.sno = b.sno and a.cno = b.cno
declare @t table(Sno varchar(10),Cno int)
insert @t
select '95001', 1000 union all
select '95001', 1000 union all
select '95001', 1002 union all
select '95002', 1003
----方法1
select * from @t as a
where not exists(select 1 from @t where Sno = a.Sno and Cno > a.Cno)
----方法2
select * from @t as a
where Cno = (select TOP 1 Cno from @t where Sno = a.Sno order by Cno DESC )
----方法3
select Sno,Max(Cno) as Cno from @t group by Sno/*结果
Sno Cno
------------------------------
95001 1002
95002 1003
*/
from a
group by sno
group by sno
大家有没有注意,我给出的表A的数据只是个例子,具体是不知道表A 的数据的,只是想删除表是中重复的行(即重复的行不要).这样的话是不是不能用max()聚函数了?谢谢你们发贴子的,希望有人能帮我解决这个问题.
1.select * into T from table a where not exists(select 1 from table where Sno = a.Sno and Cno > a.Cno)
2.sp_rename 'table','tableOld'
3.sp_rename 'T','table'
select sno,cno from @t where sno=a.sno and cno=a.cno
group by sno,cno having count(*)>1)
a , (select Sno,max(Cno) as cno from a group by sno) as b
where a.sno = b.sno and a.cno = b.cno)c
1 2
1 3
1 2
2 3
2 4
楼主是说这样的?
id重复的只保留一条记录? 既然num有重复值,那你从逻辑上无法说明需要保留哪条记录.
表中若无主健,又不标识,即使你手工删除SQL也会提示"健信息不足",因为对于我示例的第一和第三条记录,SQL自已都无法区分你要删哪条, 又何以能拿语句来区分?
95001 1000
95001 1004
95002 1001
95002 1001
95003 1004
现在要把重复的行(表中具体的数据是不知道的,这只是为了便以说明)不要,得到以下的表:
sno cno
95001 1000
95001 1004
95003 1004 是我没把题目说明白,现在补充说明一次,大家想想看,有什么方法解决啊!
然后清空原表,再将刚才的查询结果写回.
from tablename
group by ano,cno
having count(ano,cno) = 1
declare @t table(Sno varchar(10),Cno int)
insert @t
select '95001', 1000 union all
select '95001', 1004 union all
select '95002', 1001 union all
select '95002', 1001 union all
select '95003', 1004----查询
select * from @t as a
where not exists
(
select 1 from @t
where Sno = a.Sno and Cno = a.Cno
group by Sno,Cno having count(*) > 1
)/*结果
Sno Cno
-------------------------------
95001 1000
95001 1004
95003 1004
*/
解决方法上面我已说过了.
where not exists
(
select 1 from @t
where Sno = a.Sno and Cno = a.Cno
group by Sno,Cno having count(*) > 1
)
这个很有里
这样就不会有重复的数据出现啊,至少可以保证两条数据是不能相同的啊,这样做了后再select distinct(主键) from 表
楼上火星人?
说了这么明显了 重复的记录不显示
-------------------
下面这是火星文字?
--------------
有一个表A有两个字段Sno,Cno.表的结构以下: Sno Cno
95001 1000
95001 1000
95001 1002
95002 1003现在要得到以下的数据: Sno Cno
95001 1002
95002 1003
-----------------------------如果是要求 95001 1000
95001 1002
95002 1003
这样的结果,直接对字段列表进行group by或者 distinct字段列表就可以了,语句写那么复杂搞什么?
这跟设置主键有问题吗?没搞过数据就别乱讲楼主问题 hellowork(一两清风) 的是正解
select * from @t as a
where not exists
(
select 1 from @t
where Sno = a.Sno and Cno = a.Cno
group by Sno,Cno having count(*) > 1
)
现在问一下hellowork(一两清风)大师和大家,如果不用创建测试数据,而表中是有了数据的,不用@t变量,而用其它方法完成,例如可否自连结或其它的,大家再想想啊!!特别希望hellowork(一两清风)大师能给个答复,然后我再给分啊!!
谢谢各位思考啊!!
where not exists
(
select 1 from @t
where Sno = a.Sno and Cno = a.Cno
group by Sno,Cno having count(*) > 1
)
大师从何谈起?只是使用SQL比楼主多熟练了些罢了!
楼主可以直接将@t换成实际的表名称,试一下就知道了.
这种查询方法叫相关子查询.也就是说每扫描一行时,都判断一下表中是否还有完全相同的行(即对当前行的所有列进行分组计数count(*)的值>=2或>1),如果有则说明当前行有重复行,故不符合要求;如果没有则说明当前行在表中是唯一的,没有重复行,故符合要求而被select.
如果表中还有其它列,比如Dno,
select * from @t as a
where not exists
(
select 1 from @t
where Sno = a.Sno and Cno = a.Cno and Dno = a.Dno and Eno = a.Eno and....
group by Sno,Cno,Dno,Eno,... having count(*) > 1
)
即将所有列都分组计数.重新说明一下下面这行,说得不是很明确:
(即对当前行的所有列进行分组计数count(*)的值>=2或>1)
-----------------------------------------------------------------------------
应为:(即:对表中与当前行的所有列值相同的行进行分组计数,看看与当前行的所有列的值都相同的行有多少.如果等于1则说明是与当前行完全重复的行是当前行本身,当前行是唯一的;如果大于1则说明有其他行与当前行所有列的值完全相同,那么就说明当前行有重复行,不符合要求)
select * from a
where not exists
(
select * from a
where Sno = a.Sno and Cno = a.Cno
group by Sno,Cno having count(*) >1
)
显示的结果是空值,为什么呢?是不是在exits内部不能实现自身连结?
而用下面的代码,就行了select * from a as b
where not exists
(
select * from a
where b.Sno = a.Sno and b.Cno = a.Cno
group by Sno,Cno having count(*) >1
)
请大家回复
建议楼主使用使用规范点的表名称,然后把该表赋予一个表别名,例如:
select * from tbName as a /*指定表别名*/
where not exists
(
select * from tbName
where Sno = a.Sno and Cno = a.Cno /*通过表别名a进行内外关联*/
group by Sno,Cno having count(*) >1
)
select *
from tablename
group by ano,cno
having count(*) = 1
select *
from tablename
group by ano,cno
having count(*) = 1
from tablename
group by ano,cno
having count(*) = 1
楼上说的不用exists相关子查询也可以.
drop table test
create table test(Sno varchar(10),Cno int)
insert test
select '95001', 1000 union all
select '95001', 1004 union all
select '95002', 1001 union all
select '95002', 1001 union all
select '95003', 1004select * from test create table #t(Sno varchar(10),Cno int)insert into #t
select * from test a
where exists(select 1 from test b where a.sno = b.sno group by b.sno,cno having count(*) = 1)select * from #tdelete from test
insert into test select * from #t
select * from test
drop table #t