差异计算不好 [英] Difference calculation not coming out well

问题描述

亲爱的专家，



计算差异和输出不对。



DIFFCQ1C
DIFFCQ2C

DIFFCQ3C

DIFFCQ4C





请仔细阅读和建议;





数据库MS SQL SERVER 2008



问题区域



COALESCE（（C.Q1Y2C-A.Q1Y1C），0）DIFFCQ1C，

COALESCE（（C.Q2Y2C-A.Q2Y1C） ），0）DIFFCQ2C，

COALESCE（（C.Q3Y2C-A.Q3Y1C），0）DIFFCQ3C，

COALESCE（（C.Q4Y2C-A.Q4Y1C）， 0）DIFFCQ4C





SELECT A.BRNNAME，A.SCORE，COALESCE（A.Q1Y1C，0）Q1Y1C，COALESCE（C。 Q1Y2C，0）Q1Y2C，

COALESCE（A.Q2Y1C，0）Q2Y1C，COALESCE（C.Q2Y2C,0）Q2Y2C，

COALESCE（A.Q3Y1C，0） Q3Y1C，COALESCE（C.Q3Y2C,0）Q3Y2C，

COALESCE（A.Q4Y1C，0）Q4Y1C，COALESCE（C.Q4Y2C,0）Q4Y2C，

COALESCE（（C.Q1Y2C-A.Q1Y1C），0）DIFFCQ1C，

COALESCE（（C.Q2Y2C-A.Q2Y1C），0）DIFFCQ2C，

COALESCE（ （C.Q3Y2C-A.Q3Y1C），0）DIFFCQ3C，

COALESCE（（C.Q4Y2C-A.Q4Y1C），0）DIFFCQ4C

来自Y1C A

加入Y1P B开A.BRNNAME = B.BRNNAME和A.SCORE = B.SCORE

加入Y2C C开A.BRNNAME = C.BRNNAME和A.SCORE = C .SCORE

加入Y2P D ON A.BRNNAME = D.BRNNAME和A.SCORE = D.SCORE

订购A.BRNNAME





BRNNAME SCORE Q1Y1C Q1Y2C Q2Y1C Q2Y2C Q3Y1C Q3Y2C Q4Y1C Q4Y2C DIFFCQ1C DIFFCQ2C DIFFCQ3C DIFFCQ4C

CAPE COAST DELIGHTED 2 2 5 3 2 2 8 7 0 -2 0 -1

CAPE COAST GOOD 1 1 1 0 0 0 1 1 0 0 0 0

HOHOE DELIGHTED 4 0 0 0 4 5 4 4 0 0 1 0

HOHOE GOOD 2 2 0 0 2 2 2 2 0 0 0 0

HOHOE SATISFACT ORY 1 0 1 2 0 0 1 1 0 1 0 0

HOHOE UNHAPPY 1 0 1 1 0 1 1 1 0 0 0 0

KUMASI BAD 1 1 0 0 0 0 1 1 0 0 0 0

KUMASI DELIGHTED 0 0 3 3 3 3 6 6 0 0 0 0

KUMASI GOOD 0 0 1 1 0 0 1 1 0 0 0 0



我尝试了什么：



这些正在建设中的代码。

Dear Expert,

Calculation the differences and output not right.

DIFFCQ1C
DIFFCQ2C
DIFFCQ3C
DIFFCQ4C


Please peruse and advice ;


database MS SQL SERVER 2008

Problem area

COALESCE((C.Q1Y2C-A.Q1Y1C) , 0) DIFFCQ1C ,
COALESCE((C.Q2Y2C-A.Q2Y1C) , 0) DIFFCQ2C ,
COALESCE((C.Q3Y2C-A.Q3Y1C) , 0) DIFFCQ3C ,
COALESCE((C.Q4Y2C-A.Q4Y1C) , 0) DIFFCQ4C


SELECT A.BRNNAME,A.SCORE , COALESCE(A.Q1Y1C, 0) Q1Y1C , COALESCE(C.Q1Y2C, 0) Q1Y2C ,
COALESCE(A.Q2Y1C, 0) Q2Y1C , COALESCE(C.Q2Y2C, 0) Q2Y2C ,
COALESCE(A.Q3Y1C, 0) Q3Y1C , COALESCE(C.Q3Y2C, 0) Q3Y2C ,
COALESCE(A.Q4Y1C, 0) Q4Y1C , COALESCE(C.Q4Y2C, 0) Q4Y2C,
COALESCE((C.Q1Y2C-A.Q1Y1C) , 0) DIFFCQ1C ,
COALESCE((C.Q2Y2C-A.Q2Y1C) , 0) DIFFCQ2C ,
COALESCE((C.Q3Y2C-A.Q3Y1C) , 0) DIFFCQ3C ,
COALESCE((C.Q4Y2C-A.Q4Y1C) , 0) DIFFCQ4C
FROM Y1C A
JOIN Y1P B ON A.BRNNAME=B.BRNNAME AND A.SCORE=B.SCORE
JOIN Y2C C ON A.BRNNAME=C.BRNNAME AND A.SCORE=C.SCORE
JOIN Y2P D ON A.BRNNAME=D.BRNNAME AND A.SCORE=D.SCORE
ORDER BY A.BRNNAME


BRNNAME SCORE Q1Y1C Q1Y2C Q2Y1C Q2Y2C Q3Y1C Q3Y2C Q4Y1C Q4Y2C DIFFCQ1C DIFFCQ2C DIFFCQ3C DIFFCQ4C
CAPE COAST DELIGHTED 2 2 5 3 2 2 8 7 0 -2 0 -1
CAPE COAST GOOD 1 1 1 0 0 0 1 1 0 0 0 0
HOHOE DELIGHTED 4 0 0 0 4 5 4 4 0 0 1 0
HOHOE GOOD 2 2 0 0 2 2 2 2 0 0 0 0
HOHOE SATISFACTORY 1 0 1 2 0 0 1 1 0 1 0 0
HOHOE UNHAPPY 1 0 1 1 0 1 1 1 0 0 0 0
KUMASI BAD 1 1 0 0 0 0 1 1 0 0 0 0
KUMASI DELIGHTED 0 0 3 3 3 3 6 6 0 0 0 0
KUMASI GOOD 0 0 1 1 0 0 1 1 0 0 0 0

What I have tried:

These are codes under construction.

推荐答案

你在错误的级别使用COALESCE。



如果你使用NULL值计算然后整个结果变为NULL。你似乎想要的是将每个NULL值视为0并在计算中使用0。



这是一个非常简单的例子我的意思

You are using COALESCE at the wrong level.

If you use a NULL value in a calculation then the entire result becomes NULL. What you seem to want is to treat each NULL value as 0 and use the 0 in the calculation.

Here is a very simple example of what I mean

DECLARE  @table TABLE (Col1 INT NULL, Col2 INT NULL)

INSERT INTO @table (Col1, Col2) VALUES
(NULL, NULL),
(10, NULL),
(NULL, 10),
(20, 5),
(5, 20)

如果我想列出每行的Col1和Col2之间的差异，我可能会开始这样的事情：

If I want to list the differences between Col1 and Col2 for each row I might start out with something like this:

SELECT Col1 - Col2 FROM @table

但这会给我结果：

NULL
NULL
NULL
15
-15

您的 查询正在执行相当于：

Your query is doing the equivalent of:

SELECT COALESCE(Col1 - Col2,0) FROM @table

给出结果

0
0
0
15
-15

我的 方式是将查询编写为：

My way would be to write the query as:

SELECT ISNULL(Col1,0) - ISNULL(Col2,0) FROM @table

结果：

0
10
-10
15
-15

所以，请注意：

a）我正在合并每列

b我用过ISNULL而不是COALESCE。他们做同样的事情但是当你只有两个项目合并时，ISNULL（显然）稍微快一点。键入更快一点:-)



您的查询应该类似于

So, points to note:
a) I'm coalescing each column individually
b) I've used ISNULL instead of COALESCE. They do exactly the same thing but ISNULL is (apparently) marginally faster when you only have the two items to coalesce. And it is a bit quicker to type :-)

Your query should look something like

SELECT A.BRNNAME,A.SCORE , COALESCE(A.Q1Y1C, 0) Q1Y1C , COALESCE(C.Q1Y2C, 0) Q1Y2C ,
 COALESCE(A.Q2Y1C, 0) Q2Y1C , COALESCE(C.Q2Y2C, 0) Q2Y2C ,
 COALESCE(A.Q3Y1C, 0) Q3Y1C , COALESCE(C.Q3Y2C, 0) Q3Y2C ,
 COALESCE(A.Q4Y1C, 0) Q4Y1C , COALESCE(C.Q4Y2C, 0) Q4Y2C,
 ISNULL(C.Q1Y2C,0)-ISNULL(A.Q1Y1C,0) DIFFCQ1C ,
 ISNULL(C.Q2Y2C,0)-ISNULL(A.Q2Y1C, 0) DIFFCQ2C ,
 ISNULL(C.Q3Y2C,0)-ISNULL(A.Q3Y1C, 0) DIFFCQ3C ,
 ISNULL(C.Q4Y2C,0)-ISNULL(A.Q4Y1C, 0) DIFFCQ4C 
 FROM Y1C A
 JOIN Y1P B ON A.BRNNAME=B.BRNNAME AND A.SCORE=B.SCORE
 JOIN Y2C C ON A.BRNNAME=C.BRNNAME AND A.SCORE=C.SCORE
 JOIN Y2P D ON A.BRNNAME=D.BRNNAME AND A.SCORE=D.SCORE
 ORDER BY A.BRNNAME

但显然我无法对此进行测试。

but obviously I haven't been able to test this.

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