目录
SQL知识与练习
日期
-- 查询当年 当月 当天 过去七天 某个日期后的 30 天
-- SELECT * FROM ods_bctulmarket WHERE businessdate BETWEEN '2024-01-01' AND '2024-12-31';
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= DATE_TRUNC('year',CURRENT_DATE);
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= DATE_TRUNC('month',CURRENT_DATE);
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW();
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW()- INTERVAL '7 days';
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= NOW() - INTERVAL '300 days';
--
-- SELECT * FROM ods_bctulmarket WHERE businessdate >= Date '2024-01-01' + INTERVAL '29 days';
--
-- SELECT * FROM ods_bctulmarket WHERE EXTRACT(YEAR FROM businessdate) = 2023;
-- SELECT * FROM ods_bctulmarket WHERE EXTRACT(MONTH FROM businessdate) = 2;
ROW_NUMBER、SUBSTR、SUBSTR和COALESCE方法
--解读 ROW_NUMBER() 为 document_id 生成序号时,通常需要一个排序依据
ods_document_tmp as ( -- 生成凭证号
SELECT instruction_no, ROW_NUMBER ( ) OVER ( ) AS "document_id" FROM order_1 GROUP BY instruction_no
),
ROW_NUMBER() OVER (ORDER BY instruction_no):在每个唯一的 instruction_no 组内为每行生成唯一的序号,且按 instruction_no 排序。
--解读
SUBSTR( REPLACE ( cast (bill_date as text), '-', '' ), 1, 6 ) || odt.document_id AS "document_id",
cast (bill_date as text) 将时间转为文本
REPLACE(..., '-', ''):去除 bill_date 中的所有 - 符号,例如将 2024-10-24 转换为 20241024
SUBSTR(..., 1, 6):截取转换后的字符串前 6 个字符,如202410
|| odt.document_id:将截取的年月字符串与 odt.document_id 进行字符串拼接 如20241000007
odt就是ods_document_tmp
--保留两位小数
COALESCE(round( "origin_currency", 2 ),0) AS "origin_currency",
COALESCE(..., 0):如果 origin_currency 是 NULL,则返回 0,否则返回经过 ROUND 处理的 origin_currency 值。
---sql不区分大小写,需要”“ 保证大小写
LIKE、INNER JOIN、LEFT OUTER JOIN
SELECT * FROM table_test WHERE quantity > 10000 and trade_date >'2024-11-1';
SELECT * FROM table_test WHERE trade_date BETWEEN '2024-10-30' AND '2024-11-4';
SELECT * FROM "Pro_Product" WHERE "number" LIKE 'OCO%' -- 以OCO开头的数据
SELECT * FROM "Pro_Product" WHERE "number" LIKE '%TAC7' -- 以TAC7结尾的数据
SELECT * FROM "Pro_Product" WHERE "number" LIKE '_C%17' -- C在第二个位置并且以17结尾的数据
SELECT * FROM table_test WHERE quantity::TEXT LIKE '527%' ---数量以527开头的数据
SELECT * FROM table_test WHERE account NOT IN ('41183','53378')
SELECT * FROM table_test WHERE quantity IS NOT NULL
SELECT quantity FROM table_test WHERE quantity>300000
SELECT * FROM table_test LIMIT 5 OFFSET 2 -- 第三位开始提取 3 个记录
SELECT * FROM table_test ORDER BY quantity,account ASC -- 升序排列 降序 DESC
SELECT account, "sum"(quantity) AS total FROM table_test GROUP BY account
SELECT account, SUM(quantity) AS total FROM table_test GROUP BY account -- GROUP 必须和聚合函数一起使用 sum AVG COUNT
SELECT account, SUM(quantity) AS total FROM table_test GROUP BY account ORDER BY account ASC
SELECT account FROM table_test GROUP BY account HAVING COUNT(account) >1; --account 字段值进行分组,并且名称的计数大于 1 数据
SELECT DISTINCT account FROM table_test
SELECT account FROM table_test
WHERE account IN (SELECT account,quantity FROM table_test WHERE quantity >300000);
--CROSS JOIN :交叉连接 -- 第一个表的每一行与第二个表的每一行进行匹配
--INNER JOIN:内连接 --连接谓词结合两个表的列值来创建一个新的结果表
--LEFT OUTER JOIN:左外连接 --执行内部连接。连接的表在 T1 中每一行至少有一行 ,不满足条件有null也会添加一个连接行
--RIGHT OUTER JOIN:右外连接 --执行内部连接。对于表T2中不满足表T1中连接条件的每一行,其中T1列中的值为空也会添加一个连接行
--FULL OUTER JOIN:全外连接 -- 执行内部连接。然后,对于表 T1 中不满足表 T2 中任何行连接条件的每一行,如果 T2 的列中有 null 值也会添加一个到结果中。此外,对于 T2 中不满足与 T1 中的任何行连接条件的每一行,将会添加 T1 列中包含 null 值的到结果中。
SELECT * FROM table_test
INNER JOIN "Account_Maping" ON table_test.account = "Account_Maping"."ExternalAccountNumber";
SELECT * FROM table_test
LEFT OUTER JOIN "Account_Maping" ON table_test.account = "Account_Maping"."ExternalAccountNumber"
SELECT * FROM "Account_Maping" LIMIT 5;
SELECT COALESCE ( acct."AccountId", '待确认' ) AS tradingaccount,...FROM -- 将acct表中的AccountId 重新命名为tradingaccount,如果没有找到就会返回待确认
标签:account,知识,--,练习,SQL,test,table,WHERE,SELECT
From: https://www.cnblogs.com/zhanglanhua/p/18546239