|
楼主 |
发表于 2018-7-17 08:49
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
SQL语句,有点长:
- StrA = ""
- StrA = StrA & " SELECT YEAR(日期) AS 年份"
- StrA = StrA & ",工地名称"
- StrA = StrA & ",IIF(ISNULL(方量)=TRUE,0,方量) as 方量"
- StrA = StrA & ",IIF(ISNULL(金额)=TRUE,0,金额)-IIF(ISNULL(已付款)=TRUE,0,已付款) AS 欠款"
- StrA = StrA & ",日期"
- StrA = StrA & ",业务员,备注"
- StrA = StrA & " FROM [混凝土销售明细$]"
- StrA = StrA & " WHERE NOT [工地名称] IS NULL AND LEN(工地名称)>0"
-
-
- StrSQL = ""
- StrSQL = StrSQL & " SELECT 0 AS 序号,年份,业务员,工地名称"
- StrSQL = StrSQL & ",方量合计,欠款金额,开始打灰时间"
- StrSQL = StrSQL & ",NULL AS 备注"
- StrSQL = StrSQL & " FROM ("
-
-
- StrSQL = StrSQL & " SELECT 年份,工地名称,业务员"
- StrSQL = StrSQL & ",SUM(方量) AS 方量合计"
- StrSQL = StrSQL & ",SUM(欠款) AS 欠款金额"
- StrSQL = StrSQL & ",MIN(日期) AS 开始打灰时间"
- StrSQL = StrSQL & ",1 AS 排序A,1 AS 排序B,2 AS 排序C"
- StrSQL = StrSQL & " FROM (" & StrA & ")"
- StrSQL = StrSQL & " GROUP BY 年份,工地名称,业务员"
-
- StrSQL = StrSQL & " UNION ALL "
- StrSQL = StrSQL & " SELECT 年份,'小计' AS 工地名称,业务员"
- StrSQL = StrSQL & ",SUM(方量) AS 方量合计"
- StrSQL = StrSQL & ",SUM(欠款) AS 欠款金额"
- StrSQL = StrSQL & ",MIN(日期) AS 开始打灰时间"
- StrSQL = StrSQL & ",1 AS 排序A,2 AS 排序B,2 AS 排序C"
- StrSQL = StrSQL & " FROM (" & StrA & ")"
- StrSQL = StrSQL & " GROUP BY 年份,业务员"
-
- StrSQL = StrSQL & " UNION ALL "
- StrSQL = StrSQL & " SELECT 年份,'年度合计' AS 工地名称,'年度合计' AS 业务员"
- StrSQL = StrSQL & ",SUM(方量) AS 方量合计"
- StrSQL = StrSQL & ",SUM(欠款) AS 欠款金额"
- StrSQL = StrSQL & ",MIN(日期) AS 开始打灰时间"
- StrSQL = StrSQL & ",3 AS 排序A,2 AS 排序B,3 AS 排序C"
- StrSQL = StrSQL & " FROM (" & StrA & ")"
- StrSQL = StrSQL & " GROUP BY 年份"
-
- StrSQL = StrSQL & " UNION ALL "
- StrSQL = StrSQL & " SELECT '总计' AS 年份,'总计' AS 工地名称,'总计' AS 业务员"
- StrSQL = StrSQL & ",SUM(方量) AS 方量合计"
- StrSQL = StrSQL & ",SUM(欠款) AS 欠款金额"
- StrSQL = StrSQL & ",MIN(日期) AS 开始打灰时间"
- StrSQL = StrSQL & ",5 AS 排序A,5 AS 排序B,5 AS 排序C"
- StrSQL = StrSQL & " FROM (" & StrA & ")"
-
- StrSQL = StrSQL & ") ORDER BY 年份,排序A,业务员,排序B,排序C,开始打灰时间"
-
- SQLARR = GET_SQL_To_Arr(StrSQL, Str_coon, False)
复制代码 |
|