基于具有相同列名的两个表获取信息 [英] Getting information based on two tables with the same column names

问题描述

您好b $ b

从两张完全相同的列中检索信息时出现问题。



表格如下：

2009年

projNr |位置|金额

A.023东10000€

A.023东2000€

A.023东5000€

C.100东8000€

Z.253东25000€







2010年

projNr |位置|金额

A.023东500€

A.023东500€

C.100东1000€

Z.253 East 5000€





现在我想知道2009年和2010年的金额

基于projNr A.023或者可能是动态的。



这就是我所查询但它不断重复的结果：



选择a.proNr，a.Amount，a.location，b.Amount

从2009年开始作为一个

INNER JOIN 2010年AS b

ON a.projNr = b.projNr

WHERE a.projNr ='A.023'AND b.projNr ='A.023'



有人可以帮帮我吗？而不是5条记录，它重复记录直到12行

解决方案

HI，您可以尝试使用以下查询，

  SELECT   Distinct  a1.projNr 
 ，a1.location 
，a1.Amount 
  FROM  
（
  SELECT  projNr 
，location 
，Amount 
  FROM  [dbo] .Year2009 
 
  UNION  
  SELECT  projNr 
，location 
，Amount 
  FROM  [dbo] .Year2010 
）a1 
  WHERE  a1.projNr = '  A.023' 

这个查询的灵感来自之前的答案中的查询我已经做了几个mo像联盟一样，我使用了Union all

  SELECT  a1。 projNr 
，a1.location 
，a1.Amount 
  FROM  
（
  SELECT  projNr 
，location 
，Amount 
  FROM  table_2009 
 
  UNION   ALL  
  SELECT  projNr 
，location 
，Amount 
  FROM  table_2010 
）a1 
  WHERE  a1.projNr = '  A.023' 

您好试试这个查询



< pre lang =SQL> 选择 tb2.projNr，SUM（tb1.Year2009Amount + tb2.Year2010Amount） as 总计，tb2.location 来自
（选择 projNr， SUM（金额） as Year2009Amount，location from year2009 group by projNr，location）tb1

inner join

（ select projNr，SUM（金额） as Year2010Amount，位置来自 year2010 group by projNr，location）tb2

on tb1.projNr = tb2.projNr

group by tb2.projNr，tb2.location

或使用全部联盟



<前拉ng =SQL> 选择 tbl.projNr，tbl.location，Sum（tbl.Amount）为总计来自（选择 * 来自 year2009
union all
select * 来自 year2010）tbl group by tbl.projNr，tbl.location

Hi
Am getting problem retrieving information from two tables with exact the same colums.

The tables are:
Year 2009
projNr | location | Amount
A.023 East 10000€
A.023 East 2000€
A.023 East 5000€
C.100 East 8000€
Z.253 East 25000€



Year 2010
projNr | location | Amount
A.023 East 500€
A.023 East 500€
C.100 East 1000€
Z.253 East 5000€


Now i wanna find out the Amount from Year 2009 and 2010
based on projNr A.023 or maybe dynamically.

That what i query but it kept repeating the results:

SELECT a.proNr, a.Amount, a.location, b.Amount
FROM Year 2009 AS a
INNER JOIN Year 2010 AS b
ON a.projNr = b.projNr
WHERE a.projNr = 'A.023' AND b.projNr = 'A.023'

Can someone please help me? Instead of 5 records it repeating the records till 12 rows

解决方案

HI, can you please try with the below query,

SELECT Distinct a1.projNr
      ,a1.location
      ,a1.Amount
FROM
(
SELECT projNr
      ,location
      ,Amount
  FROM [dbo].Year2009

  UNION
SELECT projNr
      ,location
      ,Amount
  FROM [dbo].Year2010
  ) a1
  WHERE a1.projNr = 'A.023'

this query is inspired by the query in previous answer I have made few modification like instead of Union I used Union all

SELECT  a1.projNr
      ,a1.location
      ,a1.Amount
FROM
(
SELECT projNr
      ,location
      ,Amount
  FROM table_2009

  UNION ALL
SELECT projNr
      ,location
      ,Amount
  FROM table_2010
  ) a1
  WHERE a1.projNr = 'A.023'

Hi Try this Query

Select tb2.projNr,SUM(tb1.Year2009Amount+tb2.Year2010Amount) as Total,tb2.location from
(select projNr,SUM(Amount) as Year2009Amount,location from year2009 group by projNr,location)tb1

inner join 

(select projNr,SUM(Amount) as Year2010Amount,location from year2010 group by projNr,location)tb2 

on tb1.projNr=tb2.projNr 

group by tb2.projNr,tb2.location

or Use Union All

Select tbl.projNr,tbl.location,Sum(tbl.Amount) as Total from (select * from year2009
union all
select * from year2010)tbl group by  tbl.projNr,tbl.location

这篇关于基于具有相同列名的两个表获取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！