Sql的Cross Join
用於把兩張表做乘積
簡單來說就是兩張資料表的排列組合
例如一張表資料是A,B
另外一張表資料是1,2,3
Cross Join的結果就是
A1, A2, A3, B1, B2, B3

會用到Cross Join
是因為有一批新上架的產品
需要根據原本的美金價格
去設定其他幣別對應的價格

50個產品，加上10種幣別
就是500條資料
這時候使用Cross Join就會滿適合的

假設今天有兩張表
一張是產品的價格
一張是幣別匯率表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 產品價格表
CREATE TABLE [dbo].[Cfg_Product_Price](
	[Sn] [int] IDENTITY(1,1) NOT NULL,
	[productSn] [int] NOT NULL,
	[Currency] [nvarchar](50) NOT NULL,
	[Price] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Cfg_Product_Price]
           ([productSn]
           ,[Currency]
           ,[Price])
     VALUES
        (1,'USD',100),
        (2,'USD',200),
        (3,'USD',300),
        (3,'JPY',999)

cross-join-1.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 產品匯率表
CREATE TABLE [Cfg_Currency](
	[Sn] [int] IDENTITY(1,1) NOT NULL,
	[Currency] [nvarchar](50) NOT NULL,
	[Rate] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Cfg_Currency]
           ([Currency]
           ,[Rate])
     VALUES
       ('TWD',30),
       ('JPY',142),
       ('KRW',1310)

cross-join-2.png

要使用產品的美金價格
撈出對應的其他幣別價格
就可以使用Cross Join

1
2
3
4
5
6
7
8
9
-- 美金和其他匯率的排列組合
SELECT a1.productSn      AS productSn,
       b.currency        AS currency,
       b.rate * a1.price AS price
              
FROM   cfg_product_price AS a1
CROSS JOIN cfg_currency AS b

WHERE  a1.currency = 'USD'

cross-join-3.png

如果部分幣別已經新增過
就可以再left join自己一次
排除掉重複的部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT    c.productSn,
          c.currency,
          c.price
FROM      (
          -- 美金和其他匯率的排列組合
          SELECT     a1.productSn      AS productSn,
                     b.currency        AS currency,
                     b.rate * a1.price AS price
          FROM       cfg_product_price AS a1
          CROSS JOIN cfg_currency      AS b
          WHERE      a1.currency = 'USD') AS c

-- 過濾已經塞過的幣別
LEFT JOIN cfg_product_price AS a2   ON
          c.productSn = a2.productSn AND 
          c.currency =  a2.currency
WHERE     a2.currency IS NULL

cross-join-4.png

最後確認資料無誤
就可以用Insert Select
做資料新增

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
INSERT INTO [Cfg_Product_Price]
           ([productSn]
           ,[Currency]
           ,[Price]) 

SELECT    c.productSn,
          c.currency,
          c.price
FROM      (
          -- 美金和其他匯率的排列組合
          SELECT     a1.productSn      AS productSn,
                     b.currency        AS currency,
                     b.rate * a1.price AS price
          FROM       cfg_product_price AS a1
          CROSS JOIN cfg_currency      AS b
          WHERE      a1.currency = 'USD') AS c

-- 過濾已經塞過的幣別
LEFT JOIN cfg_product_price AS a2   ON
          c.productSn = a2.productSn AND 
          c.currency =  a2.currency
WHERE     a2.currency IS NULL

如果後台沒有批次新增功能
也沒有API可以打
純Sql也是可以擋一下
cross-join-5.png