sql经典中经典

[qj_198127]

表结构create table career (
id int identity not null primary key,
licenseno varchar(20) null
)
里面的数据，（1,'2345'
2,'2345'
3,'4566'
4,'45678'
5,'4566'
)
sql语句要求把licenseno重复的任意提出一个的ID找出来
要求出的结果
1，3或者2，5都行

[永生天地]

select min(id),licenseno from career group by licenseno

select max(id),licenseno from career group by licenseno

[chuifengde]

select  * from career a where not exists(select 1 from career where licenseno=a.licenseno and id>a.id)

[GOODlivelife]

经典地接分

[ShenLiang2025]

--1)表和数据
create table career (
id int identity not null primary key,
licenseno varchar(20) null
)
里面的数据，（1,'2345'
2,'2345'
3,'4566'
4,'45678'
5,'4566'
)

INSERT INTO career
SELECT '2345'
UNION ALL
SELECT '2345'
UNION ALL
SELECT'4566'
UNION ALL
SELECT '45678'
UNION ALL
SELECT '4566'

--SELECT * FROM career

--2)查询

SELECT Min_Way = MIN(id) FROM career
GROUP BY licenseno
HAVING COUNT(1) > 1

Min_Way
-----------
1
3

(2 row(s) affected)

--3)查询
SELECT MAX_Way = MAX(id) FROM career
GROUP BY licenseno
HAVING COUNT(1) > 1

MAX_Way
-----------
2
5

(2 row(s) affected)

[心中的彩虹]

[Quote=引用楼主 qj_198127 的回复:]
表结构create table career (
id int identity not null primary key,
licenseno varchar(20) null
)
里面的数据，（1,'2345'
2,'2345'
3,'4566'
4,'45678'
5,'4566'
)
sql语句要求把licenseno重复的任意提出一个的ID找出来
要求出的结果
1……
[/Quote]

-- 下面的max 可以改为min 随便 
select id,licenseno
from career a
where id in (select max(id) from career where a.licenseno=licenseno group by licenseno having count(*)>=2)

--去掉所有重复的 sql 2005
select id,licenseno
from (select id,licenseno,row_number() over(partition by licenseno order by id) rn
from career) a
where rn=1

[ChinaJiaBing]

--try

if object_id ('career')is not null
   drop table career
go
create table career (id int identity not null primary key,
                     licenseno varchar(20) null)
insert into career(licenseno)
    select '2345'
    union all
    select '2345'
    union all
    select '4566'
    union all
    select '45678'
    union all 
    select '4566'
select min(id) from career group by licenseno having(count(*)>1)


/*


-----------
1
3

(2 行受影响)



*/

[昵称被占用了]

...

[yibey]

2楼写得好

[yibey]

在外加以句

select min(id),licenseno from career group by licenseno   having cout(licenseno)>1

[yibey]

写错词了是 count

[wqmgxj]

select min(id) from career group by licenseno having(count(*)>1)