SQL 选择返回默认值如果为空

SQL Select Return Default Value If Null

提问by Landmine

Database: MS SQL 2008

数据库：MS SQL 2008

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, Pictures.Picture, Listing.ID 
FROM Listing INNER JOIN Pictures ON Listing.ID = Pictures.ListingID
WHERE (Pictures.ID = (SELECT MIN(ID) FROM Pictures WHERE (ListingID = Listing.ID)))

The issue is, I have several "Listings" without a Picture, and because of this SQL script they don't show up. How can I get them to show up?

问题是，我有几个没有图片的“列表”，并且由于这个 SQL 脚本，它们没有出现。我怎样才能让他们出现？

Maybe give the Pictures.Picture Column a value of "default.jpg" if the value is null? I'm pretty lost on this, so if someone could help, that'd be amazing. Sorry if I'm asking the question poorly as well, I dont understand how to ask really what I need it to do. But ask for more details and I'll post them.

如果值为空，也许给 Pictures.Picture Column 一个值“default.jpg”？我对此很迷茫，所以如果有人可以提供帮助，那就太棒了。对不起，如果我问的问题也很糟糕，我不明白如何真正问我需要它做什么。但是询问更多详细信息，我会发布它们。

Each Listing can have as many pictures as the user wants, I need this script to display a Listing even if it doesn't have a picture.

每个列表可以有用户想要的任意数量的图片，我需要这个脚本来显示列表，即使它没有图片。

---

---

PHASE 2

阶段2

---

---

Thank you all. So far I'm learning some new commands I never even knew existed. The issue now is its returning a row for each picture a listing has. But the default image is working great.

谢谢你们。到目前为止，我正在学习一些我什至不知道存在的新命令。现在的问题是它为列表中的每张图片返回一行。但是默认图像效果很好。

SELECT Listing.Title, Listing.MLS, coalesce(Pictures.PictureTH, '../default_th.jpg') as PictureTH, coalesce(Pictures.Picture, '../default.jpg') as Picture, Listing.ID FROM Listing LEFT
OUTER JOIN Pictures ON Listing.ID = Pictures.ListingID

How can I get it so it only returns 1 row per ListingID ?

我怎样才能得到它，以便每个 ListingID 只返回 1 行？

回答by Shannon Severance

Two things:

两件事情：

1. Use left outer joininstead of inner jointo get all the listings, even with missing pictures.

2. Use coalesceto apply the default

   SELECT Listing.Title
       , Listing.MLS
       , Pictures.PictureTH
       , coalesce(Pictures.Picture, 'default.jpg') as Picture
       , Listing.ID  
   FROM Listing 
   LEFT OUTER JOIN Pictures 
       ON Listing.ID = Pictures.ListingID 
   

1. 使用left outer join而不是inner join获取所有列表，即使缺少图片。

2. 使用coalesce应用默认

   SELECT Listing.Title
       , Listing.MLS
       , Pictures.PictureTH
       , coalesce(Pictures.Picture, 'default.jpg') as Picture
       , Listing.ID  
   FROM Listing 
   LEFT OUTER JOIN Pictures 
       ON Listing.ID = Pictures.ListingID 
   

EDITTo limit to one row:

编辑限制为一行：

SELECT Listing.Title
    , Listing.MLS
    , Pictures.PictureTH
    , coalesce(Pictures.Picture, 'default.jpg') as Picture
    , Listing.ID  
FROM Listing 
LEFT OUTER JOIN Pictures 
    ON Listing.ID = Pictures.ListingID 
WHERE Pictures.ID is null
OR Pictures.ID = (SELECT MIN(ID) 
    FROM Pictures 
    WHERE (ListingID = Listing.ID))) 

回答by Vijjendra

if you want to set the default value if the Pic is null you can do this via COALESCE key word:

如果您想在 Pic 为空时设置默认值，您可以通过 COALESCE 关键字执行此操作：

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, 
COALESCE (Pictures.Picture, 'default.jpg') AS Pictures, Listing.ID 
FROM Listing LEFT JOIN Pictures 
ON Listing.ID = Pictures.ListingID
WHERE (Pictures.ID = (SELECT MIN(ID) 
FROM Pictures WHERE (ListingID = Listing.ID)))

You can also achieve this via IsNull like below:

您还可以通过 IsNull 实现这一点，如下所示：

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, 
ISNULL(Pictures.Picture, 'default.jpg') AS Pictures, Listing.ID 
FROM Listing LEFT JOIN Pictures 
ON Listing.ID = Pictures.ListingID
WHERE (Pictures.ID = (SELECT MIN(ID) 
FROM Pictures WHERE (ListingID = Listing.ID)))

you can read hereabout IsNull and Coalesce

您可以在此处阅读有关 IsNull 和 Coalesce 的信息

回答by Gabriele Petrioli

Use left outer joininstead of inner join

使用left outer join代替inner join

Inner joinwill return results if and only if there is a result that satisfies the join.

Inner join当且仅当存在满足连接的结果时才会返回结果。

Left outer joinwill return results from the leftside table, and if the join is satisfied also add results from the rightside table..

Left outer join将返回左侧表的结果，如果满足连接，还添加右侧表的结果。

If you need to convert the null values returned from the non-satisfyingjoins, then use coalescefunction like coalesce(Pictures.Picture, 'default.jpg')

如果您需要转换从非满足连接返回的空值，则使用coalesce类似的函数coalesce(Pictures.Picture, 'default.jpg')

回答by ozczecho

Need to do a LEFT join

需要做一个LEFT join

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, Pictures.Picture, Listing.ID 
FROM Listing LEFT JOIN Pictures ON Listing.ID = Pictures.ListingID

回答by clyc

Landmine, what database are you using?

地雷，你用的是什么数据库？

If it's sql server 2005 or above or oracle, you can use the pivot command to achieve this.

如果是sql server 2005以上或者oracle，可以使用pivot命令来实现。