仅在存在相关数据时选择数据 [英] Selecting data only if related data exists
问题描述
我有以下查询可以在多条记录上返回批量存在查询。我想要做的只是选择u.role和其他几个字段,如果用户名存在于'users'中。这个方法有效,但是它很麻烦,因为我正在检查我选择的每个列是否存在用户名。是否有更紧凑的说法;
如果用户名存在,则返回角色,x,y,z列等。
否则,为角色,x,y,z列等返回NULL。
CREATE TEMPORARY TABLE _users(username varchar(256));
INSERT INTO _users VALUES('testuser1','testuser2');
SELECT _u.username,
CASE
WHEN ISNULL(u.username)= 1那么0
ELSE 1
END'存在',
CASE
WHEN ISNULL(u.username)= 1那么你是
ELSE NULL
结束'角色'
来自_users _u
LEFT JOIN用户u
ON u.username = _u.username;
DROP TABLE _users
我尝试过:
------------------------------------------ -------------------------------------
我建议你阅读: EXISTS(Transact-SQL) [ ^ ]
如果您愿意要返回NULL
,你可以使用这个技巧:
SELECT TOP ( 1 )Field1,Field2,Field3
FROM YourTable
WHERE UserName = ' 无论'
UNION ALL
SELECT NULL AS Field1, NULL AS Field2, NULL 作为 Field3
但是 - 我不建议做出这样的事情有几个原因...
这是一个'工作'的例子:
DECLARE @ t 表(uName VARCHAR ( 30 ))
INSERT INTO @ t (uName)
VALUES (' A'),(' B'),(' C')
SELECT TOP ( 1 )uName
FROM @ t
WHERE uName = ' D'
UNION ALL
SELECT NULL AS uName
< span class =code-comment> - 返回:NULL
>
根据我们的讨论,LEFT JOIN
正在做你期望实现的目标。请参阅:
DECLARE @tmp TABLE(uName VARCHAR(30))
INSERT INTO @tmp(uName)
VALUES('A'),( 'B'),('C')
DECLARE @orig TABLE(uName VARCHAR(30),[role] VARCHAR(30))
INSERT INTO @orig( uName,[role])
VALUES('A','admin'),('D','user'),('C','user')
SELECT t .uName AS TmpName,o。*
FROM @tmp t LEFT JOIN @orig o ON t.uName = o.uName
结果:
TmpName uName角色
A A admin
B NULL NULL
C C用户
有关详细信息,请参阅: SQL的可视化表示加入 [ ^ 一>
Hi,
I have the following query to return a bulk 'exists' query on multiple records. What I'm looking to do is only to select u.role and several other fields if the username exists in 'users'. This method works, but it's messy as I'm checking if the username exists for every column I'm selecting. Is there a more compact way to say;
If the username exists, return the role, x, y, z column etc.
Otherwise, return NULL for the role, x, y, z column etc.
CREATE TEMPORARY TABLE _users(username varchar(256)); INSERT INTO _users VALUES('testuser1', 'testuser2'); SELECT _u.username, CASE WHEN ISNULL(u.username) = 1 THEN 0 ELSE 1 END 'exists', CASE WHEN ISNULL(u.username) = 1 THEN u.role ELSE NULL END 'role' FROM _users _u LEFT JOIN users u ON u.username = _u.username; DROP TABLE _users
What I have tried:
-------------------------------------------------------------------------------
I'd recommend to read this: EXISTS (Transact-SQL)[^]
[EDIT]
If you would like to returnNULL
, you can use this trick:
SELECT TOP(1) Field1, Field2, Field3 FROM YourTable WHERE UserName = 'Whatever' UNION ALL SELECT NULL AS Field1, NULL AS Field2, NULL As Field3
But - i do NOT recommend to do such a thing for several reason...
Here's a 'working' example:
DECLARE @t TABLE (uName VARCHAR(30)) INSERT INTO @t (uName) VALUES('A'), ('B'), ('C') SELECT TOP(1) uName FROM @t WHERE uName = 'D' UNION ALL SELECT NULL AS uName --returns: NULL
[EDIT 2]
According to our discussion, aLEFT JOIN
is doing exactly what you do expect to achieve. See:
DECLARE @tmp TABLE (uName VARCHAR(30)) INSERT INTO @tmp (uName) VALUES('A'), ('B'), ('C') DECLARE @orig TABLE (uName VARCHAR(30), [role] VARCHAR(30)) INSERT INTO @orig (uName, [role]) VALUES('A', 'admin'), ('D', 'user'), ('C', 'user') SELECT t.uName AS TmpName, o.* FROM @tmp t LEFT JOIN @orig o ON t.uName = o.uName
Result:
TmpName uName role A A admin B NULL NULL C C user
For further details, please see: Visual Representation of SQL Joins[^]
这篇关于仅在存在相关数据时选择数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!