sql经典中经典

2010-06-09 01:27:37 189 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

qj_198127 墨衍会员 · AI 创作全网分发关注 + 加入社区

SQL经典实例 1. 检索记录查找满足多个行的记录可以用 and 和 or 来筛选数据， and 的优先级大于 or ，最好使用()来明确使用优先级。在where中筛选别名一般SQL语句执行顺序：from > where > select #所以该语句报错 select sal as salary, comm as commission from emp where s... 阅读详情

表结构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都行

...全文

经典Hive SQL面试题第一题需求我们有如下的用户访问数据 userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 u03 2017/1/22 8 u04 2017/1/20 3 u01 2017/1/23 6 u01 2017/2/21 8 U02 2017/1/23 6 U01 2017/2/22 ... 阅读详情

qj_198127

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wqmgxj 2010-06-10

打赏
举报

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

yibey 2010-06-09

打赏
举报

写错词了是 count

yibey 2010-06-09

打赏
举报

在外加以句

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

yibey 2010-06-09

打赏
举报

2楼写得好

昵称被占用了 2010-06-09

打赏
举报

...

ChinaJiaBing 2010-06-09

打赏
举报



--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 行受影响)







*/

心中的彩虹 2010-06-09

打赏
举报

[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

ShenLiang2025 2010-06-09

打赏
举报









--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)

GOODlivelife 2010-06-09

打赏
举报

经典地接分

chuifengde 2010-06-09

打赏
举报

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

永生天地 2010-06-09

打赏
举报

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

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

sql经典中经典

相关推荐