我想獲得我的產品詳細資訊，還想獲得每個產品的月銷售額，但我的銷售額在不同的表中。是否有任何選項可以組合這 2 個查詢并獲得所有 1 個查詢？
產品：
SELECT *
FROM products
WHERE store_id = 'ca4172e9-5627-4867-884d-d731895c33fe'
銷售量：
SELECT sku, SUM(amount) AS items, SUM(ARRAY_LENGTH(list, 1)) AS orders
FROM orders_summary
WHERE order_date BETWEEN to_timestamp(1633148000000 / 1000.0)
AND to_timestamp(1633676400000 / 1000.0)
AND sku = $1
GROUP BY sku
現在我正在對我的第一個資料執行 for 回圈，然后將每個銷售額添加到每一行。
我的產品表架構：
id uuid NOT NULL,
sku text COLLATE pg_catalog."default" NOT NULL,
store_id uuid NOT NULL,
amazon_inv_available integer DEFAULT 0,
amazon_inv_total integer DEFAULT 0,
asin text COLLATE pg_catalog."default",
big_image text COLLATE pg_catalog."default" DEFAULT 'test'::text,
small_image text COLLATE pg_catalog."default" DEFAULT 'test'::text,
cost_of_unit_product real,
fba_fee money,
package_height real,
package_length real,
package_width real,
package_weight real,
product_id text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
referral_fee real,
package_volume real,
box_per_pallet text[] COLLATE pg_catalog."default",
comment text COLLATE pg_catalog."default",
custom real,
outer_box_width text[] COLLATE pg_catalog."default",
outer_box_height text[] COLLATE pg_catalog."default",
outer_box_length text[] COLLATE pg_catalog."default",
shipping_cost_to_amazon real,
box_upc text[] COLLATE pg_catalog."default",
unit_per_outer_box text[] COLLATE pg_catalog."default",
unit_per_inner_box text[] COLLATE pg_catalog."default",
CONSTRAINT pk_orders_id PRIMARY KEY (id),
CONSTRAINT idx_orders UNIQUE (sku),
CONSTRAINT fk_orders_stores FOREIGN KEY (store_id)
REFERENCES public.stores (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
我的 order_summary 架構：
id uuid NOT NULL,
sku text COLLATE pg_catalog."default" NOT NULL,
store_id uuid NOT NULL,
order_date timestamp(0) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
asin text COLLATE pg_catalog."default" NOT NULL,
amount integer NOT NULL DEFAULT 0,
list text[] COLLATE pg_catalog."default" NOT NULL,
price money DEFAULT 0,
CONSTRAINT pk_orders_summary_id PRIMARY KEY (id),
CONSTRAINT fk_orders_summary_stores FOREIGN KEY (store_id)
REFERENCES public.stores (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
uj5u.com熱心網友回復：
假設產品由skutable 中的列標識order_summary，那么查詢可能是：
SELECT *
FROM products AS p
CROSS JOIN LATERAL
( SELECT sku, SUM(amount) AS items, SUM(ARRAY_LENGTH(list, 1)) AS orders
FROM orders_summary
WHERE order_date BETWEEN to_timestamp(1633148000000 / 1000.0)
AND to_timestamp(1633676400000 / 1000.0)
GROUP BY sku
) AS o
ON o.sku = p.sku
WHERE p.store_id = 'ca4172e9-5627-4867-884d-d731895c33fe'
AND p.sku = $1
順便說一句，我建議您在 tableorders_summary中添加一個外鍵以參考table中的sku列products：
CONSTRAINT fk_orders_summary_sku FOREIGN KEY (sku)
REFERENCES public.products (sku) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT -- rather than CASCADE so that to prevent the deletion of a product which has already been ordered
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/352997.html
標籤：sql PostgreSQL
上一篇：引數化CTE