差异计算不好

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 2008Problem areaCOALESCE((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.BRNNAMEBRNNAMESCOREQ1Y1CQ1Y2CQ2Y1CQ2Y2CQ3Y1CQ3Y2CQ4Y1CQ4Y2CDIFFCQ1CDIFFCQ2CDIFFCQ3CDIFFCQ4CCAPE COASTDELIGHTED225322870-20-1CAPE COASTGOOD111000110000HOHOEDELIGHTED400045440010HOHOEGOOD220022220000HOHOESATISFACTORY101200110100HOHOEUNHAPPY101101110000KUMASIBAD110000110000KUMASIDELIGHTED003333660000KUMASIGOOD001100110000What 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 meanDECLARE @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但这会给我结果：NULLNULLNULL15-15 您的 查询正在执行相当于：Your query is doing the equivalent of:SELECT COALESCE(Col1 - Col2,0) FROM @table给出结果00015-15 我的 方式是将查询编写为：My way would be to write the query as:SELECT ISNULL(Col1,0) - ISNULL(Col2,0) FROM @table结果：010-1015-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 likeSELECT 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. 这篇关于差异计算不好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！