SQL實現Excel的10個常用功能的範例詳解

2022-07-25 14:04:54

01. 關聯公式:Vlookup

vlookup是excel幾乎最常用的公式，一般用於兩個表的關聯查詢等。所以我先建立一個新表：複製sale表並篩選出地區僅為廣州的，命名為sale_guang。

create table sale_guang
SELECT * from sale where city="廣州";

需求：根據訂單明細號關聯兩表，並且sale_guang只有訂單明細號與利潤兩列

SELECT * from sale a
inner JOIN
(SELECT ordernum,profit from sale_guang) b
on a.ordernum=b.ordernum

02. 對比兩列差異

需求：對比sale的訂單明細號與sale_guang訂單明細號的差異；

SELECT * from sale a
WHERE a.ordernum not in 
(SELECT b.ordernum from sale_guang b);

03. 去除重複值

需求：去除業務員編碼的重複值

SELECT * FROM sale
where salesnum not in 
(SELECT salesnum from sale
GROUP BY salesman
HAVING COUNT(salesnum)>1)

04. 缺失值處理

需求：用0填充缺失值或則刪除有地區名稱缺失值的行。

--用0填充：
update sale set city = 0 where city = NULL
--刪除有缺失值的行：
delete from sale where city = NULL;

05. 多條件篩選

需求：想知道業務員張愛，在北京區域賣的商品訂單金額大於等於6000的資訊。

SELECT * from sale
where salesman = "張愛" 
and city = "北京"
and orderaccount >=6000;

06. 模糊篩選資料

需求:篩選存貨名稱含有"三星"或則含有"索尼"的資訊。

SELECT * from sale
where inventoryname like "%三星%" 
or 存貨名稱 like "%索尼%";

07. 分類彙總

需求:北京區域各業務員的利潤總額。

SELECT city,sum(`profit`)
from sale
WHERE city = "北京"
GROUP BY `city`;

08. 條件計算

需求：存貨名稱含“三星字眼”並且稅費高於1000的訂單有幾個？這些訂單的利潤總和和平均利潤是多少？

--有多少個？
SELECT COUNT(*) from sale
where inventoryname like "%三星%"
and `tax` > 1000 ;

--這些訂單的利潤總和和平均利潤是多少？
SELECT `ordernum`,SUM(profit),AVG(`profit`)
from sale
where inventoryname like "%三星%"
and `tax` > 1000 
GROUP BY `ordernum`;

09. 刪除資料間的空格

需求：刪除存貨名稱兩邊的空格。

SELECT trim(inventoryname) from sale;

10. 合併與排序列

需求：計算每個訂單號的成本並從高到低排序（成本 = 不含稅金額 - 利潤）

SELECT city,ordernum,
(Nontaxamount - profit) as cost 
from sale
order by cost DESC;

總結：結構化查詢語言(Structured Query Language)簡稱SQL，果然和它名字一樣，查詢起來得心應手，但做想做資料處理方面，能明細感受到比Python和excel吃力（也可能是我還沒學好orz）。

SQL筆試題原題

貼一些我在面試時遇到過的SQL筆試題吧：

某資料服務公司

Student表

Score表

（1）查詢Student表中的所有記錄的Sname、Ssex和Class列。

select sname,ssex,class from student;

（2）查詢Score表中成績在60到80之間的所有記錄。

select * from score between 60 and 80;

（3）查詢95033班和95031班的平均分。

select class,avg(degree) from Score a
join student b
on a.sno = b.sno
GROUP BY CLASS；

總之是比較簡單的SQL筆試題了，當時很快就寫完了。實際上這不是原題，不過我有印象就是考察這幾個知識點，並且蠻簡單的。

某手遊公司的SQL筆試題（原題）

（1）建立表Student的語句寫下來，表Student是由學號Sno,姓名Sname,性別Ssex,年齡Sage,所在系Sdept五個屬性組成，其中學號屬性不能為空，並且其值是唯一的。

create table Student_new
(sno varchar(20) PRIMARY KEY,
sname varchar(10),ssex char(2),
sage int,sdept varchar(25))；

（2）在student 表中查詢Sdept是“計算機”的學生所有資訊並按SNO列排序。

select * from student
where sdept = "計算機" 
order by sno ;

（3）在以上三個表中查詢Ccredit為5並且Grade大於60的學生的學號、姓名和性別。

select a.sno,a.sname,a.ssex from student a
join (Course b ,SC c)
on a.sno=c.sno and b.cno =c.cno 
where Ccredit = 5 and Grade > 60；

某網際網路金融公司SQL筆試題（原題）

（1）表A和表B的交集：

SELECT a.cus_id from `表a` as a
INNER JOIN `表b` as b
on a.cus_id=b.cus_id；

（2）表A和表B的並集：

SELECT * from `表a`
UNION
SELECT * from `表b`；

（3）表A和表B的對稱差：

SELECT * from `表a` 
where cus_id not in (SELECT * from `表b`)
UNION
SELECT * from `表b` 
where cus_id not in (SELECT * from `表a`)；

（4）表A中存在但表B中不存在：

SELECT * from `表a`
WHERE cus_id not in (SELECT cus_id from `表b`)；

到此這篇關於SQL實現Excel的10個常用功能的範例詳解的文章就介紹到這了,更多相關SQL Excel常用功能內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！