如上圖,第一個表的字段包含 內碼、編號、客戶、日期,第二個表包含出庫單等字段,現在查詢出來的數據,只要內碼相同,則對應的內碼、編號、客戶、日期都顯示為空,該如何寫SQL語句,原SQL語句如下
select t1.內碼,t1.編號,t1.客戶,t1.日期,t2.出庫單
from SEOrder t1 join icstockbill t2 on t1.FInterID=t2.FInterID
danielinbiti 思路是可以的,但是結果不對把
既然人家內聯可以出所有結果, 表icstockbill和SEOrder都會有重復的內碼啊,稍微改下
select t2.出庫單,
case when t2.RN = 1 then t1.內碼 else '' end as 內碼,
case when t2.RN = 1 then t1.編號 else '' end as 編號,
case when t2.RN = 1 then t1.客戶 else '' end as 客戶,
case when t2.RN = 1 then t1.日期 else '' end as 日期
from
(select 出庫單, 內碼, ROW_NUMBER()OVER(PARTITION BY 內碼 ORDER BY 內碼) AS RN FROM icstockbill) t2
join
(select 內碼,編號,客戶,日期 FROM SEOrder) t1
on t2.FInterID=t1.FInterID