CREATE TEMPORARY TABLE sales AS
SELECT
PRODUCT_SKU,
sum(GROSS_SALES - GROSS_RETURNS - COUPON_DISCOUNT) total_sales_ttm,
sum(GROSS_MARGIN) as gross_margin_ttm,
avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as avg_cost_ttm,
sum(QUANTITY_SOLD - QUANTITY_RETURNED) as unit_sold_ttm
FROM TABLE1
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;
CREATE TEMPORARY TABLE q_sales AS
SELECT
PRODUCT_SKU,
sum(GROSS_SALES_AMOUNT - GROSS_RETURNS_AMOUNT) q_total_sales_ttm,
sum(GROSS_MARGIN) as q_gross_margin_ttm,
avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as q_avg_cost_ttm,
sum(QUANTITY_SOLD - QUANTITY_RETURNED) as q_unit_sold_ttm
FROM TABLE2
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;
CREATE TEMPORARY TABLE prices AS
SELECT
rp.SKU,
rp.PRICE AS RETAIL_PRICE,
bp.PRICE AS BASELINE_PRICE
FROM TABLE3 rp
LEFT JOIN TABLE4 bp
ON rp.SKU=bp.SKU
WHERE
rp.history=1 AND bp.history=1;
//將臨時表合并為一個查詢
SELECT
sales.PRODUCT_SKU,
total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU
uj5u.com熱心網友回復：
因此，如果我制作一些測驗資料表：
CREATE TABLE sales AS
SELECT
1 as PRODUCT_SKU,
10 as total_sales_ttm,
11 as gross_margin_ttm,
5 as avg_cost_ttm,
4 as unit_sold_ttm
;
CREATE TABLE q_sales AS
SELECT
1 as PRODUCT_SKU,
12 as q_total_sales_ttm,
13 as q_gross_margin_ttm,
14 as q_avg_cost_ttm,
15 as q_unit_sold_ttm
;
CREATE TEMPORARY TABLE prices AS
SELECT
1 as SKU,
42 AS RETAIL_PRICE,
43 AS BASELINE_PRICE
您的選擇：
SELECT
sales.PRODUCT_SKU,
total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU;
給出：
| PRODUCT_SKU | TOTAL_SALES_TTM | GROSS_MARGIN_TTM | AVG_COST_TTM | Q_TOTAL_SALES_TTM | Q_GROSS_MARGIN_TTM | Q_AVG_COST_TTM | 零售價 | BASELINE_PRICE |
|---|---|---|---|---|---|---|---|---|
| 1 | 10 | 11 | 5 | 12 | 13 | 14 | 42 | 43 |
從而提出一個觀點：
CREATE VIEW view_name as
SELECT
sales.PRODUCT_SKU,
total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU;
因此：
select PRODUCT_SKU, TOTAL_SALES_TTM, GROSS_MARGIN_TTM from view_name;
給出：
| PRODUCT_SKU | TOTAL_SALES_TTM | GROSS_MARGIN_TTM |
|---|---|---|
| 1 | 10 | 11 |
您將需要確保其他用戶對視圖所在的資料庫/模式具有權限，并授予運行視圖的權限，從而也授予運行表的權限。
uj5u.com熱心網友回復：
您可能希望使用臨時表而不是臨時表，因為臨時表是在會話中限定的。
然后，您可以將這些視圖/表組合成一個視圖，并擁有對最終視圖具有 SELECT 訪問權限的角色，以便具有該角色的用戶對視圖具有只讀訪問權限。
如果您可以在這里進一步澄清您的問題，可以提供更好的幫助。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/474675.html