SQL选择返回默认值，如果为空 [英] SQL Select Return Default Value If Null

问题描述

数据库: 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)))

问题是，我有几个没有图片的清单"，由于这个SQL脚本，它们没有显示.如何让他们出现?

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?

如果值为null，可以为Pictures.Picture列提供一个值"default.jpg"吗?我对此非常迷失，因此，如果有人可以提供帮助，那就太好了.抱歉，如果我也没有很好地问这个问题，我不明白如何问我真正需要做的事情.但请提供更多详细信息，我将其发布.

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.

每个清单可以包含用户想要的图片，即使没有图片，我也需要此脚本来显示清单.

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.

第二阶段

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

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

如何获取它，以便每个ListingID仅返回1行?

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

推荐答案

两件事:

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

2. 使用coalesce来应用默认值

1. Use left outer join instead of inner join to get all the listings, even with missing pictures.

2. Use coalesce to 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 

编辑:限制为一行:

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))) 

这篇关于SQL选择返回默认值，如果为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！