如何使用自联接对记录进行计数和求和 [英] How to count and sum records using self join
本文介绍了如何使用自联接对记录进行计数和求和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨
我在计算和总结记录方面遇到了问题。这里有一个例子和可能的结果：
ProjectNr Ist Value Soll
D.01025 0 19010
D .01025 0 0
D.01025 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.01004 2210,34804 2184,59583
G.01004 637,73126 637,38993
G.01004 1557,47348 1424,94381
G.01004 745,29877 745,93531
G.01043 29285,59879 28600 ，87531
G.01162 23226 0
G.01069 72765,80058 73450,86162
结果：
ProjectNr Nr。行Soll Value
D.01025 3 428206
G.01013 1 20406,21832
。
。
G.01004 4 5001,84
我的声明如下，但不是合作
SELECT t1。[Projectnr]，count（t1。[Projectnr]） AS TotalOfProj，sum（t1。[Soll_value]） as Soll
FROM [dbo]。[table1] t1
LEFT OUTER JOIN [dbo]。[table1] t2
ON
t1。 [Soll_value] = t2。[Ist_value]
WHERE t2。[Ist_value] IS NULL
GROUP BY t1。[ Projectnr]，t1。[Soll_value]
ORDER BY t1。[Projectnr] ASC
解决方案
首先，如果你解释过逗号，那会有所帮助in20766,31575是德语符号而非英语的小数点。
我根据您的数据子集创建了一个小表，如下所示/>
创建 table table1（
ProjectNr varchar （ 20 ），
IstValue float ，
SollValue float
）
insert < span class =code-keyword> into table1 values （' D.01025'， 0 ， 19010 ）
insertinto table1 values （' D.01025'， 0 ， 0 ）
插入 进入 table1 值（' D.01025'， 0 ， 409196 ）
insert into table1 < span class =code-keyword> values （' G.01013'，< span class =code-digit> 20766 。 31575 ， 20406 。 21832 ）
插入 进入 table1 值（' G.01004'， 2210 。 34804 ， 2184 。 59583 ）
insert into table1 值（' G.01004'， 637 。 73126 ， 637 。 38993 ）
insert into table1 值（' G.01004'， 1557 。 47348 ， 1424 。 94381 ）
插入 进入 table1 值（' G.01004'， 745 。 29877 ， 745 。 93531 ）
如您所说，您在帖子中提出的查询
SELECT t1。[Projectnr]，count（t1。[Projectnr]） AS TotalOfProj，sum（t1。[SollValue]） as Soll
FROM [dbo]。[table1] t1
LEFT OUTER JOIN [dbo]。[table1] t2 ON t1。[SollValue] = t2。[IstValue]
WHERE t2。[IstValue] IS NULL
GROUP BY t1。[Projectnr]，t1。[SollValue]
ORDER BY t1。[Projectnr] ASC在针对我的子集运行时给出以下结果
PROJECTNR TOTALOFPROJ SOLL
D.01025 1 19010
D.01025 1 409196
G.01004 1 637.38993
G.01004 1 745.93531
G.01004 1 1424.94381
G.01004 1 2184.59583
G.01013 1 20406.21832您只获得1的原因是因为您在<$ c中包含了
Soll_Value$ c> GROUP BY ，它们都是唯一的。将它从Group By中删除，因为它完全没必要 - 您只显示Soll_Value的聚合而不是列本身。给你这个查询SELECT t1。[Projectnr]，count（t1。[Projectnr]） AS TotalOfProj，sum（t1。[SollValue]） as Soll
FROM [dbo]。[table1] t1
LEFT OUTER JOIN [dbo]。[table1] t2
ON
t1。[SollValue] = t2。[IstValue]
WHERE t2。[IstValue] IS NULL
GROUP BY t1。[Projectnr]
ORDER BY t1。[Projectnr] ASC
PROJECTNR TOTALOFPROJ SOLL
D.01025 2 428206
G.01004 4 4992.86488
G.01013 1 20406.21832
自我加入没有任何意义，因此以下查询将给出完全相同的结果：SELECT Projectnr，count（Projectnr） AS TotalOfProj，sum（SollValue） as Soll
FROM [dbo]。[table1]
WHERE IstValue<> SollValue
GROUP BY Projectnr
ORDER BY Projectnr ASC
编辑 - 在阅读OP的另一个问题后删除了关于自联接的位 - 自联接是应用实际需要的过滤器的有效方式
Hi
I' m having problems counting and summing up my records. Here an example and the possible outcome:
ProjectNr Ist Value Soll
D.01025 0 19010
D.01025 0 0
D.01025 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.01004 2210,34804 2184,59583
G.01004 637,73126 637,38993
G.01004 1557,47348 1424,94381
G.01004 745,29877 745,93531
G.01043 29285,59879 28600,87531
G.01162 23226 0
G.01069 72765,80058 73450,86162
OUTCOME:
ProjectNr Nr. of rows Soll Value
D.01025 3 428206
G.01013 1 20406,21832
.
.
G.01004 4 5001,84
My statement below but it isn't corporating
SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[Soll_value]) as Soll
FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2
ON
t1.[Soll_value] = t2.[Ist_value]
WHERE t2.[Ist_value] IS NULL
GROUP BY t1.[Projectnr], t1.[Soll_value]
ORDER BY t1.[Projectnr] ASC 解决方案
Firstly it would have helped if you had explained that the "comma" in "20766,31575" is the decimal point as per German notation not English.
I created a small table based on a subset of your data as follows
create table table1( ProjectNr varchar(20), IstValue float, SollValue float ) insert into table1 values('D.01025',0, 19010) insert into table1 values('D.01025',0, 0) insert into table1 values('D.01025',0, 409196) insert into table1 values('G.01013',20766.31575, 20406.21832) insert into table1 values('G.01004',2210.34804, 2184.59583) insert into table1 values('G.01004',637.73126, 637.38993) insert into table1 values('G.01004',1557.47348, 1424.94381) insert into table1 values('G.01004',745.29877, 745.93531)
As you say, the query you presented in your post
SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[SollValue]) as Soll FROM [dbo].[table1] t1 LEFT OUTER JOIN [dbo].[table1] t2 ON t1.[SollValue] = t2.[IstValue] WHERE t2.[IstValue] IS NULL GROUP BY t1.[Projectnr], t1.[SollValue] ORDER BY t1.[Projectnr] ASCgives the following results when run against my subset
PROJECTNR TOTALOFPROJ SOLL D.01025 1 19010 D.01025 1 409196 G.01004 1 637.38993 G.01004 1 745.93531 G.01004 1 1424.94381 G.01004 1 2184.59583 G.01013 1 20406.21832The reason you are only getting counts of 1 is because you have included
Soll_Valuein theGROUP BYand they are all unique. Remove it from the Group By as it is totally unnecessary - you are only displaying an aggregate of Soll_Value not the column itself. Giving you this querySELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[SollValue]) as Soll FROM [dbo].[table1] t1 LEFT OUTER JOIN [dbo].[table1] t2 ON t1.[SollValue] = t2.[IstValue] WHERE t2.[IstValue] IS NULL GROUP BY t1.[Projectnr] ORDER BY t1.[Projectnr] ASCwhich yields these results
PROJECTNR TOTALOFPROJ SOLL D.01025 2 428206 G.01004 4 4992.86488 G.01013 1 20406.21832
That self join serves no purpose either so the following query will give exactly the same results:SELECT Projectnr, count(Projectnr) AS TotalOfProj, sum(SollValue) as Soll FROM [dbo].[table1] WHERE IstValue <> SollValue GROUP BY Projectnr ORDER BY Projectnr ASC
Edit - removed the bit about the self-join after reading the OP's other question - self-join is an efficient way of applying the filters that are actually required
这篇关于如何使用自联接对记录进行计数和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
