SQL~~一個較複雜的表格聯結 (join) 範例

在這個 table join 範例中，使用了 inner join, cross join, full outer join, 以 AS 將 sub-select 視為 table 再 join ，以及 group, case 等用法。這個範例雖然很長，但只是一句 SQL 查詢。拆開來是跑不出結果的。

此範例實際上取自我為了我任職的公司所寫的一個進銷存報表程式。我目前任職的公司，採用國內飛X 公司所設計的零售業進銷存 POS 系統。這個範例中的表格及欄位名稱，直接對應該 POS 系統。另外，我是用 PHP 寫這隻報表程式，所以範例中嵌有 PHP 的變數名稱。此報表程式係依據進貨表格 (pos204) 、銷貨表格 (pos324) 及庫存表格 (product_stock) 中的貨品數量，計算出本期的期末庫存。表格 produt_stock 是我新增的，原先飛X 設計的 POS 系統中，並沒有這個表格。

select 
	case when tStock_In.shop is NULL then tSale_qty.shop else tStock_In.shop end as shop, 
	case when tStock_In.prod is NULL then tSale_qty.prod else tStock_In.prod end as prod, 
	pos110.pname, pos110.pluno, pos116.lprc,
	case when tStock_In.stock_qty is NULL then 0 else tStock_In.stock_qty end as pre_stock_qty,
	case when tStock_In.in_qty is NULL then 0 else tStock_In.in_qty end as in_qty,
	case when tSale_qty.sale_qty is NULL then 0 else tSale_qty.sale_qty end as sale_qty,
	case 
	when tSale_qty.sale_qty is NULL then (tStock_In.stock_qty + tStock_In.in_qty) 
	when tStock_In.stock_qty is NULL and tStock_In.in_qty is NULL then -tSale_qty.sale_qty
	else (tStock_In.stock_qty + tStock_In.in_qty - tSale_qty.sale_qty) end as new_stock_qty
from
(	select 
		case when tStock_qty.shop is NULL then tInqty.shop else tStock_qty.shop end as shop, 
		case when tStock_qty.prod is NULL then tInqty.prod else tStock_qty.prod end as prod, 
		case when tStock_qty.stock_qty is NULL then 0 else tStock_qty.stock_qty end as stock_qty, 
		case when tInqty.in_qty is NULL then 0 else tInqty.in_qty end as in_qty
	from (
		select product_stock.shop, product_stock.prod, product_stock.stock_qty 
		from product_stock where supp = '{$supp}' and grade_date = '{$grade_date}' 
	) as tStock_qty
	full outer join
	(	select pos204.shop, pos116.prod, cast(sum(pos204.inqty) as integer) as in_qty
		from pos116 
		inner join pos204 
		on pos116.prod = pos204.prod and pos204.indate >= '{$sdate}' and pos204.indate <= '{$edate}' and pos204.gctrl = '4'
		where pos116.supp = '{$supp}' and pos116.ano = '{$ano}'
		group by pos204.shop, pos116.prod 
	) as tInqty
	on tStock_qty.shop = tInqty.shop and tStock_qty.prod = tInqty.prod 
) as tStock_In
full outer join 
(	select pos324.shop, pos324.prod, sum(pos324.qty) as sale_qty
	from pos324
	inner join pos116 
	on pos324.prod = pos116.prod and pos116.ano = '{$ano}' and pos116.supp = '{$supp}'
	where pos324.ecrdate >= '{$sdate}' and pos324.ecrdate <= '{$edate}'
	group by pos324.shop, pos324.prod 
) as tSale_qty
on tStock_In.shop = tSale_qty.shop and tStock_In.prod = tSale_qty.prod
inner join (pos110 
	inner join pos116 
	on pos110.prod = pos116.prod and pos116.ano = '{$ano}' and pos116.supp = '{$supp}') 
on tStock_In.prod = pos116.prod or tSale_qty.prod = pos116.prod
order by tStock_In.shop, tStock_In.prod;

SQL99 中取代子查詢表格的功能 - CTE (Common Table Expression)

樂多舊網址: http://blog.roodo.com/rocksaying/archives/1186860.html

樂多舊回應

未留名 (#comment-24895745)

Fri, 11 Jul 2014 17:35:07 +0800

作者您好，看了您的SQL語句相信你對SQL與訪箱當熟練。
小弟我剛出社會。
最近我也在幫公司做一個後端系統(MySQL)，系統中有需要用到INSERT語法。

我想請問
INSERT INTE TABLE (A,B,C,D) VALUES (1,2,3,????)
以上????的值地方我需要從另一張表格查尋出來，但語法好像不能這樣寫。
請問作者有解決的方法嗎?

未留名 (#comment-24895837)

Fri, 11 Jul 2014 18:22:09 +0800

不好意思，以上INTE打錯，應該是INTO。

未留名 (#comment-24919926)

Mon, 14 Jul 2014 11:05:06 +0800

你可以試試 WITH ，但我不確定 MySQL 支不支援 (我從來不把 MySQL 當標準SQL資料庫)。
WITH (CTE): http://blog.roodo.com/rocksaying/archives/2346392.html
http://msdn.microsoft.com/zh-tw/library/ms174335.aspx

例如:
WITH InsertTemp (A, B, C, D) AS ( SELECT 1, 2, 3, Table1.D FROM Table1 )
INSERT INTO Table2 SELECT * FROM InsertTemp;

ps. Microsoft SQL Server 2005 和 PostgreSQL 9.1 可用.

SQL~~一個較複雜的表格聯結 (join) 範例

相關文章

樂多舊回應