##mysql數據庫事務###

數據庫事務（簡稱：事務）是由一系列對系統中數據進行訪問與更新的操作所組成的一個程序執行邏輯單元。

1. 主鍵 超鍵 候選鍵 外鍵

主 鍵：數據庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

超 鍵：在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵

候選鍵：是最小超鍵，即沒有冗餘元素的超鍵。

外 鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

2.數據庫事務的四個特性及含義

數據庫事務transanction正確執行的四個基本要素。

ACID,原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

原子性:整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

一致性:在事務開始之前和事務結束以後，數據庫的完整性約束沒有被破壞。

隔離性:隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作。如果有兩個事務，運行在相同的時間內，執行 相同的功能，事務的隔離性將確保每一事務在系統中認為只有該事務在使用系統。這種屬性有時稱為串行化，為了防止事務操作間的混淆，必須串行化或序列化請 求，使得在同一時間僅有一個請求用於同一數據。

持久性:在事務完成以後，該事務所對數據庫所作的更改便持久的保存在數據庫之中，並不會被回滾。

視圖的作用，視圖可以更改麼？

1.視圖的創建

第一類：create view v as select * from table;

第二類、基於不同數據庫

這種情況只比上面的sql語句多一個數據庫的名字，如下：

create view 數據庫1.v as (select * from 數據庫1.table1) union all (select * from 數據庫2.table2);

或

create view 數據庫2.v as (select * from 數據庫1.table1) union all (select * from 數據庫2.table2);

如果執行第一個sql將在數據庫1下建立視圖，反之亦然；

視圖的作用？

視圖是虛擬的表，與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢；不包含任何列或數據。使用視圖可以簡化複雜的sql操作，隱藏具體的細節，保護數據；視圖創建後，可以使用與表相同的方式利用它們。視圖不能被索引，也不能有關聯的觸發器或默認值，如果視圖本身內有order by 則對視圖再次order by將被覆蓋。

視圖可以更改麼？

對於某些視圖比如未使用聯結子查詢分組聚集函數Distinct 、Union等，是可以對其更新的，

4、drop,delete與truncate的區別

一、不同點

1.truncate table 和 delete只刪除記錄不刪除表的結構，drop語句將刪除表的結構依賴的約束(constrain)，觸發器(trigger),索引(index);drop語句將刪除表的結構被依賴的約束(constrain)，觸發器(trigger)，索引(index);依賴於該表的存儲過程/函數將保留，但是變為invalid狀態

2.truncate之後的自增字段從頭開始計數了，而delete的仍保留原來的最在數值。

二、總結：

1.在速度上，一般來說，drop> truncate > delete。

2.在使用drop和truncate時一定要注意，雖然可以恢復，但為了減少麻煩，還是要慎重。

3.如果想刪除部分數據用delete，注意帶上where子句，回滾段要足夠大；

如果想刪除表，當然用drop；

如果想保留表而將所有數據刪除，如果和事務無關，用truncate即可；

如果和事務有關，或者想觸發trigger，還是用delete

如果是整理表內部的碎片，可以用truncate跟上reuse stroage，再重新導入/插入數據

5.索引的工作原理及其種類

數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據。索引的實現通常使用B樹及其變種B+樹

MySQL索引的建立對於MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。索引也是一張表，該表保存了主鍵與索引字段，並指向實體表的記錄。上面都在說使用索引的好處，但過多的使用索引將會造成濫用。

因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會佔用磁盤空間的索引文件。

ALTER table tableName ADD INDEX indexName(columnName)

##表的創建、更新、修改

28、 創建表

Create table actor

Actor_id smallint(5) notnull Primary key,

First_name varchar(45) not null,

Last_name varchar(45) not null,

Last_update timestamp not null

Default (datetime('now','locatetime'))

33、

insert INTO actor #tablename

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33),

(2,NICK,WAHLBERG,2006-02-15 12:34:33)

34、

Insert ignore into

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33)

35、

Create table actor_name

As

Select first_name,last_name from actor

37、 創建索引

Create unique index unique_idx_firstname on actor(first_name)

Create index index_lastname on actor(last_name)

38、 臨時試圖

Create view actor_name_view

As

Select first_name as first_name_v,last_name as last_name_v

From actor

39、 增加一列

Alter table actor add column

Create_date datetime not null

40、 觸發器

Create triggle audit_log

After insert into employee_test

Begin insert into audit

Values (NEW.id,NEW.NAME)

END

41、 刪除重複記錄，保留小的id

Select emp_no,title,from_date,to_date,min(id) as id

From title_test t

Group by emp_no,title,from_date,to_date

Delete title_test t

Where id not in (select min(id) from title_test group by emp_no)

42 更新

Update title_set set from_date='2001-01-01'and to_date = null

Where to_date = '9999-01-01'

43、

Repalce into titles_test

as

Select 5,10005,title ,from_date,to_date

From title_test where id = 5

44、 更改表明

Alter table titles_test rename to titles_2017

45、 創建外檢約束

Alter table audit add foreign key

Reference emplyees_test(id)

46、 選出和視圖一樣的數據

Select em.* from emplyees as em,emp_v as ev

Where em.emp_no = ev.emp_no

Select * from emp_v

47、 獲獎員工當前工資增加

Update

Salaries s

Set salary = salary *1.1

Where s.emp_no in (select emp_no from emp_bonus) and s.to_date='9999-01-01'

48

只修改列的數據類型的方法:

通常可以寫成 alter table 表名 modify column 列名 新的列的類型

例如:student表中列sname的類型是char(20),現在要修改為varchar(20),SQL語句如下

alter table student modify column sname varchar(20);

同時修改列名和列的數據類型的方法:

通常可以寫成 alter table 表名 change column 舊列名 新列名 新的列類型

例如:student表中列sname的類型是char(20),現在要修改為stuname varchar(20),SQL語句如下

alter table student change column sname stuname varchar(20);

###數據庫查詢

1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

Select t.* , 01_score , 02_score

From student t

Inner join

(Select s1.s_id,s1.score as 01_score ,s2.score as 02_score

From score s1 ,score s2

Where s1.c_id =’01’ and s2.c_id =’02’ and s1.score > s2.score and s1.s_id =s2.s_id ) t1

On t.s_id =t1.s_id

##沒選02課程的人

Select

S.*,s1.score as 01_score , s2.score as 02_score

From

(Student s

Right join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

Left join score s2

On s1.s_id = s2.s_id and s2.c_id=’02’ and s1.score > s2.score)

Union

###沒有選擇02課程成績的同學#####

Select

S.*,s1.score as 01_score , 0 as 02_score

From

Student s

join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’

join score s2

On s1.s_id = s2.s_id and s2_id not in (select s_id from score where c_id = ‘02’))

如果要的滿足"01"課程比"02"課程成績高條件的學生 所有功課

Select st.* ,’語文’,’數學’,’英語

’from student st

Join

(

Select sc.s_id ,

Sum(case sc.c_id = ‘01’ then sc.score else 0 end) as ‘語文’,

Sum(case sc.c_id = ‘02’ then temp.02_scoreelse 0 end )as ‘數學’, ##選擇temp 02成績作為數學成績

Sum(case sc.c_id = ‘03’ then sc.score else 0 end) as ‘英語’,

From score sc

Join

###滿足條件的學生id#####################

(Select

S.*,s1.score as 01_score , s2.score as 02_score

From

(Student s

Right join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

Left join score s2

On s1.s_id = s2.s_id and s2.c_id=’02’ and s1.score > s2.score)

Union

###沒有選擇02課程成績的同學#####

Select

S.*,s1.score as 01_score , 0 as 02_score

From

(Student s

join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

join score s2

On s1.s_id = s2.s_id and s2_id not in (select s_id from score where c_id = ‘02’))

) temp

On sc.s_id = temp.s_id

Group by sc.s_id)

-- 2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數

select a.* ,b.s_score as 01_score,c.s_score as 02_score from

(student a left join score b on a.s_id=b.s_id and b.c_id='01' )

join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score

-- 3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

Select s_id ,avg(score) as svg_score

From score

Group by s_id

Having avg(score) >=60

Select s1.name,s2.s_id,round(avg(score),2) as svg_score

from student s1

Left join score s2

On s1.s_id = s2.s_id

Group by s1.s_id

Having avg(score) >=60

-- 4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

-- (包括有成績的和無成績的)

Select s.name ,s.s_id, round(avg(score),2) as avg_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id having avg(score) < 60

Union

Select s.name ,s.s_id, 0 as avg_score

From student s

Where s_id not in (select dinstinct s_id from score)

#mysql 版本優化了，可以再having裡面使用別名

Select s.name ,s.s_id, round(avg(score),2) as avg_score

From student s

Left join score s1 on s.s_id = s1.s_id

Group by s.s_id

having avg_score < 60 or avg_score is null

--19、按各科成績進行排序，並顯示排名

1. 分數相同rank相同，rank考慮是有多少人比你分數高

#先進行子查詢，每一條外查詢都跑一輪子查詢

Select s1.c_id ,

(Select count(1)+1 from score s2 where s1.c_id =s2.c_id and s1.score < s2.score ) as rank

From score s1

Order by s1.c_id ,rank

2.分數相同rank相同，rank考慮是有多少分數比你分數高

Select s1.c_id ,

(Select count(dinstinct s2.score)+1 from score s2 where s1.c_id =s2.c_id and s1.score < s2.score ) as rank

From score s1

Order by s1.c_id ,rank

--46、查詢各學生的年齡

-- 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

Select s.name,s_id,

Date_format(now(),’%Y’)- Date_format(s.birth,’%Y’) -

(Case when Date_format(now(),’%m%d’) < Date_format(s.birth,’%m%d’) then 1 else 0 end) as age

From student s

Leetcode

##########行程################

select t.Request_at as Day,

round((sum(case when t.Status != 'completed' then 1 else 0 end))/count(*),2)

as 'Cancellation Rate' #名字有空格使用引號

from

Trips t

inner join Users u1

on t.Client_Id= u1.Users_Id and u1.Banned ='No'

inner join Users u2

on t.Driver_Id= u2.Users_Id and u2.Banned ='No'

where

convert(t.Request_at,date) between '2013-10-01' and '2013-10-03'

group by t.Request_at

order by Day

Month(t.Request_at) = 10 and Year(t.Request_at) = 2013 and day(t.Request_at) <=3 and day(t.Request_at) >= 1

DATE_FORMAT(t.Request_at,'%Y-%m-%d') between '2013-10-01' and '2013-10-03'

from_unixtime(time,'%Y-%m-%d %H:%i:%s') #unixtime 改成固定格式

Select UNIX_TIMESTAMP(’2006-11-04 12:23:00′);

Datatime = date+time

IFNULL(expr1,expr2)：如果第一個參數不為空，則返回第一個參數，否則返回第二個參數。

ISNULL(expr)：判斷是否是空，是空則返回1，否則返回0。

IF(expr1,expr2,expr3)：如果第一個表達式的值為TRUE（不為0或null），則返回第二個參數的值，否則返回第三個參數的值。

-- 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

Select s1.s_id ,s1.name,count(1) as num, sum(score) as sum_score

From student s1 left join score s2

On s1.s_id =s2.s_id

Group by s1.s_id

-- 6、查詢學過"張三"老師授課的同學的信息

Select from s.*

Student s join score s1

On s.s_id = s1.s_id

Where s1.c_id in

(

Select Course.C_id

from

Course join Teacher

On Course.t_id = Teacher.t_id

Where Teacher.t_name = ‘張三’)

-- 7、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息

Select S.*

From student s, score s1,score s2

Where s.s_id =s1.s_id and s1.s_id = s2.s_id

And s1.c_id = ‘01’and s2.s_id = ‘02’

Select S.*

From student s

Join score s1 on s.s_id =s1.s_id And s1.c_id = ‘01’

Join score s2 on s1.s_id = s2.s_id and s2.s_id = ‘02’

-- 8、查詢學過編號為"01"並且"02"的同學所選的課程的同學的信息

Select s.*

From student s join

(Select * from (

Select distinct s_id

From score t Where t.c_id in

(Select c_id From score s

Where s.s_id = ‘01’) and t.s_id != ‘01’) t1

Where t1.c_id in

(Select c_id

From score s

Where s.s_id = ‘02’) and t1.s_id != ‘02’))t2

On s.s_id = t2.s_id

Select s.*

From student s join

(select distinct s_id From score s where s_id != ‘01’and s_id != ‘02’and (c_id in (Select c_id From score s

Where s.s_id = ‘01’) or c_id in (Select c_id From score s

Where s.s_id = ‘01’ ))

) t

On s.s_id =t.s_id

-- 10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

Select s.*

From student s ,score s1 Where s.s_id = s1.s_id

And s1.c_id = ‘01’ and s1.s_id not in (

Select s_id from score s2 where c_id = ‘02’)

-- 11、查詢沒有學全所有課程的同學的信息

Select s.*

from student s ,(select s_id from score s1

Group by s1.s_id

Having count(s1.c_id) < (select count(distinct s2.c_id ) from score s2)

) t

Where s.s_id = t.s_id

-- 12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

select distinct s.* # 這樣對，但是慢

from student s ,

score s1

Where s.s_id = s1.s_id and

S1.s_id != ‘01’ and s1.c_id in

(Select c_id from score where s_id = ‘01’)[寫錯了，一個同學出現了很多條記錄]

Select * from student where s_id in

(Select distinct s_id from score where c_id in (Select c_id from score where s_id = ‘01’) and s_id != ‘01’)

-- 13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息

Select s.* from student s

Join

(Select s_id from score s1 on where s1.c_id in (Select c_id from score where s_id = ‘01’)

and s_id != ‘01’

group by s.s_id

having

count(c_id) = (select count(s2.c_id) from score s2 where s2.s_id = ‘01’)

)

Tmp

On s.s_id = tmp.s_id

Leetcode 刪除重複郵箱，保留id小的

沒有id直接distinct 就好

Select e1.id ,e1.email

From emails e1,emails e2

Where e1.email = e2.email and e1.id < e2.id

Delete e1.id ,e1.email

from

emails e1,emails e2

Where e1.email = e2.email and e1.id > e2.id

-- 20、查詢學生的總成績並進行排名

Select

@k := (case when @score = a.sum_score then @k else @k+1 end )as rank,

@score : = a.sum_score as score

From

(Select sum(score) as sum_score from score group by s_id order by sum_score Desc) a , (select @k:=0,@score:=0)s

Leetcode

select a.score as Score, (select count(distinct b.score)+1 from Scores b

where a.score < b.score ) as Rank

from Scores a

order by Rank desc

Group_concat函數

mysql中的函數，字符串拼接的話，可以用concat()，但是此函數是針對一條記錄中，可以將不同的字段拼接，並不適用多條記錄的某一字段。查了一下，mysql中group_concat函數就可以獲得到這樣的結果。

1. group_concat只有與group by語句同時使用才能產生效果。

2. 需要將拼接的結果去重的話，可與DISTINCT結合使用即可。

SELECT

DISTINCT o.id_,o.order_sn,o.create_time,o.wait_out_storage_total,o.back,group_concat(og.goods_name) AS goods_names ,o.store_title FROM wms_orders o LEFT JOIN wms_orders_goods og ON o.id_=og.order_id WHERE o.wait_out_storage_total>0

GROUP BY o.id_;

15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

Select

s1.s_id,s1.name,round(avg(s2.score),2) as avg_score

Student s1 join score s2

On s1.s_id = s2.s_id

where s2.score < 60

Gruop by s1.s_id,s1.name

Having count(1) >=2

15.查詢比30部門最高薪資的人薪資更高的所有員工信息

SELECT *

FROM emp

WHERE sal >

(SELECT MAX(sal) FROM emp WHERE deptno = '30')

查詢比30部門所有人薪資都高員工信息

SELECT *

FROM emp

WHERE sal >

ALL (SELECT sal FROM emp WHERE deptno = '30')

兩句話一樣的查詢，函數不一樣，函數放的位置不一樣

- 16、檢索"01"課程分數小於60，按01分數降序排列的學生信息

SELECT s.* ,s1.score

from student s ,score s1

Where s.s_id = s1.s_id and s1.c_id = ‘01’and s1.score < 60

Order by s1.score

- 17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

(Select s_id ,sum(score) as sum_score ,round(avg(acore),2) as avg_score

From score s

Group by s_id) #有成績的有選課的同學

Union

(Select s_id ,0 as sum_score ,0 as svg_score

From student s1 where s1.s_id not in (select distinct s_id from score)

每門課的成績和平均成績

Select

s.s_id,s.name,

sum(case when s1.c_id = ‘01’then s1.score else 0 end ) as ‘語文’，

sum(case when s1.c_id = ‘02’then s1.score else 0 end ) as ‘數學’，

sum(case when s1.c_id = ‘03’then s1.score else 0 end ) as ‘英語’，

round(avg(acore),2) as avg_score

From student s left join score s1

Group by s1.s_id

Order by avg_score desc

-- 18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

Select

S.C_id,c.name

Max(score) as max_score,

Min(score) as min_score,

Round(avg(score),2)as avg_score,

Round(Sum(case when score >= 60 then 1 else 0 end )/count(1),2) as jige_rate,

Round(Sum(case when score >=70 and score < 80then 1 else 0 end )/count(1),2) as mid_rate,

Round(Sum(case when score >= 80 and score < 90 then 1 else 0 end )/count(1),2) as lianghao_rate,

Round(Sum(case when score >= 90 then 1 else 0 end )/count(1),2) as youxiu_rate,

From score s left join course c

Group by s.c_id,c.name

-- 19、按各科成績進行排序，並顯示排名(實現不完全)

Select

@i := @i+1 as index,

@k := (case when @score = a.score then @k else @i end ) as rank

@score : = a.score as 01_score

From (select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC)

a,(select @k:=0,@i:=0,@score:=0)s

Union

................

網易mysql

1、好評率是會員對平臺評價的重要指標。現在需要統計2018年1月1日到2018年1月31日，用戶'小明'提交的母嬰類目"花王"品牌的好評率（好評率=“好評”評價量/總評價量）:

用戶評價詳情表：a

字段：id（評價id，主鍵），create_time（評價創建時間，格式'2017-01-01'）， user_name(用戶名稱)，goods_id(商品id，外鍵) ，

sub_time（評價提交時間，格式'2017-01-01 23:10:32'），sat_name（好評率類型，包含：“好評”、“中評”、“差評”）

商品詳情表：

b 字段：good_id（商品id，主鍵），bu_name（商品類目）, brand_name(品牌名稱)

Select

A. user_name ,b.bu_name ,brand_name,

Round(sum(Case when a.sat_name = ‘好評’then 1 esle 0 end) / count(sat_name) ,2) as ‘好評率’

From a left join b on a.goods_id = b.good_id

Where b.bu_name = ‘母嬰’and brand_name = ‘花王’

And a.user_name = ‘小明’and a.sub_time between '2018-01-01' and

'2018-01-31'

2、考拉運營"小明"負責多個品牌的銷售業績，請完成：

（1）請統計小明負責的各個品牌，在2017年銷售最高的3天，及對應的銷售額。

銷售表 a:

字段：logday（日期，主鍵組），SKU_ID（商品SKU，主鍵組），sale_amt(銷售額)

商品基礎信息表 b:

字段：SKU_ID（商品SKU，主鍵）,bu_name（商品類目），brand_name(品牌名稱)，user_name（運營負責人名稱）

（2）請統計小明負責的各個品牌，在2017年連續3天增長超過50%的日期，及對應的銷售額。

###通過查詢類容創建表###################################

1

Create table if not exists temp

as

(Select a.* ,b.*

From a left join b on a.SKU_ID = b.SKU_ID

Where year(a.logday) = ‘2017’and b.user_name = ‘小明’)

Select a.*

From temp a

Where

(select count(b.sale_amt) from temp b where a.sale_amt < b.sale_amt and a.bu_name = b.bu_name and a.brand_name = b.brand_name ) < 3

Order by a.bu_name, a.brand_name,a.sale_amt

2.

Select

Distinct a.logday ,a.sale_amt ,a.bu_name ,a.brand_name

From temp a,temp b ,temp c

Where

a. bu_name = b.bu_name and a.brand_name = b.brand_name and

b. bu_name = b.bu_name and c.brand_name = b.brand_name

and

(a. sale_amt * 1.5 < b.sale_amt and b.sale_amt * 1.5 < c.sale_amt

Date_add(a.logday,interval 1 day) = b.logday and

Date_add(b.logday,interval 1 day) = c.logday )

Or

(b. sale_amt * 1.5 < a.sale_amt and a.sale_amt * 1.5 < c.sale_amt

And Date_add(b.logday,interval 1 day) = a.logday and

Date_add(a.logday,interval 1 day) = c.logday )

)

or

(b.sale_amt * 1.5 < c.sale_amt and c.sale_amt * 1.5 < a.sale_amt

Date_add(b.logday,interval 1 day) = c.logday and

Date_add(c.logday,interval 1 day) = a.logday )

)

Order by a.bu_name, a.brand_name,a.sale_amt

查詢student表中重名的學生，結果包含id和name，按name,id升序

Select s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id != s2.id

Order by s1.name ,s1.id

select id,name

from student

where name in (select name from student group by name having(count(*) > 1)

) order by name,id;

查詢student表中重名的學生，保留id小的

Select s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id < s2.id

Order by s1.name ,s1.id

Select s.name ,min(s.id) as id

From student s

Group by s.name

Order by s.name ,s.id

Delete

s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id > s2.id

Order by s1.name ,s1.id

總成績最高的學生，結果列出學生id、姓名和總成績

Select s.s_id ,s.name,sum(score) as sum_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id

Having sum(s1.score) >= (select max(sum(score))from score s2 group by s_id )

Select s.s_id ,s.name,sum(score) as sum_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id,s.name

在student_course表查詢課程1成績第2高的學生，如果第2高的不止一個則列出所有的學生

Select a.* from

(Select s.* ,s1.score

From (student s join score s1 on s.s_id = s1.s_id )

join course c on s1.c_id = c.c_Id and c_c_id = ‘01’

###多次的join

##或者 之前的作為表temp，score s1 where temp.s_id = s1.s_Id

)a

Where

(select count(distinct score) from

(Select s.* From student s join course c on s.c_id = c.c_id and c_c_id = ‘01’)b

Where a.score < b.score ) =1

新思路： 對成績分組

Select st.*,sc.score

From student st join score sc on st.s_id = sc.s_id

Where sc.score =

(Select s1.score

From (score s join course c on s.c_id = c.c_id and c.c_id = ‘01’)

join student s1 on s.s_id = s1.s_Id

Group by s1.score

Order by s1.score desc

Limit 1,1)

閱讀更多 洛鴻0920 的文章

關鍵字: 數據結構 MySQL 好評率