如何获得所有差异 [英] How to get all differences

问题描述

嗨我有这张桌子，我想得到所有不同的东西。我已经尝试了 Excepts，Not in，Exists 等，但我得到的正确数字与excel一样。

 ProjectNr Ist Value Soll Value 
 D.01025 0 19010 
 D.01048 0 0 
 D.01620 0 409196 
 G.01013 20766,31575 20406,21832 
 G.10002 3409,73749 3707,07765 
 G.10004 8052,97646 8057,03358 
 G .01006 1,50104 1,50104 
 G.00960 585385,8582 543435,6963 
 G.01961 11000 0 
 G.01003 2210,34804 2184,59583 
 G.01004 637,73126 637,38993 
 G.01005 1557,47348 1424,94381 
 G.01113 745,29877 745,93531 
 G.01043 29285,59879 28600,87531 
 G .01162 23226 0 
 G.01069 72765,80058 73450,86162 

我想这样做：



1.什么价值不是Ist值（Soll不在Ist）

2.Ist值不是Soll值（Ist）不在Soll中）

3.什么是soll值Ist（Soll in Ist）

4. Soll的价值是什么（Ist in Soll）



专业解决方案：

 Soll not in Ist Ist not in Soll Soll in Ist in Soll 
 19.010,00  -   -   -  
  -   -   - 
 409.196,00  -   -   -  
  -  20.406,22 20.766,32 
  -  3.707,08 3.409,74 
  -  8.057,03 8.052,98 
  -  1， 50 1,50 
  -  543.435,70 585.385,86 
 11.000,00  -   -  
  -  2.184,60 2.210,35 
  -  637,39 637,73 
 -  1.424,94 1.557,47 
  -  745,94 745,30 
  -  28.600,88 29.285,60 
 23.226,00  -   -  
  -  73.450,86 72.765,80 

  1 。 
  SELECT  [Soll_value]  AS  Soll 
  FROM  [dbo]。[表1] 
  WHERE   NOT   IN （ SELECT  [Ist_value]  AS  Ist  FROM  [dbo]。[Table1] 
 
  2 。
  SELECT  [Ist_value]  AS  Ist 
  FROM  [dbo]。[表1] 
  WHERE   NOT   IN （ SELECT  [Soll_value]  AS  Ist  FROM  [dbo]。[表1] 
 
  2 （替代）。
  SEL ECT  [Soll_value]  AS  Soll 
  FROM  [dbo]。[表1] 
  EXCEPT  
  SELECT  [Ist value]  AS  Ist 
  FROM  [dbo]。[Table1] 
 
  3 。 
  SELECT  [Soll_value]  AS  Soll 
  FROM  [dbo]。[表1] 
  WHERE   EXISTS   IN （ SELECT  [Ist_value]  AS  Ist  FROM  [dbo]。[Table1] 
 
  4 。
  SELECT  [Ist_value]  AS  Ist 
  FROM  [dbo]。[表1] 
  WHERE   EXISTS   IN （ SELECT  [Soll_value]  AS  Ist  FROM  [dbo]。[表1] 

解决方案

希望下面的查询会有所帮助哟u。

 [ 1 ] 
 选择 t1.soll_value 
 来自 table1 t1  left  外部  join  table1 t2 
  on  t1.soll_id = t2。 lst_value 
 其中 t2.lst_value    null < /跨度>; 
 
 [ 2 ] 
 选择 t1.lst_value 
 来自 table1 t1  left   outer   join  table1 t2 
  on  t1.lst_value = t2.soll_value 
  where  t2.soll_value    null ; 
 
 [ 3 ] 
 选择 t1.soll_value 
 来自 table1 t1  left   outer   join  table1 t2 
  on  t1.soll_value = t2.lst_value 
  where  t2.lst_value   不 空; 
 
 [ 4 ] 
  select  t1.lst_value 
 来自 table1 t1  left   outer   join  table1 t2 
  on  t1.lst_value = t2.soll_value 
  where  t2.soll_value    not  空; 

Hi i have this one table and i wanna get all the differenves. I have tried Excepts, Not in, Exists etc but i'ant getting the right figures like with excel.

ProjectNr	Ist Value	Soll Value
D.01025	          0	          19010
D.01048           0	          0
D.01620	          0	          409196
G.01013	       20766,31575	  20406,21832
G.10002	       3409,73749	  3707,07765
G.10004	       8052,97646	  8057,03358
G.01006	       1,50104	          1,50104
G.00960	       585385,8582	  543435,6963
G.01961	       11000	          0
G.01003	       2210,34804	  2184,59583
G.01004	       637,73126	  637,38993
G.01005	      1557,47348	  1424,94381
G.01113	       745,29877	  745,93531
G.01043	      29285,59879	 28600,87531
G.01162	      23226	          0
G.01069	     72765,80058	73450,86162

I wanna do something like this:

1.What soll-values are not in Ist value (Soll not in Ist)
2.What Ist values are not in Soll values (Ist not in Soll)
3.What soll values are in Ist (Soll in Ist)
4.What Ist values are in Soll (Ist in Soll)

Experted Solution:

Soll not in Ist	 Ist not in Soll 	 Soll in Ist 	 Ist in Soll 
      19.010,00   		-	               -            -
		                -     	               -            -
      409.196,00   		-	               -             -     
		                -                 20.406,22   	    20.766,32   
		                -                  3.707,08   	     3.409,74   
		                -                  8.057,03   	     8.052,98   
		                -                  1,50   	     1,50   
		                -                 543.435,70   	     585.385,86   
	                   11.000,00   	             -     	      -
		              -                    2.184,60   	     2.210,35   
		              -                    637,39   	     637,73   
		              -                    1.424,94   	     1.557,47   
		              -                    745,94   	     745,30   
		              -                    28.600,88   	     29.285,60   
	                   23.226,00   	              -     	       -
		              -                   73.450,86   	      72.765,80   

1.
SELECT [Soll_value] AS Soll
	FROM [dbo].[Table1]
WHERE NOT IN (SELECT [Ist_value] AS Ist FROM [dbo].[Table1]

2.
SELECT [Ist_value] AS Ist
	FROM [dbo].[Table1]
WHERE NOT IN (SELECT [Soll_value] AS Ist FROM [dbo].[Table1]

2(alternative).
	SELECT  [Soll_value] AS Soll
	FROM [dbo].[Table1]
EXCEPT
	SELECT [Ist value] AS Ist
	FROM [dbo].[Table1]
 
3.	
SELECT [Soll_value] AS Soll
	FROM [dbo].[Table1]
WHERE EXISTS IN (SELECT [Ist_value] AS Ist FROM [dbo].[Table1]

4.
SELECT [Ist_value] AS Ist
	FROM [dbo].[Table1]
WHERE EXISTS IN (SELECT [Soll_value] AS Ist FROM [dbo].[Table1]

解决方案

Hope below queries will help you.

[1]
select t1.soll_value
from table1 t1 left outer join table1 t2
on t1.soll_id = t2.lst_value
where t2.lst_value is null; 

[2]
select t1.lst_value
from table1 t1 left outer join table1 t2
on t1.lst_value = t2.soll_value
where t2.soll_value is null; 

[3]
select t1.soll_value
from table1 t1 left outer join table1 t2
on t1.soll_value = t2.lst_value
where t2.lst_value is not null; 

[4]
select t1.lst_value
from table1 t1 left outer join table1 t2
on t1.lst_value = t2.soll_value
where t2.soll_value is not null; 

这篇关于如何获得所有差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！