如何合并包含相同两个“键”值的行的行？列？

编程入门行业动态更新时间:2024-10-26 06:30:24

本文介绍了如何合并包含相同两个“键”值的行的行？列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我的存储过程在很大程度上是返回我想要的数据。但是，在一些孤立的情况下，它返回两个记录，其中应该只有一个。具体来说，在坏情况下，有两个具有相同Description和MemberName的记录。每个Description和MemberName组合应该只有一行（max）。而不是单独列出它们，两个记录的值应合并为一个。 IOW，数据应如下：

My Stored Procedure is, for the most part, returning the data I want. However, in a few isolated cases, it returns two records where there should just be one. Specifically, in the "bad" cases, there are two records with the same Description and MemberName. There should only be one row (max) for each Description and MemberName combination. Rather than list them seperately, the values of the two records should be combined into one. IOW, the data should be like this:

PLATYPUSDESCRIPTION CRITTERNAME WEEK1VAL WEEK2VAL ----------------- ------------- -------- -------- Platypus Bill 7 42 Duckbill Pat 40 76

...但它包含一些重复的PLATYPUSDESCRIPTION + CRITTERNAME值，如下所示：

...but it contains some duplicated PLATYPUSDESCRIPTION + CRITTERNAME values like so:

PLATYPUSDESCRIPTION CRITTERNAME WEEK1VAL WEEK2VAL ----------------- ------------- -------- -------- Platypus Bill 7 42 Duckbill Pat 40 76 Duckbill Pat 40 99

在上面的例子中，第2行应该是：

In the case above, row 2 should be:

Duckbill Pat 80 175

...而且应该没有第3行。这是SP的最后一部分，汇集了返回数据：

...and there should be no row 3. Here is the last part of the SP, where the assembled data is returned:

SELECT PLATYPUSDESCRIPTION, CRITTERNAME, TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE, TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE FROM #TEMPCOMBINED TC ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

为了避免PLATYPUSDESCRIPTION + CRITTERNAME重复，我尝试在GROUP BY中连接PLATYPUSDESCRIPTION和CRITTERNAME：

In order to avoid PLATYPUSDESCRIPTION + CRITTERNAME duplicates, I tried concatenating PLATYPUSDESCRIPTION and CRITTERNAME in a GROUP BY:

...并在GROUP BY中单独列出PLATYPUSDESCRIPTION和CRITTERNAME：

...and listing PLATYPUSDESCRIPTION and CRITTERNAME singly in a GROUP BY:

...并尝试以这种方式在PLATYPUSDESCRIPTION和CRITTERNAME上使用DISTINCT：

...and tried to use DISTINCT on PLATYPUSDESCRIPTION and CRITTERNAME this way:

SELECT DISTINCT(PLATYPUSDESCRIPTION), DISTINCT(CRITTERNAME), TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE, TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE FROM #TEMPCOMBINED TC GROUP BY PLATYPUSDESCRIPTION, CRITTERNAME ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

......就这样：

...and this way:

SELECT DISTINCT(PLATYPUSDESCRIPTION + CRITTERNAME), TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE, TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE FROM #TEMPCOMBINED TC GROUP BY PLATYPUSDESCRIPTION, CRITTERNAME ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

...但是他们都不会编译。如何合并描述相同PLATYPUSDESCRIPTION + CRITTERNAME的行？

...but none of them would even compile. How can I consolidate rows that describe the same PLATYPUSDESCRIPTION + CRITTERNAME?

推荐答案

根据问题中给出的输入数据和预期结果，您可以使用SUM和GROUP BY来获取您要查找的输出。我根据列创建了一个表，并将问题中提供的三行插入到表中。 From your input data and expected result given in the question, you can use SUM and GROUP BY to get the output you are looking for. I created a table based on the columns and inserted three rows you provided in the question into the table. PLATYPUSDESCRIPTION CRITTERNAME WEEK1VAL WEEK2VAL ------------------- ----------- -------- -------- Platypus Bill 7 42 Duckbill Pat 40 76 Duckbill Pat 40 99

此查询将产生您的预期结果：

This query will produce your expected result:

select PLATYPUSDESCRIPTION, CRITTERNAME, sum(WEEK1VAL) as WEEK1VAL, sum(WEEK2VAL) as WEEK2VAL from dbo.TestTable group by PLATYPUSDESCRIPTION,CRITTERNAME

输出这个查询是：

The output of this query is:

PLATYPUSDESCRIPTION CRITTERNAME WEEK1VAL WEEK2VAL ------------------- ----------- -------- -------- Platypus Bill 7 42 Duckbill Pat 80 175