7 个解决方案
#1
这样的表单视图估计只能用子查询实现了,或者更改视图数据组织逻辑~
#2
#3
就是这样有三个月的报表,我就想生成一张对比表。但在SQL里我不知道怎样做。各位能指点一下吗?谢谢!
#4
CREATE TABLE T1(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T2(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T3(CODE VARCHAR(10),QUANTITY INTEGER);
INSERT INTO T1(CODE,QUANTITY)
VALUES('A',1000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('B',2000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('C',3000);
INSERT INTO T2(CODE,QUANTITY)
VALUES('A',500);
INSERT INTO T2(CODE,QUANTITY)
VALUES('B',4000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('A',2000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('C',3000);
SELECT COALESCE(A.CODE,B.CODE,C.CODE,'') AS CODE,
COALESCE((B.QUANTITY - A.QUANTITY)*1.00/A.QUANTITY,0) AS ADD1,
COALESCE((C.QUANTITY - B.QUANTITY)*1.00/B.QUANTITY,0) AS ADD2
FROM T1 A FULL JOIN T2 B ON A.CODE = B.CODE
FULL JOIN T3 C ON A.CODE = C.CODE
ORDER BY COALESCE(A.CODE,B.CODE,C.CODE,'');
这个是db2的语句,可以参考一下.不过这种问题,数据量大了以后要一组语句实现的话,函数套函数,还得计算,还得全连接,会挠墙的.
CREATE TABLE T2(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T3(CODE VARCHAR(10),QUANTITY INTEGER);
INSERT INTO T1(CODE,QUANTITY)
VALUES('A',1000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('B',2000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('C',3000);
INSERT INTO T2(CODE,QUANTITY)
VALUES('A',500);
INSERT INTO T2(CODE,QUANTITY)
VALUES('B',4000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('A',2000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('C',3000);
SELECT COALESCE(A.CODE,B.CODE,C.CODE,'') AS CODE,
COALESCE((B.QUANTITY - A.QUANTITY)*1.00/A.QUANTITY,0) AS ADD1,
COALESCE((C.QUANTITY - B.QUANTITY)*1.00/B.QUANTITY,0) AS ADD2
FROM T1 A FULL JOIN T2 B ON A.CODE = B.CODE
FULL JOIN T3 C ON A.CODE = C.CODE
ORDER BY COALESCE(A.CODE,B.CODE,C.CODE,'');
这个是db2的语句,可以参考一下.不过这种问题,数据量大了以后要一组语句实现的话,函数套函数,还得计算,还得全连接,会挠墙的.
#5
还有个业务的问题,你2月份的C是没有销售的,不应该算是100%的负增长吗.然后3月份是正增长,不应该简单的归0吧
#6
好的,我试试。谢谢!
#7
在数据窗口里通过SQL可以实现,如果不行,就用三个数据窗口把数据查出来,然后通过代码查找对应的数值,把差值写入到别一个数据窗口或者表。
#1
这样的表单视图估计只能用子查询实现了,或者更改视图数据组织逻辑~
#2
#3
就是这样有三个月的报表,我就想生成一张对比表。但在SQL里我不知道怎样做。各位能指点一下吗?谢谢!
#4
CREATE TABLE T1(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T2(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T3(CODE VARCHAR(10),QUANTITY INTEGER);
INSERT INTO T1(CODE,QUANTITY)
VALUES('A',1000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('B',2000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('C',3000);
INSERT INTO T2(CODE,QUANTITY)
VALUES('A',500);
INSERT INTO T2(CODE,QUANTITY)
VALUES('B',4000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('A',2000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('C',3000);
SELECT COALESCE(A.CODE,B.CODE,C.CODE,'') AS CODE,
COALESCE((B.QUANTITY - A.QUANTITY)*1.00/A.QUANTITY,0) AS ADD1,
COALESCE((C.QUANTITY - B.QUANTITY)*1.00/B.QUANTITY,0) AS ADD2
FROM T1 A FULL JOIN T2 B ON A.CODE = B.CODE
FULL JOIN T3 C ON A.CODE = C.CODE
ORDER BY COALESCE(A.CODE,B.CODE,C.CODE,'');
这个是db2的语句,可以参考一下.不过这种问题,数据量大了以后要一组语句实现的话,函数套函数,还得计算,还得全连接,会挠墙的.
CREATE TABLE T2(CODE VARCHAR(10),QUANTITY INTEGER);
CREATE TABLE T3(CODE VARCHAR(10),QUANTITY INTEGER);
INSERT INTO T1(CODE,QUANTITY)
VALUES('A',1000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('B',2000);
INSERT INTO T1(CODE,QUANTITY)
VALUES('C',3000);
INSERT INTO T2(CODE,QUANTITY)
VALUES('A',500);
INSERT INTO T2(CODE,QUANTITY)
VALUES('B',4000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('A',2000);
INSERT INTO T3(CODE,QUANTITY)
VALUES('C',3000);
SELECT COALESCE(A.CODE,B.CODE,C.CODE,'') AS CODE,
COALESCE((B.QUANTITY - A.QUANTITY)*1.00/A.QUANTITY,0) AS ADD1,
COALESCE((C.QUANTITY - B.QUANTITY)*1.00/B.QUANTITY,0) AS ADD2
FROM T1 A FULL JOIN T2 B ON A.CODE = B.CODE
FULL JOIN T3 C ON A.CODE = C.CODE
ORDER BY COALESCE(A.CODE,B.CODE,C.CODE,'');
这个是db2的语句,可以参考一下.不过这种问题,数据量大了以后要一组语句实现的话,函数套函数,还得计算,还得全连接,会挠墙的.
#5
还有个业务的问题,你2月份的C是没有销售的,不应该算是100%的负增长吗.然后3月份是正增长,不应该简单的归0吧
#6
好的,我试试。谢谢!
#7
在数据窗口里通过SQL可以实现,如果不行,就用三个数据窗口把数据查出来,然后通过代码查找对应的数值,把差值写入到别一个数据窗口或者表。