我该如何编写类似SQL的全连接操作,用于以下签名的列表?
fullJoin :: [a] -> [b] -> (a -> b -> Bool) -> [(Maybe a, Maybe b)]
fullJoin xs [] _ = map (\x -> (Just x, Nothing)) xs
fullJoin [] ys _ = map (\y -> (Nothing, Just y)) ys
fullJoin xs@(xh:xt) ys@(yh:yt) on =
if on xh yh
then (Just xh, Just yh) : fullJoin xt yt
else ???
这个条件是由
a -> b -> Bool
提供的。在这个例子中,它被设置为 (\n i -> length n == i)
,意思是从 names
中选择与 nums
中数字相同长度的记录。示例:
names = ["Foo","Bar", "John" , "Emily", "Connor"]
nums = [1,2,3,4,5]
fullJoin names nums (\n i -> length n == i)
== [ (Just "Foo", Just 3)
, (Just "Bar", Just 3)
, (Just "John", Just 4)
, (Just "Emily", Just 5)
, (Just "Connor", Nothing)
, (Nothing, Just 1)
, (Nothing, Just 2)
]
为了明确该函数的SQL等效写法,以下是在PostgreSQL中编写的方法:
create table names as
select * from (values
('Foo'),
('Bar'),
('John'),
('Emily'),
('Connor')
)
as z(name);
create table nums as
select * from (values
(1),
(2),
(3),
(4),
(5)
)
as z(num);
select * from names
full join nums
on char_length(name) = num
运行此命令将产生以下结果:
name num
(null) 1
(null) 2
Foo 3
Bar 3
John 4
Emily 5
Connor (null)
Fiddle链接:sqlfiddle。