iT邦幫忙

0

SQL語法 兩張表關聯 其中一張表多筆重複資料 取日期最大 不用Group by的方法

請問我有兩張表
大約有裡面類型的資料

表A

pk   不重複編號      手機
ID   OrderId        tel
1   20211130000001 0987000111
2   20211130000002 0987000111
3   20211130000003 0987000222
3   20211130000004 0987000111
3   20211130000005 0987000222

表B

pk   重複編號         資料           日期
ID   OrderId         memo          date
1   20211130000001  '資料1'        '2021/11/3'
2   20211130000001  '資料2'        '2021/11/8'
3   20211130000003  '資料3'        '2021/11/8'
4   20211130000001  '資料4'        '2021/11/15' 
5   20211130000002  '資料4'        '2021/12/4'
6   20211130000003  '資料5'        '2021/12/9'
7   20211130000003  '資料6'        '2021/12/13'
8   20211130000004  '資料7'        '2021/12/13'
9   20211130000004  '資料8'        '2021/12/14'

需要變成
重複編號 取日期最大
(例如WHERE = '0987000111')

重複編號
     重複編號         手機                資料            日期
ID   OrderId         tel                 memo            date
1   20211130000001   '0987000111'        '資料1'        '2021/11/3'
2   20211130000001   '0987000111'        '資料2'        '2021/11/8'
3   20211130000001   '0987000111'        '資料4'        '2021/11/15' 
3   20211130000002   '0987000111'        '資料4'        '2021/12/4'
3   20211130000004   '0987000111'        '資料7'        '2021/12/13'
3   20211130000004   '0987000111'        '資料8'        '2021/12/14'

取最大日期
重複編號           手機                資料            日期
OrderId           tel                 memo            date
20211130000001   '0987000111'        '資料4'        '2021/11/15' 
20211130000002   '0987000111'        '資料4'        '2021/12/4'
20211130000004   '0987000111'        '資料8'        '2021/12/14'

因為要還撈memo欄位資料所以不用Group By
有試過已下方法了 但無法成功
JOIN

SELECT A.*, B.*
FROM A表 aa
LEFT JOIN
(
   SELECT TOP 1 * FROM B表
   WHERE OrderId = aa.OrderId --這裡有問題
   ORDER BY date DESC
)as bb
ON bb.OrderId = aa.OrderId
WHERE MemberTel = '0987000111'
ORDER BY OrderNum DESC

WHERE=/WHERE IN
但括號後面不是一個值(除非select一個欄位出來) 所以也不對

SELECT A.*, B.*
FROM A表 aa
WHERE OrderId =
(
   SELECT TOP 1 * FROM B表
   where OrderId = aa.OrderId --這個關聯有問題
	ORDER BY date DESC
)
AND MemberTel = '0987000111'
ORDER BY OrderNum DESC

後來寫到一個比較接近的
但出來的資料還是有重複QQ
應該是可以只取到黃色箭頭的資料的

https://ithelp.ithome.com.tw/upload/images/20211206/20120558YTDicWiHG1.png

目前都試不出來
求大神們給指教
謝謝

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1

JOIN不是給你這樣用的。

LEFT JOIN
(
   SELECT TOP 1 * FROM B表
   WHERE OrderId = aa.OrderId --這裡有問題
   ORDER BY date DESC
)as bb

改成

LEFT JOIN
(
   SELECT TOP 1 * FROM B表   
)as bb ON bb.OrderId = aa.OrderId

連ORDER也沒用。

看你的用法似乎還不懂的如何利用JOIN。

1
小山丘
iT邦新手 2 級 ‧ 2021-12-06 14:53:58
SELECT aa.*, bb.*
FROM A表 aa,
     B表 bb
WHERE bb.date=(SELECT MAX(cc.date) From B表 cc WHERE bb.OrderId = cc.OrderId)
AND aa.OrderId=bb.OrderId
AND aa.Tel ='0987000111'
1
天黑
iT邦研究生 5 級 ‧ 2021-12-06 14:57:22

先湊一個應該有更好的做法

先取出單號最大日期,在做join

Declare @tempT1 Table (Id int ,OrderId varchar(100) ,tel varchar(20))
insert into @tempT1
select 1,'20211130000001','0987000111' union
select 2,'20211130000002','0987000111' union
select 3,'20211130000003','0987000222' union
select 4,'20211130000004','0987000111' union
select 5,'20211130000005','0987000222' 

Declare @tempT2 Table (Id int ,OrderId varchar(100) ,memo varchar(100) ,date Datetime)
insert into @tempT2
select 1,'20211130000001','資料1','2021/11/3' union
select 2,'20211130000001','資料2','2021/11/8' union
select 3,'20211130000003','資料3','2021/11/8' union
select 4,'20211130000001','資料4','2021/11/15' union  
select 5,'20211130000002','資料4','2021/12/4' union
select 6,'20211130000003','資料5','2021/12/9' union
select 7,'20211130000003','資料6','2021/12/13' union
select 8,'20211130000004','資料7','2021/12/13' union
select 9,'20211130000004','資料8','2021/12/14' 

declare @tel varchar(20) = '0987000111';

WITH temp2maxDate (OrderId ,date)
AS 
(  
	select OrderId,Max(date) from @tempT2 GROUP BY OrderId
)

select t1.OrderId,t1.tel,t2.memo,t2.date
from @tempT1 t1
join @tempT2 t2 on t1.OrderId = t2.OrderId
join temp2maxDate t3 on t1.OrderId = t3.OrderId and t2.date = t3.date
where t1.tel = @tel
2
rogeryao
iT邦大師 1 級 ‧ 2021-12-06 15:25:32
CREATE TABLE AAA (
ID int,
OrderId nvarchar(20),
tel nvarchar(20));
 
INSERT INTO AAA
VALUES
(1,'20211130000001','0987000111'),
(2,'20211130000002','0987000111'),
(3,'20211130000003','0987000222'),
(3,'20211130000004','0987000111'),
(3,'20211130000005','0987000222');
CREATE TABLE BBB (
ID int,
OrderId nvarchar(20),
memo nvarchar(20),
Xdate date);
 
INSERT INTO BBB
VALUES
(1,'20211130000001',N'資料1','2021/11/3'),
(2,'20211130000001',N'資料2','2021/11/8'),
(3,'20211130000003',N'資料3','2021/11/8'),
(4,'20211130000001',N'資料4','2021/11/15'),
(5,'20211130000002',N'資料4','2021/12/4'),
(6,'20211130000003',N'資料5','2021/12/9'),
(7,'20211130000003',N'資料6','2021/12/13'),
(8,'20211130000004',N'資料7','2021/12/13'),
(9,'20211130000004',N'資料8','2021/12/14');
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY AAA.OrderId ORDER BY BBB.Xdate DESC) AS NO,
AAA.OrderId,AAA.tel,BBB.memo,BBB.Xdate
FROM AAA
INNER JOIN BBB ON BBB.OrderId = AAA.OrderId
WHERE AAA.tel = '0987000111'
) AS K
WHERE K.NO = 1

Demo

1
dscwferp
iT邦高手 1 級 ‧ 2021-12-06 16:29:05

因為要還撈memo欄位資料所以不用Group By

還是可以用 Group By
如下

SELECT A.*,D.*
FROM A,(
SELECT B.* FROM B,
(
select OrderId, max(Xdate) as Xdate
from B
group by OrderId
) C
where C.OrderId=B.OrderId and C.Xdate=B.Xdate
) D
WHERE A.OrderId=D.OrderId
2
一級屠豬士
iT邦大師 1 級 ‧ 2021-12-06 19:55:50

PostgreSQL 有一個特殊語法 distinct on 針對這類問題.但這不是標準的,在其他DB上無法使用.
提供大家參考.

create table it1206a (
  id int not null primary key
, orderid text unique
, tel text
);

insert into it1206a values
(1,'20211130000001','0987000111'),
(2,'20211130000002','0987000111'),
(3,'20211130000003','0987000222'),
(4,'20211130000004','0987000111'),
(5,'20211130000005','0987000222');

create table it1206b (
  id int not null primary key
, orderid text references it1206a(orderid)
, memo text
, xdate date
);

insert into it1206b values
(1,'20211130000001','資料1','2021/11/3'),
(2,'20211130000001','資料2','2021/11/8'),
(3,'20211130000003','資料3','2021/11/8'),
(4,'20211130000001','資料4','2021/11/15'),
(5,'20211130000002','資料4','2021/12/4'),
(6,'20211130000003','資料5','2021/12/9'),
(7,'20211130000003','資料6','2021/12/13'),
(8,'20211130000004','資料7','2021/12/13'),
(9,'20211130000004','資料8','2021/12/14');

select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc;

    orderid     | memo  |   xdate    
----------------+-------+------------
 20211130000001 | 資料4 | 2021-11-15
 20211130000002 | 資料4 | 2021-12-04
 20211130000003 | 資料6 | 2021-12-13
 20211130000004 | 資料8 | 2021-12-14
(4 rows)


with t1 as (
select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc
)
select t1.orderid
     , tel
     , memo
     , xdate
  from t1
  join it1206a
 using (orderid)
 order by 1;
 
    orderid     |    tel     | memo  |   xdate    
----------------+------------+-------+------------
 20211130000001 | 0987000111 | 資料4 | 2021-11-15
 20211130000002 | 0987000111 | 資料4 | 2021-12-04
 20211130000003 | 0987000222 | 資料6 | 2021-12-13
 20211130000004 | 0987000111 | 資料8 | 2021-12-14
(4 rows)

demo

我要發表回答

立即登入回答