当我在sqlserver中使用联接查询时，输出中的数据重复 [英] Data repetation in my output when I used join query in sqlserver

问题描述

问题:连接来自3个表的多列的值并显示在一列中.日期字段是datetime.now我的问题是，如果日期是重复一张表，那么amt2，amt3也将在同一日期重复
table1:

Question: Concatenate values from multiple columns of 3 tables and display in one column. Date field is datetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date
table1:

date        amt1
-----------------
1-1-2016    111
1-1-2016    222    repeat date
3-4-2016    111

table2:

table2:

date        amt2
-----------------
1-1-2016    101 
2-2-2016    333
2-3-2016    444
3-3-2016    456
1-4-2016    101     
3-4-2016    111     

table3:

table3:

date        amt3
-----------------
2-2-2016    001
2-3-2016    002 
3-3-2016    003
1-4-2016    555
2-4-2016    666
3-4-2016    777

查询

query

SELECT 
    COALESCE(t1.date, t2.date, t3.date)
  , t1.amount
  , t2.cashamount
  , t3.grandtotal
FROM tb_amount t1
FULL JOIN tb_cashamt t2 ON t1.date =t2.date 
FULL JOIN tb_grandtotal  t3 ON t2.date = t3.date

输出:

output:

date        amt1     amt2    amt3
----------------------------------
1-1-2016    111     101      NULL data repeat on amt2 and amt3
1-1-2016    222     101      null 
2-3-2016    NULL    444      002 
3-3-2016    NULL    456      003

------------------------------------
TOTAL       ---    ---     ----- 

所需输出:

Desired output:

date        amt1     amt2    amt3
----------------------------------
1-1-2016    333     101      NULL 
2-3-2016    NULL    444      002 
3-3-2016    NULL    456      003

------------------------------------
TOTAL       ---    ---     -----

我尝试过的事情:

连接来自3个表的多列的值，并在一列中显示.日期字段是smalldatetime.现在我的问题是，如果日期是重复一张表，那么amt2，amt3也在同一日期重复.我根据日期解释在同一日期总计amt1，amt2，amt3显示的期望输出.

What I have tried:

Concatenate values from multiple columns of 3 tables and display in one column. Date field is smalldatetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date.i explain desired output in the same date total amt1,amt2,amt3 display according to date.

推荐答案

一种选择是使用通用表表达式:

One option is to use Common Table Expressions:

WITH cte1 AS
(
  SELECT [date]
  , SUM([amt1]) [amt1]
  FROM #table1
  GROUP BY [date]
)
, cte2 AS
(
  SELECT [date]
  , SUM([amt2]) [amt2]
  FROM #table2
  GROUP BY [date]
)
, cte3 AS
(
  SELECT [date]
  , SUM([amt3]) [amt3]
  FROM #table3
  GROUP BY [date]
)
SELECT 
    COALESCE(t1.[date], t2.[date], t3.[date]) [date]
  , t1.[amt1]
  , t2.[amt2]
  , t3.[amt3]
FROM cte1 t1
FULL JOIN #table2 t2 ON t1.[date] = t2.[date]
FULL JOIN #table3 t3 ON t2.[date] = t3.[date]

SQL SERVER 2008中的公用表表达式(CTE) [ ^ ]

使用公用表表达式 [

Common Table Expressions(CTE) in SQL SERVER 2008[^]

Using Common Table Expressions[^]

这篇关于当我在sqlserver中使用联接查询时，输出中的数据重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！