select '姓名',
sum(case '收入類型' when '年獎(jiǎng)金' then '收入金額' else 0 end) 年獎(jiǎng)金,
sum(case '收入類型' when '月工資' then '收入金額' else 0 end) 月工資
from A
group by '姓名'

select * from
(
    select 姓名,收入類型,收入金額 from A
) test
pivot(sum(收入金額) for 收入類型 in ('月工資','年終獎(jiǎng)')) pvt

–1.學(xué)生表 
Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別 
–2.課程表 
Course(c_id,c_name,t_id) – –課程編號(hào), 課程名稱, 教師編號(hào) 
–3.教師表 
Teacher(t_id,t_name) –教師編號(hào),教師姓名 
–4.成績(jī)表 
Score(s_id,c_id,s_score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)

--建表
--學(xué)生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--課程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教師表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成績(jī)表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入學(xué)生表測(cè)試數(shù)據(jù)
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--課程表測(cè)試數(shù)據(jù)
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');

--教師表測(cè)試數(shù)據(jù)
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成績(jī)表測(cè)試數(shù)據(jù)
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

SELECT st.*,sc.`s_score` AS '語(yǔ)文' ,sc2.`s_score` AS '數(shù)學(xué)' 
FROM student st 
LEFT JOIN score sc ON st.s_id=sc.`s_id` AND sc.`c_id`='01'
LEFT JOIN score sc2 ON st.s_id=sc2.`s_id` AND sc2.`c_id`='02'  
WHERE sc.`s_score` > sc2.`s_score`;

SELECT s.`s_id`,s.`s_name`,ROUND(AVG(sc.`s_score`),2) AS '平均成績(jī)' FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) >= 60;

SELECT s.`s_id`,s.`s_name`,COUNT(sc.`c_id`) AS '選課總數(shù)',SUM(CASE WHEN sc.`s_score` IS NULL THEN 0 ELSE sc.`s_score` END) AS '總成績(jī)' FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`

SELECT s.* FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
LEFT JOIN course c ON sc.`c_id` = c.`c_id`
LEFT JOIN teacher t ON t.`t_id` = c.`t_id`
WHERE t.`t_name` = "張三"

 SELECT st.* FROM student st WHERE st.s_id NOT IN(
  SELECT sc.s_id FROM score sc WHERE sc.c_id IN (SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id=c.t_id WHERE  t.t_name="張三")
  )

SELECT s.* FROM student s
INNER JOIN score sc ON s.`s_id` = sc.`s_id` 
INNER JOIN score sc1 ON s.`s_id` = sc1.`s_id`
WHERE sc.`c_id`='01' AND sc1.`c_id`='02'

--方式二
SELECT a.* 
FROM
    student a,
    score b,
    score c
WHERE
    a.s_id = b.s_id
    AND a.s_id = c.s_id
    AND b.c_id = '01'
    AND c.c_id = '02';

SELECT DISTINCT s.* FROM student s 
LEFT JOIN score c ON s.`s_id` = c.`s_id`
WHERE c.`c_id` IN (
    SELECT sc.`c_id` FROM student s
    LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
    WHERE s.`s_id`='01'
);

SELECT DISTINCT s.* FROM student s 
LEFT JOIN score c ON s.`s_id` = c.`s_id`
GROUP BY s.`s_id`
HAVING COUNT(c.`c_id`) = (
    SELECT COUNT(sc.`c_id`) FROM student s
    LEFT JOIN score sc ON s.`s_id` = sc.`s_id`
    WHERE s.`s_id`='01'
);

SELECT s.`s_name` FROM student s
WHERE s.`s_id` NOT IN(
    SELECT sc.`s_id` FROM score sc
    LEFT JOIN course c ON sc.`c_id` = c.`c_id`
    LEFT JOIN teacher t ON t.`t_id` = c.`t_id`
    WHERE t.`t_name`="張三"
)

SELECT s.`s_id`,s.`s_name`,AVG(sc.`s_score`) FROM student s 
INNER JOIN score sc ON s.`s_id` = sc.`s_id`
WHERE s.`s_id` IN (
    SELECT sc.`s_id` FROM score sc 
    WHERE sc.`s_score`<60
    GROUP BY sc.`s_id`
    HAVING COUNT(1)>=2
)
GROUP BY s.`s_id`

SELECT s.`s_id`,s.`s_name`,sc.`s_score` AS "語(yǔ)文" ,sc1.`s_score` AS "數(shù)學(xué)",sc2.`s_score` AS "英語(yǔ)",AVG(sc3.`s_score`) "平均分" FROM student s 
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id` = "01"
LEFT JOIN score sc1 ON s.`s_id` = sc1.`s_id` AND sc1.`c_id` = "02"
LEFT JOIN score sc2 ON s.`s_id` = sc2.`s_id` AND sc2.`c_id` = "03"
LEFT JOIN score sc3 ON s.`s_id` = sc3.`s_id`  
GROUP BY s.`s_id`
ORDER BY AVG(sc3.`s_score`) DESC

SELECT c.`c_id`,c.`c_name`,MAX(s.`s_score`) "最高分",MIN(s.`s_score`) "最低分",AVG(s.`s_score`) "平均分", 
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 60)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "及格率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 80 >= sc.`s_score` AND sc.`s_score` >= 70)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "中等率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND 90 >= sc.`s_score` AND sc.`s_score` >= 80)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "優(yōu)良率",
((SELECT COUNT(1) FROM score sc WHERE sc.`c_id` = c.`c_id` AND sc.`s_score` >= 90)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id = c.c_id)) "優(yōu)秀率"
FROM course c
LEFT JOIN score s ON c.`c_id` = s.`c_id`
GROUP BY c.`c_id`; 

(SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="01"
LEFT JOIN course c ON sc.`c_id` = c.`c_id`
ORDER BY sc.`s_score` DESC
LIMIT 1,2)
UNION ALL
(SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="02"
LEFT JOIN course c ON sc.`c_id` = c.`c_id` 
ORDER BY sc.`s_score` DESC
LIMIT 1,2)
UNION ALL
(SELECT s.*,c.`c_name`,sc.`s_score` "成績(jī)" FROM student s
LEFT JOIN score sc ON s.`s_id` = sc.`s_id` AND sc.`c_id`="03"
LEFT JOIN course c ON sc.`c_id` = c.`c_id` 
ORDER BY sc.`s_score` DESC
LIMIT 1,2)

SET @i=0;
SELECT test.*,@i:=@i+1 "名次" FROM(
SELECT s.`s_name`,ROUND(AVG(sc.`s_score`),2) "平均成績(jī)" FROM score sc
LEFT JOIN student s ON s.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
ORDER BY AVG(sc.`s_score`) DESC) test;

SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
WHERE (
SELECT COUNT(1) FROM student st2 
LEFT JOIN score sc2 ON sc2.s_id=st2.s_id
LEFT JOIN course c2 ON c2.c_id=sc2.c_id
WHERE sc.s_score=sc2.s_score AND c.c_id!=c2.c_id 
)>=1

SELECT a.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 0,2) a
UNION ALL
SELECT b.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="02"
ORDER BY sc.s_score DESC LIMIT 0,2) b
UNION ALL
SELECT c.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="03"
ORDER BY sc.s_score DESC LIMIT 0,2) c

SELECT a.s_id,a.c_id,a.s_score FROM score a
WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id

SELECT st.* FROM student st 
WHERE WEEK(NOW())=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))

SELECT st.* FROM student st 
WHERE WEEK(NOW())+1=WEEK(DATE_FORMAT(st.s_birth,'%Y%m%d'))

SELECT st.* FROM student st 
WHERE MONTH(NOW())=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))

SELECT st.* FROM student st 
WHERE MONTH(TIMESTAMPADD(MONTH,1,NOW()))=MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))

SELECT st.* FROM student st WHERE (MONTH(NOW()) + 1) MOD 12 = MONTH(DATE_FORMAT(st.s_birth,'%Y%m%d'))