我想创建一个从 3 个 MySQL 表中获取值的 Crystal 报告,但我想编写我的自定义查询,因为当我在 Visual Studio 中选择表时,它显示的查询不符合我的预期。 我在本地主机phpMyAdmin上测试了查询,它起作用了。
注意1:我正在使用Visual Studio 2019并安装了Crystal Report SP26 注意 2:当 Visual Studio 编写查询时,它会在表名中添加"1" 就像boq_table让它boq_table1一样。
下面的代码表示我要执行的查询:
SELECT
ubc.boq_table.itemNum,
ubc.boq_table.descriptionOfWork,
ubc.boq_table.unit,
ubc.boq_table.contractualQuantity,
ubc.boq_table.priceNum,
ubc.summary.executedQuantLastSummary,
ubc.summary.priceLastWorks,
ubc.summary.executedQuantBetw2Sum,
ubc.submittal.priceCurrentWorks
FROM
ubc.boq_table
LEFT OUTER JOIN ubc.summary ON
ubc.boq_table.itemNum = ubc.summary.itemNum
LEFT OUTER JOIN ubc.submittal ON
ubc.boq_table.itemNum = ubc.submittal.itemNum
WHERE
ubc.boq_table.projectName ='proj'
UNION
SELECT
ubc.boq_table.itemNum,
ubc.boq_table.descriptionOfWork,
ubc.boq_table.unit,
ubc.boq_table.contractualQuantity,
ubc.boq_table.priceNum,
ubc.summary.executedQuantLastSummary,
ubc.summary.priceLastWorks,
ubc.summary.executedQuantBetw2Sum,
ubc.submittal.priceCurrentWorks
FROM
ubc.summary
LEFT OUTER JOIN ubc.boq_table ON
ubc.summary.itemNum = ubc.boq_table.itemNum
LEFT OUTER JOIN ubc.submittal ON
ubc.summary.itemNum = ubc.submittal.itemNum
WHERE
ubc.summary.projectName = 'proj'
UNION
SELECT
ubc.boq_table.itemNum,
ubc.boq_table.descriptionOfWork,
ubc.boq_table.unit,
ubc.boq_table.contractualQuantity,
ubc.boq_table.priceNum,
ubc.summary.executedQuantLastSummary,
ubc.summary.priceLastWorks,
ubc.summary.executedQuantBetw2Sum,
ubc.submittal.priceCurrentWorks
FROM
ubc.submittal
LEFT OUTER JOIN ubc.boq_table ON
ubc.submittal.itemNum = ubc.boq_table.itemNum
LEFT OUTER JOIN ubc.summary ON
ubc.submittal.itemNum = ubc.summary.itemNum
WHERE
ubc.submittal.projectName = 'proj'
ORDER BY
itemNum;
这段代码是Visual Studio在我选择表格后编写的
SELECT boq_table1.itemNum, boq_table1.descriptionOfWork, boq_table1.unit, boq_table1.contractualQuantity, boq_table1.priceNum, submittal1.priceCurrentWorks, summary1.executedQuantLastSummary, summary1.executedQuantBetw2Sum, summary1.priceLastWorks
FROM (ubc.boq_table boq_table1 INNER JOIN ubc.submittal submittal1 ON boq_table1.ID=submittal1.ID) INNER JOIN ubc.summary summary1 ON boq_table1.ID=summary1.ID
ORDER BY boq_table1.itemNum
最后一个代码只读我无法编辑
如果你想将数据获取到晶体 reprots,你可以做两件事,你在数据库选择器中再看一遍,有一个添加命令的选项,或者使用连接器和 Datareader 来构建一个你可以使用的数据集。
第一个是可以的,如果你不需要大量计算数据。