facebook面试SQL

– 算排名

CREATE DATABASE sqloa;
use sqloa;
CREATE TABLE sqloa
    (`Name` varchar(8), `Sales` int)
;
    
INSERT INTO sqloa
    (`Name`, `Sales`)
VALUES
    ('John', 10),
    ('Jennifer', 15),
    ('Stella', 20),
    ('Sophia', 40),
    ('Greg', 50),
    ('Jeff', 20)
;

CREATE TABLE Total_Sales
    (`Name` varchar(8), `Sales` int)
;
    
INSERT INTO Total_Sales
    (`Name`, `Sales`)
VALUES
    ('John', 10),
    ('Jennifer', 15),
    ('Stella', 20),
    ('Sophia', 40),
    ('Greg', 50),
    ('Jeff', 20)
;

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

– 我们先来看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我们算出有多少笔资 料 Sales 栏位的值是比自己本身的值小或是相等。如果在 Sales 栏位中没有同样大小的资 料，那这部分的 WHERE 子句本身就可以产生出正确的排名。
– 子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，则是让我们在 Sales 栏位中 有同样大小的资料时 (像 Stella 及 Jeff 这两笔资料)，仍然能够产生正确的排名。

– 算中位数

SELECT a1.Sales as Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name) 
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

– 算累积总计

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales