UNION的替代品？ [英] a substitute for UNION?
问题描述
我想更改查询：
SELECT DM。*，''condition1''，NULL FROM DM
WHERE（condition1）
UNION
SELECT DM。*，NULL，''condition2''FROM DM
WHERE（condition2）
>
到这样一个SELECT
SELECT DM。*，WasCondition1，WasCondition2 FROM DM
WHERE（condition1）或（condition2）
但是如何填写WasConditionX栏？
UNION版本不好，因为如果一行满足2
条件，它重复而不是加入它们，如下所示：
SELECT DM。*，''condition1''，''condition2''FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM。*，''condition1''，NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM。*，NULL，''condition2''FROM DM
WHERE NOT condition1 AND condition2
或（比如DM有列A，B，C）：
SELECT A，B，C，SUM（cond1），SUM（cond2）FROM
（
SELECT DM。*，1 AS cond1,0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM。*，0 AS cond1,1 AS cond2 FROM DM
WHERE condition2
）AS DM
GROUP BY A，B，C
2月19日，20：46，bbl ... @ op.pl写道：
我想更改查询：
SELECT DM。*，''condition1''，NULL FROM DM
WHERE（条件1）
UNION
SELECT DM。*，NULL，''condition2''FROM DM
哪里（条件2）
到这样一个SELECT
SELECT DM。*，WasCondition1，WasCondition2 FROM DM
WHERE（condition1）或（condition2）
但是如何填写WasConditionX列？
UNION版本不好因为以防万一一行满足2
条件，重复而不是加入它们，如下所示：
SELECT DM。*，''condition1''，' 'condition2''来自DM
WHERE condition1 AND condition2
UNION
SELECT DM。*，''condition1''，NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM。*，NULL，''condition2''FROM DM
WHERE NOT condition1 AND condition2
或（比如DM有列A，B，C）：
SELECT A，B，C， SUM（cond1），SUM（cond2）FROM
（
SELECT DM。*，1 AS cond1,0 AS cond2 FROM DM
WHERE条件1
UNION
SELECT DM。*，0 AS cond1,1 AS cond2 FROM DM
WHERE condition2
）AS DM
GROUP BY A，B，C
使用CASE表达式。为了适应你的第二个例子：
SELECT col1，col2，col3，
CASE WHEN（condition1）那么'''''''''''''' END AS WasCondition1，
CASE WHEN（condition2）那么'''''''''''''''''''''''''''''''''''''''''''$ $ $ $ $ $ $ $ br />
总是避免使用SELECT *。
-
David Portas，SQL Server MVP
请尽可能发布足够的代码来重现您的问题。
包含CREATE TABLE和INSERT语句通常会有所帮助。
说明您正在使用的SQL Server版本并指定任何错误消息的内容
。
SQL Server联机丛书：
http://msdn2.microsoft.com/library/m...S,SQL.90）。 aspx
-
使用CASE表达式。为了适应你的第二个例子：
>
SELECT col1，col2，col3，
CASE WHEN（ condition1）那么'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WHERE（condition1）OR（condition2）;
那么，每个条件都会被检查两次 - 性能
减少（条件使用子查询）？
始终避免使用SELECT *。
为什么？
我会告诉你我的查询里面没有想要出于某种原因编译
（关键字GROUP附近的语法不正确）：
DECLARE @pp INT
SET @pp = 7
SELECT SUM（已发送）AS已发送，SUM（已接收）AS已收到，[ID]
FROM（
SELECT 1 AS已发送，0 AS已收到，
DM。* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @ pp
UNION
SELECT 0 AS已发送，1 AS收到，
DM。*来自WSDMS .. DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @pp
UNION
SELECT 0 AS已发送，1 AS收到，
DM。* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
（SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.P ersonID = @pp AND PD.ObsoleteDate IS NULL）
AND DM.StationID IN
（SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL）
UNION
SELECT 0 AS已发送，1 AS已收到，
DM。* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
（SELECT PD.DepartmentID FROM CF ..PersonnelDepartment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL）
AND DM.InstructionNr IN
（SELECT I .InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @pp AND I.PUID IN
（从WSDMS中选择PU.ID PU，PU在哪里。 PersonID = @pp）
）
）
GROUP BY [ID]
I''d like to change query:
SELECT DM.*, ''condition1'', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, ''condition2'' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, ''condition1'', ''condition2'' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, ''condition1'', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, ''condition2'' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, C
On 19 Feb, 20:46, bbl...@op.pl wrote:I''d like to change query:
SELECT DM.*, ''condition1'', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, ''condition2'' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, ''condition1'', ''condition2'' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, ''condition1'', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, ''condition2'' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, CUse a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN ''Y'' ELSE ''N'' END AS WasCondition1,
CASE WHEN (condition2) THEN ''Y'' ELSE ''N'' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
Use a CASE expression. To adapt your second example:>
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN ''Y'' ELSE ''N'' END AS WasCondition1,
CASE WHEN (condition2) THEN ''Y'' ELSE ''N'' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?
Always avoid using SELECT *.Why?
I''ll show you my query which doesn''t want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @pp INT
SET @pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
)
)
GROUP BY [ID]
这篇关于UNION的替代品？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
