SQL 基礎
Last updated
Was this helpful?
Last updated
Was this helpful?
Was this helpful?
http://www.postgresqltutorial.com/
SELECT device_token FROM users WHERE device_token IS NOT NULL AND device_token != '';
SELECT DISTINCT ON device_token FROM users;
https://www.w3schools.com/sql/sql_datatypes.asp
ALTER TABLE <Table名稱> ALTER COLUMN <column名稱> SET DEFAULT <預設值>;
或是建表時設定
可以使用 with as,後面一定要接著 select 語句
也可參考 postGIS https://postgis.net/
// 先在 SQL 輸入以下,安裝插件
create extension cube;
create extension earthdistance;
// 搜尋範例
select (point(-0.1277,51.5073) <@> point(-74.006,40.7144)) as distance;
https://stackoverflow.com/a/25140461
搜尋兩人距離範例:
select (
point(
(select latlng from user_account where first_name = 'Manel')
)
<@>
point(
(select latlng from user_account where first_name = 'Ben')
)
) as distance
完整範例:
DISTINCT ON 只有 postgres 有, MySQL 要使用 group by,另外 postgres 的 group by 不能單獨選擇特定對象,必須要包含在 select 裡面
SELECT *
FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
) p
ORDER BY purchased_at DESC;
https://stackoverflow.com/a/9796104/4622645
如果 A 有多筆資料,想要 DISTINCT A 中最新的,之後把選出來的 A, B, C 再依照時間排序
SELECT *
FROM (
SELECT DISTINCT ON (customer_name) *
FROM orders
ORDER BY customer_name, create_time DESC
) p
ORDER BY create_time DESC;