用UNION ALL命令(如何设置条件) [英] Command with UNION ALL (how to set condition)

问题描述

亲爱的所有人，
以下是我在外部查询中使用UNION All的SQL查询.所以我想选择特定日期以后的数据.可能吗?如果是，请回复.

Dear All,
Below is my SQL query in which I have used UNION All In my outer query. so I want to select data onwards particular date . Is it possible? if yes please reply.

[CODE]--------------------------------------------------------------------------------------------------
--KNOW-HOWS
-------------------
	SELECT KnowHowID AS Id,
			FirstName + '' ''+ LastName AS PostedBY,--PostedByID
			Title AS Title,
			ImagePath AS PhotoGraph,
			PostedDateTime AS DateTime,
			''knowHows'' AS FeatureName,
			'''' AS cnt
	FROM uvw_KnowHows 
	WHERE Active=1 AND PostedByID IN( --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
						 )
UNION All
---------------------------------------------------------------------------------------------------------------------
--NEWS-VIEWS
-------------------
	SELECT NewsViewsID AS Id,
			FirstName + '' ''+ LastName AS PostedBy,
			Title AS Title,
			ImagePath AS PhotoGraph,
			PostedDateTime AS DateTime, 
			''NewsViews'' AS FeatureName,
			'''' AS cnt 
	FROM uvw_NewsViews 
	WHERE Active=1 AND PostedByID IN(
						  --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
						) 
UNION All
---------------------------------------------------------------------------------------------------------------------
--PUBLIC IMAGE
-------------------
	SELECT PhotographID AS Id,
			FirstName + '' ''+ LastName AS PostedBy,
			Title AS Title,
			[Path] AS PhotoGraph,
			PostedDateTime AS DateTime,
			''PublicImage'' AS FeatureName ,
			'''' AS cnt
	FROM uvw_PublicAlbum  
	WHERE Active=1 AND UserID IN (
						 --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
					 )

推荐答案

当然，您可以将整个查询包装在另一个select语句中，然后过滤日期:

Sure, you can wrap your whole query above in another select statement and then filter on the date:

Select Id, PostedBy, Title, PhotoGraph, DateTime, FeatureName, cnt 
From 
( 
[your query here]
) 

where DateTime > ''1/1/2011''

这将更容易编写，但是，如果您将每个子选择限制在日期范围内，则可能会更有效.这取决于您的服务器如何优化查询.

This would be easier to write, however, it may be more efficient if you limited each sub select with the date range. This would depend on how your server optimizes the query.

这篇关于用UNION ALL命令(如何设置条件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！