搜索
编程论坛 → 数据库技术 → 『 VFP论坛 』 → 求一句SQL语句解决问题
标题：求一句SQL语句解决问题
只看楼主
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 31 楼
 得分:0 
sqlml="SELECT a1.编码,a1.名称,"
sqlml=+sqlml+"IIF(ISNULL(b1.本月发出),0,b1.本月发出) as 本月发出,"
sqlml=+sqlml+"IIF(ISNULL(c1.本月开票),0,c1.本月开票) as 本月开票,"
sqlml=+sqlml+"IIF(ISNULL(d1.本月回款),0,d1.本月回款) as 本月回款,"
sqlml=+sqlml+"IIF(ISNULL(b2.累计发出),0,b2.累计发出) as 累计发出,"
sqlml=+sqlml+"IIF(ISNULL(c2.累计开票),0,c2.累计开票) as 累计开票,"
sqlml=+sqlml+"IIF(ISNULL(d2.累计回款),0,d2.累计回款) as 累计回款 from 客户编码 a1 "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月发出 from 发出商品 where 日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)b1 on a1.编码=b1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月开票 from 开票信息 where 开票日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)c1 on a1.编码=c1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 本月回款 from 回款信息 where 日期 BETWEEN #&ycrq.# and #&jsrq.#  group by khbm)d1 on a1.编码=d1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计发出 from 发出商品 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)b2 on a1.编码=b2.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计开票 from 开票信息 where 开票日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)c2 on a1.编码=c2.khbm "
sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 累计回款 from 回款信息 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)d2 on a1.编码=d2.khbm "
sqlml=+sqlml+"order by a1.编码"

IF SQLEXEC(mycon1,sqlml,'lsk')<0
   errmsg()
   RETURN
ENDIF
BROWSE
USE IN lsk
这样提示错误，不知道怎么办才行？？
2016-12-09 12:39
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 32 楼
 得分:0 
ncrq="2016-1-1"
jsrq="2016-11-30"
ycrq="2016-11-1"

sqlml="SELECT a1.编码,a1.名称,"
sqlml=+sqlml+"IIF(ISNULL(b1.本月发出),0,b1.本月发出) as 本月发出,"
sqlml=+sqlml+"IIF(ISNULL(c1.本月开票),0,c1.本月开票) as 本月开票,"
sqlml=+sqlml+"IIF(ISNULL(d1.本月回款),0,d1.本月回款) as 本月回款,"
sqlml=+sqlml+"IIF(ISNULL(b2.累计发出),0,b2.累计发出) as 累计发出,"
sqlml=+sqlml+"IIF(ISNULL(c2.累计开票),0,c2.累计开票) as 累计开票,"
sqlml=+sqlml+"IIF(ISNULL(d2.累计回款),0,d2.累计回款) as 累计回款 from 客户编码 a1 "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月发出 from 发出商品 where 日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)b1 on a1.编码=b1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月开票 from 开票信息 where 开票日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)c1 on a1.编码=c1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 本月回款 from 回款信息 where 日期 BETWEEN #&ycrq.# and #&jsrq.#  group by khbm)d1 on a1.编码=d1.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计发出 from 发出商品 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)b2 on a1.编码=b2.khbm "
sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计开票 from 开票信息 where 开票日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)c2 on a1.编码=c2.khbm "
sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 累计回款 from 回款信息 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)d2 on a1.编码=d2.khbm "
sqlml=+sqlml+"order by a1.编码"

IF SQLEXEC(mycon1,sqlml,'lsk')<0
   errmsg()
   RETURN
ENDIF
BROWSE
USE IN lsk
2016-12-09 12:40
mywisdom88
Rank: 16Rank: 16Rank: 16Rank: 16
等　级：版主
威　望：190
帖　子：3125
专家分：8340
注　册：2015-3-25
第 33 楼
 得分:0 
以下是引用yll148在2016-12-8 19:47:51的发言：

谢谢朋友们，在VFP的DBF表状态可行，如果这些表都是在ACCESS中的表，这种方法还可以吗？

方法一样，但我没用 ACCESS ，不知道里面的函数，你可以先把 ACCESS 的数据 读到 VFP 来，然后统计完后，再 更新到 ACCESS 上去。
2016-12-09 15:38
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 34 楼
 得分:0 
sqlml="SELECT a1.编码,a1.名称,"
sqlml=+sqlml+"IIF(ISNULL(b1.本月发出),0,b1.本月发出) as 本月发出 from 客户编码 a1 left join"
sqlml=+sqlml+"(select khbm,sum(金额) as 本月发出 from 发出商品 日期 BETWEEN #&ncrq.# and #&jsrq.# group by khbm)b1 on a1.编码=b1.khbm"
sqlml=+sqlml+"order by a1.编码"

这样读取ACCESS表没问题，但是下面这样就不行了，提示语句错误，不知道为啥？

ncrq="2016-1-1"
jsrq="2016-11-30"
ycrq="2016-11-1"

 sqlml="SELECT a1.编码,a1.名称,"
sqlml=+sqlml+"IIF(ISNULL(b1.本月发出),0,b1.本月发出) as 本月发出,"
sqlml=+sqlml+"IIF(ISNULL(c1.本月开票),0,c1.本月开票) as 本月开票,"
sqlml=+sqlml+"IIF(ISNULL(d1.本月回款),0,d1.本月回款) as 本月回款,"
sqlml=+sqlml+"IIF(ISNULL(b2.累计发出),0,b2.累计发出) as 累计发出,"
sqlml=+sqlml+"IIF(ISNULL(c2.累计开票),0,c2.累计开票) as 累计开票,"
sqlml=+sqlml+"IIF(ISNULL(d2.累计回款),0,d2.累计回款) as 累计回款 from 客户编码 a1 "
 sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月发出 from 发出商品 where 日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)b1 on a1.编码=b1.khbm "
 sqlml=+sqlml+"left join (select khbm,sum(金额) as 本月开票 from 开票信息 where 开票日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)c1 on a1.编码=c1.khbm "
 sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 本月回款 from 回款信息 where 日期 BETWEEN #&ycrq.# and #&jsrq.#  group by khbm)d1 on a1.编码=d1.khbm "
 sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计发出 from 发出商品 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)b2 on a1.编码=b2.khbm "
 sqlml=+sqlml+"left join (select khbm,sum(金额) as 累计开票 from 开票信息 where 开票日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)c2 on a1.编码=c2.khbm "
 sqlml=+sqlml+"left join (select khbm,sum(回款金额) as 累计回款 from 回款信息 where 日期 BETWEEN #&ncrq.# and #&jsrq.#  group by khbm)d2 on a1.编码=d2.khbm "
 sqlml=+sqlml+"order by a1.编码"

IF SQLEXEC(mycon1,sqlml,'lsk')<0
    errmsg()
    RETURN
 ENDIF
 BROWSE
 USE IN lsk
2016-12-09 20:13
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 35 楼
 得分:0 
显示如下错误
2016-12-09 20:17
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 36 楼
 得分:0 
ncrq="2016-1-1"
jsrq="2016-11-30"
ycrq="2016-11-1"

TEXT TO sqlml NOSHOW TEXT PRETEXT 7
sqlml="SELECT a1.编码,a1.名称,"
sqlml=+sqlml+"IIF(ISNULL(b1.本月发出),0,b1.本月发出) as 本月发出,"
sqlml=+sqlml+"IIF(ISNULL(c1.本月开票),0,c1.本月开票) as 本月开票,"
sqlml=+sqlml+"IIF(ISNULL(d1.本月回款),0,d1.本月回款) as 本月回款,"
sqlml=+sqlml+"IIF(ISNULL(b2.累计发出),0,b2.累计发出) as 累计发出,"
sqlml=+sqlml+"IIF(ISNULL(c2.累计开票),0,c2.累计开票) as 累计开票,"
sqlml=+sqlml+"IIF(ISNULL(d2.累计回款),0,d2.累计回款) as 累计回款 from 客户编码 a1 left join "
sqlml=+sqlml+"(select khbm,sum(金额)     as 本月发出 from 发出商品 where 日期     BETWEEN #&ycrq.# and #&jsrq.# group by khbm)b1 on a1.编码=b1.khbm left join "
sqlml=+sqlml+"(select khbm,sum(金额)     as 本月开票 from 开票信息 where 开票日期 BETWEEN #&ycrq.# and #&jsrq.# group by khbm)c1 on a1.编码=c1.khbm left join "
sqlml=+sqlml+"(select khbm,sum(回款金额) as 本月回款 from 回款信息 where 日期     BETWEEN #&ycrq.# and #&jsrq.# group by khbm)d1 on a1.编码=d1.khbm left join "
sqlml=+sqlml+"(select khbm,sum(金额)     as 累计发出 from 发出商品 where 日期     BETWEEN #&ncrq.# and #&jsrq.# group by khbm)b2 on a1.编码=b2.khbm left join "
sqlml=+sqlml+"(select khbm,sum(金额)     as 累计开票 from 开票信息 where 开票日期 BETWEEN #&ncrq.# and #&jsrq.# group by khbm)c2 on a1.编码=c2.khbm left join "
sqlml=+sqlml+"(select khbm,sum(回款金额) as 累计回款 from 回款信息 where 日期     BETWEEN #&ncrq.# and #&jsrq.# group by khbm)d2 on a1.编码=d2.khbm "
sqlml=+sqlml+"order by a1.编码"
ENDTEXT

IF SQLEXEC(mycon1,sqlml,'lsk')<0
   errmsg()
   RETURN
ENDIF
BROWSE
USE IN lsk
2016-12-09 20:40
andy292
Rank: 2
等　级：论坛游民
威　望：1
帖　子：86
专家分：13
注　册：2005-2-26
第 37 楼
 得分:0 
热心人真多，VF大坛人气旺
2016-12-10 08:51
yll148
Rank: 2
等　级：论坛游民
威　望：3
帖　子：266
专家分：15
注　册：2012-7-3
第 38 楼
 得分:0 
谢谢各位朋友的指教，我弄明白了，谢谢谢谢！
2016-12-11 14:21
sash
Rank: 4
等　级：业余侠客
威　望：6
帖　子：63
专家分：245
注　册：2014-4-25
第 39 楼
 得分:0 
以下是我用一句SQL语句获得楼主所需数据：（此语句在VFP9下执行通过，另外楼主提供的数据有误，某些明细数据中的khbm的值，客户信息表中没有）
SELECT 发送年月 AS 统计年月,IIF(ISNULL(客户编码),khbm,客户编码) AS 客户编码,IIF(ISNULL(客户名称),'',客户名称) AS 客户名称,发送金额汇总,回款金额汇总,开票金额汇总 FROM ;
(;
SELECT 编码 AS 客户编码,名称 AS 客户名称 FROM khbm ;
) a ;
RIGHT JOIN ;
(;
SELECT IIF(ISNULL(bb.khbm),IIF(ISNULL(cc.khbm),dd.khbm,cc.khbm),bb.khbm) AS khbm,;
       IIF(ISNULL(发送金额),0000000000.00,发送金额) AS 发送金额汇总,IIF(ISNULL(发送年月),IIF(ISNULL(回款年月),开票年月,回款年月),发送年月) AS 发送年月,;
       IIF(ISNULL(回款金额),0000000000.00,回款金额) AS 回款金额汇总,IIF(ISNULL(回款年月),IIF(ISNULL(发送年月),开票年月,发送年月),回款年月) AS 回款年月, ;
       IIF(ISNULL(开票金额),0000000000.00,开票金额) AS 开票金额汇总,IIF(ISNULL(开票年月),IIF(ISNULL(发送年月),回款年月,发送年月),开票年月) AS 开票年月 ;
  FROM ;
(;
SELECT khbm,SUM(金额) AS 发送金额,发送年月 FROM (SELECT khbm,金额,ALLTRIM(STR(YEAR(日期)))+'年'+PADL(ALLTRIM(STR(MONTH(日期))),2,'0')+'月' AS 发送年月 FROM fcsp ) fcsptmp GROUP BY khbm,发送年月 ;
) bb ;
  FULL JOIN ;
(;
SELECT khbm,SUM(回款金额) AS 回款金额,回款年月 FROM (SELECT khbm,回款金额,ALLTRIM(STR(YEAR(日期)))+'年'+PADL(ALLTRIM(STR(MONTH(日期))),2,'0')+'月' AS 回款年月 FROM hkxx ) hkxxtmp GROUP BY khbm,回款年月 ;
) cc ON bb.khbm=cc.khbm AND bb.发送年月=cc.回款年月 ;
  FULL JOIN ;
(;
SELECT khbm,SUM(金额) AS 开票金额,开票年月 FROM (SELECT khbm,金额,ALLTRIM(STR(YEAR(开票日期)))+'年'+PADL(ALLTRIM(STR(MONTH(开票日期))),2,'0')+'月' AS 开票年月 FROM kpxx ) kpxxtmp GROUP BY khbm,开票年月 ;
)  dd ON bb.khbm=dd.khbm AND bb.发送年月=dd.开票年月 ;
 WHERE 发送金额<>0 OR 回款金额<>0 OR 开票金额<>0 ;
) b;
ON a.客户编码=b.khbm ;
 ORDER BY 1,2;
  INTO CURSOR 汇总表
2016-12-11 20:59



参与讨论请移步原网站贴子：https://bbs.bccn.net/thread-472007-1-1.html




关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有，并保留所有权利。
Powered by Discuz, Processed in 0.037038 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved