为什么我得到0行，当我加入多个表与个人id?

Question

我有一个表，其中匹配的衣服类型和一个所有者的模型名称。这个表格是我用的软件给的，我不能改变我的模型设计。

这个衣柜的主人是Paul，他的个人ID是poa

<表类> ID owner_id cid 名称 类型 Date_own_from Date_own_to tbody><<tr>1《行动纲领》sho-1龙鞋子01-01-202101-10-20212《行动纲领》shi-1SimpleT衬衫01-01-202131-12-20993《行动纲领》21白色模型套接字01-01-202131-12-20994《行动纲领》sho-3蛇速度3鞋子01-10-202131-12-2099

给穷人设计与你斗争。

这里是示例数据和ddl，以防有人想玩。

create table Wardrobe
(
ID int
, owner_id varchar(10)
, cid varchar(10)
, Name varchar(20)
, Type varchar(10)
)
insert Wardrobe
select 1, 'poa', 'sho-1', 'SnakeSpeed', 'Shoes' union all
select 2, 'poa', 'shi-1', 'SimpleT', 'Shirt' union all
select 3, 'poa', 'so-21', 'White model', 'Sockets'
insert Wardrobe
select 4, 'poa', 'so-22', 'green', 'Sockets'
create table Shoes
(
ID int
, Clothes_ID varchar(10)
, Size varchar(10)
)
insert Shoes
select 1, 'sho-1', '42'
create table Shirts
(
ID int
, Clothes_ID varchar(10)
, Size varchar(10)
)
insert Shirts
select 1, 'shi-1', 'M'
create table Sockets
(
ID int
, Clothes_ID varchar(10)
, Size varchar(10)
)
insert Sockets
select 2, 'so-22', 'M'

可能有一些其他的方法来处理这个问题。但即使一个人只有两只鞋和一件没有插座的衬衫，这个例子也适用。我首先使用一些ctes和row_number隔离每组行。然后用一些条件聚合将它们重新组合在一起。

declare @Owner varchar(10) = 'poa';
with cteShoes as
(
select w.ID
, w.owner_id
, w.Name
, sho.Size
, RowNum = ROW_NUMBER() over(order by w.Name)
from Wardrobe w     
join Shoes sho on sho.Clothes_ID = w.cid and w.Type = 'Shoes'
where w.owner_id = @Owner
)
, cteShirts as
(
select w.ID
, w.owner_id
, w.Name
, shi.Size
, RowNum = ROW_NUMBER() over(order by w.Name)
from Wardrobe w 
join Shirts shi on shi.Clothes_ID = w.cid and w.Type = 'Shirt'
where w.owner_id = @Owner
)
, cteSockets as
(
select w.ID
, w.owner_id
, w.Name
, soc.Size
, RowNum = ROW_NUMBER() over(order by w.Name)
from Wardrobe w 
join Sockets soc on soc.Clothes_ID = w.cid and w.Type = 'Sockets'
where w.owner_id = @Owner
)
select Owner = coalesce(sho.owner_id, shi.owner_id, soc.owner_id)
, Shoes = max(case when sho.RowNum is not null then sho.Name end)
, ShoesSize = max(case when sho.RowNum is not null then sho.Size end)
, Shirt = max(case when shi.RowNum is not null then shi.Name end)
, ShirtSize = max(case when shi.RowNum is not null then shi.Size end)
, Sockets = max(case when soc.RowNum is not null then soc.Name end)
, SocketsSize = max(case when soc.RowNum is not null then soc.Size end)
from cteShoes sho
full outer join cteShirts shi on shi.RowNum = sho.RowNum
full outer join cteSockets soc on soc.RowNum = sho.RowNum
group by coalesce(sho.ID, shi.ID, soc.ID)
, coalesce(sho.owner_id, shi.owner_id, soc.owner_id)

使用左连接，因为这个用户没有t

SELECT w.*, shoes.*, shirt.*
FROM (SELECT distinct cid, owner_id FROM wardrobe) w
LEFT JOIN (SELECT cid, clothes_id
FROM wardrobe WHERE type ='Shoes') shoelink ON shoelink.cid = w.cid
LEFT JOIN shoes sho on sho.clothes_id = shoelink.cid 
LEFT JOIN (SELECT cid, clothes_id
FROM wardrobe WHERE type ='Shirt') shirtlink ON shirtlink.cid = w.cid
LEFT JOIN shirt shi on shi.clothes_id = shirtlink.cid
where w.owner_id = 'poa'

我不知道为什么你需要加入任何表，因为他们没有添加任何到你的结果-除非你过度简化。

根据你的结果可以这样做。

但是，如果一个所有者有不止一双鞋等，你需要告诉我们你希望在你的结果中看到什么。

SELECT
owner_id AS owner,
MAX(CASE WHEN type = 'shoes' THEN name END) AS shoes,
MAX(CASE WHEN type = 'shirt' THEN name END) AS shirt,
MAX(CASE WHEN type = 'sockets' THEN name END) AS sockets
FROM w
GROUP BY owner_id