select * from tablename group by 所有的字段列表 having count(*)>1
解决方案 »
- report service没有注册上
- 简单问题
- 数据库问题
- SQL中的表触发器中如何获得执行的SQL语句,如果获得自动列的值。
- 解决立即结帖!!!!!在线等待!很简单的问题:在B表上怎么插入A表所有满足WHERE条件的记录?
- 一个关于SQL的字符串的问题
- 是否有什么设置限制了select top 10 from table这种语句?
- 有一个商品资料表,数据库中的很多表都引用约束这个商品资料的条码资料,然后当数据库很大时,我想删除某一条码资料时系统会很慢很慢
- 关于vfp的项目编译出现的问题?补充。
- 在线问一个问题,急!!!!!!!!!!!11
- 一百分求Log Explorer 的下载和使用说明
- 这是一个很经典的问题,相信很多人都碰到过,非常简单
比方说
f1 f2 f3 f4 f5
1 1 1 1 1
2 1 1 1 1
3 2 1 23 1
4 2 3 1 3
………………
我现在就想把第一条和第二条列出来,如果下面还有的话也列出来
没想到更好的办法
from 表 as a, 表 as b
where a.f1 <> b.f1 and a.所有的其他字段 = b.所有的其他字段
select * from 表 group by .. having count(*)>1不知道还有没有什么更好的方法
FROM 表 a INNER JOIN
表 b ON a.f1 <> b.f1 AND a.所有的其他字段 = b.所有的其他字段
(select f2,f3,f4,f5 from tablename group by f2,f3,f4,f5 having count(*)>1) b
where a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5
SELECT DISTINCT a.*
FROM 表 a INNER JOIN
表 b ON a.f1 <> b.f1 AND a.所有的其他字段 = b.所有的其他字段
SELECT DISTINCT a.*
FROM 表 a INNER JOIN
表 b ON a.f1 <> b.f1 AND a.所有的其他字段 = b.所有的其他字段
select a=min(f1),b=max(f1)
from 表 group by f2,f3,f4,f5
having count(*)>1
) b on a.f1 between b.a and b.b
create proc p_qry
@tbname sysname, --要查询的表名
@keyfdname sysname=null --表中的主键,如果未指定,则表中无主键
as
declare @nokey bit,@fd varchar(8000)
set nocount on
if isnull(@keyfdname,'')=''
begin
select @keyfdname=cast(newid() as char(36)),@nokey=1
exec('alter table ['+@tbname+'] add ['+@keyfdname+'] decimal(38,0) identity(1,1)')
end
set @fd=''
select @fd=@fd+',['+name+']' from syscolumns
where object_name(id)=@tbname and name<>@keyfdname
set @fd=substring(@fd,2,8000)
exec('select * from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')
if @nokey=1 exec('alter table ['+@tbname+'] drop column ['+@keyfdname+']')
set nocount off
go
select a.* from 表 a join(
select a=min(f1),b=max(f1)
from 表 group by f2,f3,f4,f5
having count(*)>1
) b on a.f1 between b.a and b.b--查询重复记录的通用存储过程
create proc p_qry
@tbname sysname, --要查询的表名
@keyfdname sysname=null --表中的主键,如果未指定,则表中无主键
as
declare @nokey bit,@fd varchar(8000)
set nocount on
if isnull(@keyfdname,'')=''
begin
select @keyfdname=cast(newid() as char(36)),@nokey=1
exec('alter table ['+@tbname+'] add ['+@keyfdname+'] decimal(38,0) identity(1,1)')
end
set @fd=''
select @fd=@fd+',['+name+']' from syscolumns
where object_name(id)=@tbname and name<>@keyfdname
set @fd=substring(@fd,2,8000)
if @nokey=1
begin
exec('select '+@fd+' from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')
exec('alter table ['+@tbname+'] drop column ['+@keyfdname+']')
end
else
exec('select a.* from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')set nocount off
go
drop procedure [dbo].[p_qry]
GO--查询重复记录的通用存储过程
create proc p_qry
@tbname sysname, --要查询的表名
@keyfdname sysname=null --表中的主键,如果未指定,则表中无主键
as
declare @nokey bit,@fd varchar(8000)
set nocount on
if isnull(@keyfdname,'')=''
begin
select @keyfdname=cast(newid() as char(36)),@nokey=1
exec('alter table ['+@tbname+'] add ['+@keyfdname+'] decimal(38,0) identity(1,1)')
end
set @fd=''
select @fd=@fd+',['+name+']' from syscolumns
where object_name(id)=@tbname and name<>@keyfdname
set @fd=substring(@fd,2,8000)
if @nokey=1
begin
exec('select '+@fd+' from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')
exec('alter table ['+@tbname+'] drop column ['+@keyfdname+']')
end
else
exec('select a.* from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')set nocount off
go--创建测试数据
create table 表(f1 int,f2 int,f3 int,f4 int,f5 int)
insert into 表
select 1,1,1,1,1
union all select 2,1,1,1,1
union all select 3,2,1,23,1
union all select 4,2,3,1,3
go--直接查询
select a.* from 表 a join(
select a=min(f1),b=max(f1)
from 表 group by f2,f3,f4,f5
having count(*)>1
) b on a.f1 between b.a and b.b--调用通用存储过程实现楼主的查询
exec p_qry '表','f1'--删除测试环境
drop table 表
/*--测试结果f1 f2 f3 f4 f5
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
2 1 1 1 1(所影响的行数为 2 行)f1 f2 f3 f4 f5
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
2 1 1 1 1--*/