iT邦幫忙

2022 iThome 鐵人賽

DAY 23
0
Software Development

新手小白的每天一點SQL系列 第 23

Day 23 SQLBolt - 7 :expressions

SQLBolt:https://sqlbolt.com/lesson/introduction

使用概念 & 語法

  • expression (表達式)

In addition to querying and referencing raw column data with SQL, you can also use expressions to write more complex logic on column values in a query. These expressions can use mathematical and string functions along with basic arithmetic to transform values when the query is executed, as shown in this physics example.

-- Example query with expressions
-- ABS 數值函數(Mathematical Functions) -> 取絕對值

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;
  • AS

The use of expressions can save time and extra post-processing of the result data, but can also make the query harder to read, so we recommend that when expressions are used in the SELECT part of the query, that they are also given a descriptive alias using the AS keyword.

Select query with expression aliases
SELECT col_expression AS expr_description, …
FROM mytable;

SQL Lesson 9: Queries with expressions

本章節介紹了expression,可以對查詢中的數據編寫更複雜的邏輯。比如將撈出來的基本數據使用數值函數(Mathematical Functions)與字串函數(String Functions)進行基本處理。再使用 AS 讓這些數據擁有一個方便閱讀的別名。

AS 的用法,我們在 Day 15 也有介紹過喔。

照例開始解題:

題目 1

/* 1. List all movies and their combined sales in millions of dollars*/
SELECT title, (domestic_sales + international_sales) / 1000000 AS  sales_in_millions_of_dollars
FROM movies JOIN boxoffice
ON movies.id = boxoffice.movie_id;

題目 2

/* 2. List all movies and their ratings in percent*/
SELECT title, rating * 10 AS rating_percent
FROM movies JOIN boxoffice
ON movies.id = boxoffice.movie_id;

題目 3

SELECT title, year
FROM movies
WHERE year % 2 = 0; -- 偶數年

明天開始,我們就要來介紹各種 SQL Function 啦~

參考資料:


上一篇
Day 22 SQLBolt - 6:NULLs
下一篇
Day 24 SQLBolt - 8:aggregates (Pt. 1)
系列文
新手小白的每天一點SQL31
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言