为什么此查询有时会为描述返回多条记录？ [英] Why would this query sometimes return more than one record for a Description?

问题描述

我创建的存储过程应该只为其中显示的每两周时间返回一条记录，因为它首先查询第一周的数据，然后它在第二周使用基本相同的查询，最后将存储它们的两个临时表中的记录合并到第三个临时表中的单个记录中（这也增加了一些值）。



然而，甚至虽然以上理论上是*应该*发生，但我有时会得到同一项目的多条记录。



此外，WEEK2PRICE中的值不同柱。怎么会这样？存储程序AFAICT不允许这种重复。这是：

The Stored Procedure I've created should only return one record for each two week period represented within it, because it first queries data for the first week, and thereafter it uses basically the same query for the second week period, and finally combines the records from the two temp tables they are stored in into a single record in a third temp table (which also adds a few more values).

Yet, even though the above is theoretically what *should* happen, I sometimes get multiple records for the same item.

Also, the value differs for the value in the "WEEK2PRICE" column. How could this be? The Stored Procedure, AFAICT, would not allow such duplication. Here it is:

CREATE Procedure [dbo].[priceAndUsageVariance]
	@Unit varchar(25),
	@BegDate datetime,
	@EndDate datetime
AS 

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);

// temp1 holds some values for the first week
CREATE TABLE #TEMP1
(
	MemberNo VARCHAR(6),
	MemberName VARCHAR(50),
	MEMBERITEMCODE VARCHAR(25),
	DESCRIPTION VARCHAR(50),
	WEEK1USAGE DECIMAL(18,2),
	WEEK1PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP1 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION, WEEK1USAGE, 

WEEK1PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND @Week1End
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// temp2 holds some values for the second week
CREATE TABLE #TEMP2
(
	MemberNo VARCHAR(6),
	MemberName VARCHAR(50),
	MEMBERITEMCODE VARCHAR(25),
	DESCRIPTION VARCHAR(50),
	WEEK2USAGE DECIMAL(18,2),
	WEEK2PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP2 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION, WEEK2USAGE, 

WEEK2PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @Week2Begin AND @ENDDATE
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// Now tempCombined gets the shared values from temp1 as well as the unique vals from temp1 and the unique vals from temp2
CREATE TABLE #TEMPCOMBINED(
  MemberNo VARCHAR(6),
  MemberName VARCHAR(50),
  ItemCode VARCHAR(15),
  MemberItemCode VARCHAR(20),
  PlatypusDESCRIPTION VARCHAR(50),
  MEMBERDESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS CAST((WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) AS DECIMAL

(18,5))
);

INSERT INTO #TEMPCOMBINED (MemberNo, MemberName, ItemCode, MemberItemCode, 

PlatypusDESCRIPTION, MEMBERDESCRIPTION,
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.MemberNo, T1.MemberName, 'X', T1.MEMBERITEMCODE, NULL, T1.DESCRIPTION, 

T1.WEEK1USAGE, T2.WEEK2USAGE, 
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE

	// Now some mumbo-jumbo is performed to display the "general" description rather than the "localized" description
	UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
	(SELECT TOP 1 ItemCode 
	 FROM MasterMemberUnitMapping 
	 WHERE Unit=@Unit
	 AND MemberNo=#TEMPCOMBINED.MemberNo 
	 AND MemberItemCode = #TEMPCOMBINED.MemberItemCode 
	 AND ItemCode IN (SELECT ItemCode FROM UnitProducts WHERE Unit=@Unit)),'X'
	)
	WHERE ItemCode='X'

	UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
	(SELECT TOP 1 ItemCode FROM MasterMemberMapping WHERE 
	 MemberNo=#TEMPCOMBINED.MemberNo AND MemberItemCode + PackType = 

#TEMPCOMBINED.MemberItemCode ),'X'
	) 
	WHERE ItemCode='X'

	UPDATE #TEMPCOMBINED SET PlatypusDESCRIPTION = ISNULL(MP.Description,'')
	FROM #TEMPCOMBINED TC 
	INNER JOIN MasterProducts MP ON MP.Itemcode=TC.ItemCode

// finally, what is hoped to be the desired amalgamation is returned
SELECT TC.PlatypusDESCRIPTION, TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, 

TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
ORDER BY TC.PlatypusDESCRIPTION, TC.MemberName;

那么我怎样才能找回给定周的重复记录，以及价值这些准冗余记录之间通常但不是普遍重复？

So how can it be that I'm getting back duplicate records for a given week, and that the values are generally, but not universally duplicated, between those quasi-redundant records?

推荐答案

我相信当你加入两个临时表时，你会错过一个条件。 br />


尝试：

I believe you're missing a condition when you join your two temp tables.

Try:

INSERT INTO #TEMPCOMBINED (MemberNo, MemberName, ItemCode, MemberItemCode, 
 
PlatypusDESCRIPTION, MEMBERDESCRIPTION,
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.MemberNo, T1.MemberName, 'X', T1.MEMBERITEMCODE, NULL, T1.DESCRIPTION, 
 
T1.WEEK1USAGE, T2.WEEK2USAGE, 
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
    AND T1.MEMBERNO = T2.MEMBERNO

这篇关于为什么此查询有时会为描述返回多条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！