Select语句具有可提供选择值的大小写-SQL Server [英] Select Statement has Case that provies select value - SQL Server

问题描述

我想从用户"表中选择UserType，UserID，FirstName，LastName，UserName.如果从照片"表中可以使用PhotoURL，则选择.如果记录不存在(用户类型-管理员仅在照片"表中有记录)，我应该发送空格.

I want to select UserType, UserID, FirstName, LastName, UserName from Users table. if PhotoURL is available from Photos table, I select. If record does not exists (UserType - admin only has record in Photos table), I should send spaces.

查询如下.如果您认为查询更好，请提出建议.

The query is below. If you think of better query, please suggest.

Select UserType, UserID, FirstName, LastName, UserName,
CASE 
    WHEN EXISTS(
        SELECT PhotoURL FROM Photos WHERE Photos.UserID = Users.UserID AND UserType = 'admin' AND Photos.PhotoNum = 1
    )
    THEN (
        SELECT PhotoURL FROM Photos WHERE Photos.UserID = Users.UserID AND UserType = 'admin' AND Photos.PhotoNum = 1
    ) 
    ELSE '' 
END AS PhotoURL
from Users

推荐答案

SELECT UserType, Users.UserID, FirstName, LastName, UserName, ISNULL(Photos.PhotoURL, '') as PhotoURL
FROM Users
LEFT JOIN Photos on Users.UserID = Photos.UserID 
    AND Photos.PhotoNum = 1
    AND Users.UserType = 'admin'

这篇关于Select语句具有可提供选择值的大小写-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！