要与另一个字段进行比较的聚合查询（SUM） [英] Aggregate query (SUM) to be compared to another field

问题描述

我需要创建一个查询，其中我有2个不同的表，具有相同的字段。 Table1表具有UID，Title和Cost作为Table2的字段和相同的字段。在表1中，字段UID是唯一的主键，而表2是辅助键。我想总结一下按UID分组的Table2上的Cost
（将多次出现），并将其与Table1中相应UID的成本（唯一）进行比较。表2可能有不匹配的UID，不应返回。

I need to create a query where I have 2 different tables with same fields. Table1 table has UID, Title and Cost as fields and same fields for Table2. In Table1 the field UID is unique and primary key and on Table2 is secondary key. I want to sum up the Cost on Table2 grouped by UID (which will appear multiple times) and compare it to the cost for the corresponding UID in Table1 (unique). Table 2 may have non-matching UIDs which shouldnt be returned.

表1

表2

UID  ;      标题                       成本

UID        Title                        Cost

推荐答案

嗨Kenny，

Hi Kenny,

我原本期望一个带有数据的样本DDL，以便我们回复您的查询。不过，这里是您正在寻找的解决方案

I would have expected a sample DDL with data so that it becomes easy for us to reply your query. Nevertheless, here is the solution that you are looking for

create table #table1 (UID int,	Title	varchar(100), Cost money)
INSERT INTO     #table1 VALUES  
(55215,'NOWHERE BOYS 	                        ','15,000.00'),
(55216,'NOWHERE BOYS 	                        ','22,500.00'),
(55217,'NOWHERE BOYS 	                        ','52,500.00'),
(55218,'OUIJA: ORIGIN OF EVIL (2016)	            ','125,416.67'),
(55219,'OUIJA: ORIGIN OF EVIL (2016)	            ','89,583.33'),
(55220,'ROGER CORMAN''S DEATH RACE 2050 (2017)	','115,000.00'),
(55221,'SIREN (2016)	                            ','115,000.00'),
(55222,'TANGO ONE (2018)	                        ','90,000.00'),
(55223,'SING (2016)	                            ','556,666.67'),
(55224,'SING (2016)	                            ','278,333.33');

create table #table2 (UID int,	Title	varchar(100), Cost money)
INSERT INTO     #table2 VALUES  
(55215,'NOWHERE BOYS	                            ','7,500.00'),
(55215,'NOWHERE BOYS	                            ','7,500.00'),
(55216,'NOWHERE BOYS	                            ','11,250.00'),
(55216,'NOWHERE BOYS	                            ','11,250.00'),
(55217,'NOWHERE BOYS	                            ','26,250.00'),
(55217,'NOWHERE BOYS	                            ','26,250.00'),
(55218,'OUIJA: ORIGIN OF EVIL (2016)	            ','62,708.33'),
(55218,'OUIJA: ORIGIN OF EVIL (2016)	            ','62,708.34'),
(55219,'OUIJA: ORIGIN OF EVIL (2016)	            ','44,791.67'),
(55219,'OUIJA: ORIGIN OF EVIL (2016)	            ','44,791.66'),
(55220,'ROGER CORMAN''S DEATH RACE 2050 (2017)	','57,500.00'),
(55220,'ROGER CORMAN''S DEATH RACE 2050 (2017)	','57,500.00'),
(55221,'SIREN (2016)	                            ','57,500.00'),
(55221,'SIREN (2016)	                            ','57,500.00'),
(55222,'TANGO ONE (2018)	                        ','45,000.00'),
(55222,'TANGO ONE (2018)	                        ','45,000.00'),
(55223,'SING (2016)	                            ','278,333.34'),
(55223,'SING (2016)	                            ','278,333.33'),
(55224,'SING (2016)	                            ','139,166.66'),
(55224,'SING (2016)	                            ','139,166.67'),
(55225,'SPLIT (2017)	                            ','118,750.00'),
(55225,'SPLIT (2017)	                            ','118,750.00'),
(55226,'SPLIT (2017)	                            ','118,750.00'),
(55226,'SPLIT (2017)	                            ','118,750.00');


SELECT t1.uid,t1.Title,t1.Cost,sum(t2.cost) 
FROM #table1 t1
INNER JOIN #table2 t2
    ON t1.uid = t2.uid
GROUP BY t1.uid,t1.Title,t1.Cost
HAVING t1.Cost = sum(t2.cost); 

以上查询查找具有匹配成本的记录。您可以更改having子句来过滤成本不匹配的记录，例如：

Above query finds records which have matching cost. You can change the having clause to filter records where cost does not match, like this.

SELECT t1.uid,t1.Title,t1.Cost,sum(t2.cost) 
FROM #table1 t1
INNER JOIN #table2 t2
    ON t1.uid = t2.uid
GROUP BY t1.uid,t1.Title,t1.Cost
HAVING t1.Cost <> sum(t2.cost); 

这篇关于要与另一个字段进行比较的聚合查询（SUM）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！