create table #a
(
ID int,
日期 datetime,
費用專案 varchar(50),
費用金額 numeric(10,2),
)
insert #a
select '1','2020-09-20','差旅費','200'
union
select '1','2020-09-20','辦公費','300'
union
select '2','2020-09-20','水電費','200'
union
select '2','2020-09-20','租金','500'
union
select '3','2020-09-20','工資','1000'
create table #b
(
ID int,
日期 datetime,
付款賬號 varchar(50),
付款金額 numeric(10,2),
)
insert #b
select '1','2020-09-20','現金','100'
union
select '1','2020-09-20','微信','400'
union
select '2','2020-09-20','現金','700'
union
select '3','2020-09-20','微信','500'
union
select '3','2020-09-20','支付寶','500'
select * from #a
select * from #b
drop table #a
drop table #b
怎么查詢變成這個樣子？用join資料重復了
uj5u.com熱心網友回復：
SELECT a.日期,a.費用專案,a.費用金額,b.付款賬號,b.付款金額 FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS x
FROM #a
) a
FULL JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS y
FROM #b
) b ON b.ID = a.ID AND b.日期 = a.日期 AND x=y
uj5u.com熱心網友回復：
create table #a
(
ID int,
日期 datetime,
費用專案 varchar(50),
費用金額 numeric(10,2),
)
insert #a
select '1','2020-09-20','差旅費','200'
union
select '1','2020-09-20','辦公費','300'
union
select '2','2020-09-20','水電費','200'
union
select '2','2020-09-20','租金','500'
union
select '3','2020-09-20','工資','1000'
create table #b
(
ID int,
日期 datetime,
付款賬號 varchar(50),
付款金額 numeric(10,2),
)
insert #b
select '1','2020-09-20','現金','100'
union
select '1','2020-09-20','微信','300'
union
select '1','2020-09-20','支付寶','100'
union
select '2','2020-09-20','現金','700'
union
select '3','2020-09-20','微信','500'
union
select '3','2020-09-20','支付寶','500'
--select * from #a
--select * from #b
SELECT a.ID,a.日期,a.費用專案,a.費用金額,b.付款賬號,b.付款金額 FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS x
FROM #a
) a
FULL JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS y
FROM #b
) b ON b.ID = a.ID AND b.日期 = a.日期 AND x=y
drop table #a
drop table #b
怎么只是加一行資料就亂了
uj5u.com熱心網友回復：
--拍個序就好了
SELECT a.ID,a.日期,a.費用專案,a.費用金額,b.付款賬號,b.付款金額 FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS x
FROM #a
) a
FULL JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 日期,id ORDER BY GETDATE()) AS y
FROM #b
) b ON b.ID = a.ID AND b.日期 = a.日期 AND x=y
ORDER BY ISNULL(a.id,b.id)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/213112.html
標籤：基礎類
上一篇：MySQL查看連接埠