SQL合併兩個表格的問題

sql join #sqljoin

lsesroom 2022-11-04 15:39:34 ‧ 1013 瀏覽

分享至

我有一個顧客產品訂購資料表A簡單表示如下

c_no year mon num
c001 2020 12 50
c001 2021 1 100
c001 2021 3 50
c002 2021 1 70
c002 2021 3 50
c002 2021 4 90
c002 2021 7 100

另一個訂購規格表B簡單表示如下

c_no year mon color size
c001 2021 1 red M
c002 2021 2 blue S
c002 2021 4 yellow XL

我想將上面兩個表格合併如下

c_no year mon num color size
c001 2020 12 50 red M
c001 2021 1 100 red M
c001 2021 3 50 red M
c002 2021 1 70 blue S
c002 2021 3 50 blue S
c002 2021 4 90 yellow XL
c002 2021 7 100 yellow XL

也就是說
訂購規格表(表B)與產品訂購表(表A)的時間上並非重合。
在訂購規格登錄(表B)的時間點之前及之後所有的訂單(表A)的產品規格需參照該登錄的規格計算，直至下一次訂購規格登錄才改為新的規格。
請問我要如何使用SQL指令，以表A為基礎join表B的資料呢？

jim_lai iT邦新手 5 級 ‧ 2022-11-04 16:58:51 檢舉

就結果看不出有什麼邏輯可用語法來完成

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

純真的人

iT邦大師 1 級 ‧ 2022-11-04 17:49:37

最佳解答

@@".....用慣用SQL

declare @data table(
	c_no varchar(50)
	,years int
	,mon int
	,num int
)

declare @head table(
	c_no varchar(50)
	,years int
	,mon int
	,color varchar(50)
	,size varchar(50)
)

insert into @data
values('c001',2020,12,50)
,('c001',2021,1,100)
,('c001',2021,3,50)
,('c002',2021,1,70)
,('c002',2021,3,50)
,('c002',2021,4,90)
,('c002',2021,5,100)

insert into @head
values('c001',2021,1,'red','M')
,('c002',2021,2,'blue','S')
,('c002',2021,4,'yellow','XL')

select a.*
,isNull((
	select top 1 color
	from @head c
	where a.c_no = c.c_no
	and Convert(varchar,a.years) + '/' + Convert(varchar,a.mon) + '/1'
	>= Convert(varchar,c.years) + '/' + Convert(varchar,c.mon) + '/1'
	order by c.years,c.mon desc
),(
	select top 1 color
	from @head c
	where a.c_no = c.c_no
	and Convert(varchar,a.years) + '/' + Convert(varchar,a.mon) + '/1'
	< Convert(varchar,c.years) + '/' + Convert(varchar,c.mon) + '/1'
	order by c.years,c.mon
)) color
,isNull((
	select top 1 size
	from @head c
	where a.c_no = c.c_no
	and Convert(varchar,a.years) + '/' + Convert(varchar,a.mon) + '/1'
	>= Convert(varchar,c.years) + '/' + Convert(varchar,c.mon) + '/1'
	order by c.years,c.mon desc
),(
	select top 1 size
	from @head c
	where a.c_no = c.c_no
	and Convert(varchar,a.years) + '/' + Convert(varchar,a.mon) + '/1'
	< Convert(varchar,c.years) + '/' + Convert(varchar,c.mon) + '/1'
	order by c.years,c.mon
)) size
from @data a

回應 1
分享
檢舉

lsesroom iT邦新手 2 級 ‧ 2022-11-08 08:08:54 檢舉

感謝解惑。我使用的是Oracle資料庫，不支援top及isNull語法；但可以用NVL及rownum執行相同的邏輯判斷及篩選。

登入發表回應

實習工程師

iT邦新手 1 級 ‧ 2022-11-04 16:40:53

CREATE TABLE #TABLE_A (
c_no VARCHAR(20)
,[year]  VARCHAR(20)
,mon VARCHAR(20)
,num VARCHAR(20)
)


CREATE TABLE #TABLE_B (
c_no VARCHAR(20)
,[year]  VARCHAR(20)
,mon VARCHAR(20)
,color VARCHAR(20)
,size VARCHAR(20)
)


INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c001','2020','12','50')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c001','2021','1','100')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c001','2021','3','50')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c002','2021','1','70')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c002','2021','3','50')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c002','2021','4','90')
INSERT INTO #TABLE_A (c_no,[year],mon,num)Values('c002','2021','7','100')


INSERT INTO #TABLE_B (c_no,[year],mon,color,size)Values('c001','2021','1','red','M')
INSERT INTO #TABLE_B (c_no,[year],mon,color,size)Values('c002','2021','2','blue','S')
INSERT INTO #TABLE_B (c_no,[year],mon,color,size)Values('c002','2021','4','yellow','XL')

SELECT 
	#TABLE_A.c_no 
	,#TABLE_A.[year] 
	,#TABLE_A.[mon] 
	,#TABLE_A.num 
	,#TABLE_B.color 
	,#TABLE_B.size 
FROM #TABLE_A
	LEFT JOIN #TABLE_B
		ON #TABLE_A.c_no = #TABLE_B.c_no


DROP TABLE #TABLE_A
DROP TABLE #TABLE_B

是這樣嗎?

回應 1
分享
檢舉

實習工程師 iT邦新手 1 級 ‧ 2022-11-04 17:14:22 檢舉

我懂版主意思了，版主是要根據 "資料表B"時間為依據，去取得 "資料表A"的前一筆資料、後一筆資料。

登入發表回應

alien663

iT邦研究生 3 級 ‧ 2022-11-09 10:53:00

我看完之後，猜測需求如下

取表A資料進B查詢
當表A時間在表B中可以找到 A.date betwen B.date1 and B.date2，則輸出B.date1
若A.date小於表B所有時間，則輸出表B最小值
若A.date大於表B所有時間，則輸出表B最大值

小弟我資歷尚淺，感覺這需求比較複雜，單純靠Query沒有辦法處理，覺得寫一個function輔助輸出資料比較實際一點。
另外，為了符合這筆資料(c002 2021 4 90 yellow XL)的結果，呼叫function我是用2號帶進去的。

create or alter function fn_ttt(@cno varchar(10), @the_date date)
	returns @result table(color varchar(10), size varchar(10))
as
begin
	declare @temp table(ID int, c_no varchar(10), [year] int, mon int, color varchar(10), size varchar(10))
	insert into @temp(ID, c_no, [year], mon, color, size)
		select ROW_NUMBER() over(order by [year], mon) as ID, T.*
		from (values
			('c001', 2021, 1, 'red', 'M'),
			('c002', 2021, 2, 'blue', 'S'),
			('c002', 2021, 4, 'yellow', 'XL')
		) T(c_no, [year], mon, color, size)
	declare @id int = (
		select T1.ID from @temp T1 inner join @temp T2 on T1.c_no = T2.c_no and T1.ID = T2.ID-1
		where T1.c_no = @cno and @the_date
			between convert(date, CONCAT(T1.[year], '/', T1.mon, '/01'))
				and convert(date, CONCAT(T2.[year], '/', T2.mon, '/01'))
	)
	if @id is null begin
		if not exists(select 1 from @temp where c_no = @cno and @the_date < convert(date, CONCAT([year], '/', mon, '/01')))
			set @id = (select max(ID) from @temp where c_no = @cno)
		else if not exists(select 1 from @temp where c_no = @cno and @the_date > convert(date, CONCAT([year], '/', mon, '/01')))
			set @id = (select min(ID) from @temp where c_no = @cno)
	end
	insert into @result(color, size)
		select color, size
		from @temp
		where ID = @id
		return;
end
go

select row_number() over(order by T.c_no) as ID,* into #aa
from (
    values
        ('c001', 2020, 12, 50)
        ('c001', 2021, 1, 100)
        ('c001', 2021, 3, 50),
        ('c002', 2021, 1, 70),
        ('c002', 2021, 3, 50),
        ('c002', 2021, 4, 90),
        ('c002', 2021, 7, 100)
) T(c_no,[year],mon,num)

select A.c_no, A.[year], A.mon, A.num, F.color, F.size
	from #aa A cross apply fn_ttt(A.c_no, convert(date, CONCAT(A.[year], '/', A.mon, '/02'))) F