UNION查询无法正常工作 [英] UNION Query is not working properly

问题描述

我正在使用两个表.我想使用union在两个表中求和.

当我执行查询时，出现错误.

请参阅我的表格并在下面查询.

表1

EmpNumber值WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
102 1 1 2012
102 1 2 2012
102 0 3 2012
103 1 1 2012
103 1 2 2012
103 0 3 2012

表2

EmpNumber值WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
201 1 1 2012
201 1 2 2012
201 0 3 2012
202 1 1 2012
202 1 2 2012
202 0 3 2012
203 1 1 2012
203 1 2 2012
203 0 3 2012

查询

I am using two table''s. i want to sum in both tables using union.

When i am executing the query i am getting error.

see my table and query below.

Table1

EmpNumber value WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
102 1 1 2012
102 1 2 2012
102 0 3 2012
103 1 1 2012
103 1 2 2012
103 0 3 2012

Table2

EmpNumber value WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
201 1 1 2012
201 1 2 2012
201 0 3 2012
202 1 1 2012
202 1 2 2012
202 0 3 2012
203 1 1 2012
203 1 2 2012
203 0 3 2012

Query

Select sum(val) as Val from 
(select * from Table2 T1 inner join project P on T1.projectcode = P.Projectno 
where T1.WeekPeriod =16 and T1.YearPeriod =2012 and T1.Emp_Number ='101'  
union
select * from Table2 T2 inner join project P on T2.projectcode = P.Projectno 
where T2.WeekPeriod =16 and T2.YearPeriod =2012 and T2.Emp_Number ='101'  ) Val

推荐答案

尝试使用表别名将查询中的"*"替换为特定的列名.如下所示

Hi,
Try by replacing " * " in your query to the specific column names using the table aliases. as like below

Select sum(value) as Val from
(select T1.value from Table2 T1 inner join project P on T1.projectcode = P.Projectno
where T1.WeekPeriod =16 and T1.YearPeriod =2012 and T1.Emp_Number ='101'
union
select T2.value from Table2 T2 inner join project P on T2.projectcode = P.Projectno
where T2.WeekPeriod =16 and T2.YearPeriod =2012 and T2.Emp_Number ='101'  )

希望它能起作用.

Hope it works.

您的查询是正确的.您可能使用了错误的列名，请检查.
如果仍然存在问题，则也发布您的表结构.我创建了一个示例查询，供您参考

Your query is correct. You might have used wrong column name, please check.
If still problem persist post your table structure too. I have created a sample query use this for your reference

DECLARE @Table1 TABLE (EmpNumber VARCHAR(20), value INT, WeekPeriod INT, YearPeriod INT)
INSERT INTO @Table1 VALUES 
('101',1,1,2012),('101',1,2,2012),('101',0,3,2012),
('102',1,1,2012),('102',1,2,2012),('102',0,3,2012),
('103',1,1,2012),('103',1,2,2012),('103',0,3,2012)

DECLARE @Table2 TABLE (EmpNumber VARCHAR(20), value INT, WeekPeriod INT, YearPeriod INT)
INSERT INTO @Table1 VALUES 
('101',1,1,2012),('101',1,2,2012),('101',0,3,2012),
('201',1,1,2012),('201',1,2,2012),('201',0,3,2012),
('202',1,1,2012),('202',1,2,2012),('202',0,3,2012),
('203',1,1,2012),('203',1,2,2012),('203',0,3,2012)

SELECT 
	SUM(ISNULL(value,0)) AS Val 
FROM 
	(
		SELECT 
			* 
		FROM 
			@Table1 T1  
		WHERE 
			T1.EmpNumber ='101'  
		UNION
		SELECT 
			* 
		FROM 
			@Table2 T2  
		WHERE 
			T2.EmpNumber = '101'  
	) Val

问候
Praveen

Regards
Praveen

这篇关于UNION查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！