UNION的替代品？

或（比如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 DMWHERE (condition1)UNIONSELECT DM.*, NULL, ''condition2'' FROM DMWHERE (condition2) to one SELECT like this SELECT DM.*, WasCondition1, WasCondition2 FROM DMWHERE (condition1) or (condition2) but how to fill in the WasConditionX column?The UNION version was bad because in case a row fulfilled 2conditions, it was repeated instead of joining them, such like this: SELECT DM.*, ''condition1'', ''condition2'' FROM DMWHERE condition1 AND condition2UNIONSELECT DM.*, ''condition1'', NULL FROM DMWHERE condition1 AND NOT condition2UNIONSELECT DM.*, NULL, ''condition2'' FROM DMWHERE 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 DMWHERE condition1UNIONSELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DMWHERE condition2) AS DMGROUP BY A, B, C 解决方案 On 19 Feb, 20:46, [email protected] wrote:I''d like to change query:SELECT DM.*, ''condition1'', NULL FROM DMWHERE (condition1)UNIONSELECT DM.*, NULL, ''condition2'' FROM DMWHERE (condition2)to one SELECT like thisSELECT DM.*, WasCondition1, WasCondition2 FROM DMWHERE (condition1) or (condition2)but how to fill in the WasConditionX column?The UNION version was bad because in case a row fulfilled 2conditions, it was repeated instead of joining them, such like this:SELECT DM.*, ''condition1'', ''condition2'' FROM DMWHERE condition1 AND condition2UNIONSELECT DM.*, ''condition1'', NULL FROM DMWHERE condition1 AND NOT condition2UNIONSELECT DM.*, NULL, ''condition2'' FROM DMWHERE NOT condition1 AND condition2or (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 DMWHERE condition1UNIONSELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DMWHERE condition2) AS DMGROUP 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 WasCondition2WHERE (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 contentof 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 WasCondition2WHERE (condition1) OR (condition2) ; Well, then each condition will be checked twice -- performancedecreases (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 INTSET @pp = 7SELECT 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 NULLAND DM.SenderID = @pp UNION SELECT 0 AS Sent, 1 AS Received,DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULLAND DM.ReceiverPersonID = @pp UNION SELECT 0 AS Sent, 1 AS Received,DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULLAND DM.ReceiverDepartmentID IN(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PDWHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)AND DM.StationID IN(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PSWHERE 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 NULLAND DM.ReceiverDepartmentID IN(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PDWHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)AND DM.InstructionNr IN(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest IWHERE I.PUID = @pp AND I.PUID IN(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)) ) GROUP BY [ID] 这篇关于UNION的替代品？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！