总数语句格式

houdongen

您好：

       select o.xcode,xname=d.xproductName,xcun=sum(o.xnumber - b.xselectSum),ying=sum(o.xnumber)

FROM QRY_DYN_ORDERINFO o
INNER  JOIN QRY_DYN_SENDANDRECEIVEINFORMATION b
ON o.xcode=b.xorderFormCode
INNER  JOIN QRY_DYN_PRODUCTTABLE d
ON o.xproductName = d.xcode
group by o.xcode,d.xproductName
以上是查询语句
以下是指定总数语句，格式是不是有误，报错了
[backcolor=rgba(245, 246, 249, 0.4)]SELECT[backcolor=rgba(245, 246, 249, 0.4)] [backcolor=rgba(245, 246, 249, 0.4)]COUNT[backcolor=rgba(245, 246, 249, 0.4)]([backcolor=rgba(245, 246, 249, 0.4)]*[backcolor=rgba(245, 246, 249, 0.4)]) [backcolor=rgba(245, 246, 249, 0.4)]AS[backcolor=rgba(245, 246, 249, 0.4)] TotalCount
[backcolor=rgba(245, 246, 249, 0.4)]FROM[backcolor=rgba(245, 246, 249, 0.4)] (
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]SELECT[backcolor=rgba(245, 246, 249, 0.4)] o.xcode,xname[backcolor=rgba(245, 246, 249, 0.4)]=[backcolor=rgba(245, 246, 249, 0.4)]d.xproductName,xcun[backcolor=rgba(245, 246, 249, 0.4)]=[backcolor=rgba(245, 246, 249, 0.4)]sum[backcolor=rgba(245, 246, 249, 0.4)](o.xnumber [backcolor=rgba(245, 246, 249, 0.4)]-[backcolor=rgba(245, 246, 249, 0.4)] b.xselectSum),ying[backcolor=rgba(245, 246, 249, 0.4)]=[backcolor=rgba(245, 246, 249, 0.4)]sum[backcolor=rgba(245, 246, 249, 0.4)](o.xnumber)
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]FROM[backcolor=rgba(245, 246, 249, 0.4)] QRY_DYN_ORDERINFO o
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]INNER[backcolor=rgba(245, 246, 249, 0.4)]  [backcolor=rgba(245, 246, 249, 0.4)]JOIN[backcolor=rgba(245, 246, 249, 0.4)] QRY_DYN_SENDANDRECEIVEINFORMATION b
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]ON[backcolor=rgba(245, 246, 249, 0.4)] o.xcode[backcolor=rgba(245, 246, 249, 0.4)]=[backcolor=rgba(245, 246, 249, 0.4)]b.xorderFormCode
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]INNER[backcolor=rgba(245, 246, 249, 0.4)]  [backcolor=rgba(245, 246, 249, 0.4)]JOIN[backcolor=rgba(245, 246, 249, 0.4)] QRY_DYN_PRODUCTTABLE d
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]ON[backcolor=rgba(245, 246, 249, 0.4)] o.xproductName [backcolor=rgba(245, 246, 249, 0.4)]=[backcolor=rgba(245, 246, 249, 0.4)] d.xcode
[backcolor=rgba(245, 246, 249, 0.4)]    [backcolor=rgba(245, 246, 249, 0.4)]group[backcolor=rgba(245, 246, 249, 0.4)] [backcolor=rgba(245, 246, 249, 0.4)]by[backcolor=rgba(245, 246, 249, 0.4)] o.xcode,d.xproductName
[backcolor=rgba(245, 246, 249, 0.4)]) [backcolor=rgba(245, 246, 249, 0.4)]AS[backcolor=rgba(245, 246, 249, 0.4)] subquery

论坛管理员

指定总数语句在哪儿？没看见，报错截图发出来，格式乱了看不出来
另外您使用的原生sql语句吧，如果语句报错，可以先去数据库连接工具里试试看您的sql语句是否正确

houdongen

论坛管理员 发表于 2023-9-27 09:32
指定总数语句在哪儿？没看见，报错截图发出来，格式乱了看不出来
另外您使用的原生sql语句吧，如果语句报错 ...

语句没问题的，总数语句忽略可以执行，现在想使用分页，指定语句应该怎样写，例子里面是  SELECT count(o.id) FROM TaskCompleted o，，那聚合函数，应该返回什么呢....

论坛管理员

一样，跟您查询的条件一样的，然后返回总数，这个总数是用于视图分页的

houdongen

论坛管理员 发表于 2023-9-27 16:25
一样，跟您查询的条件一样的，然后返回总数，这个总数是用于视图分页的

您好：

        总数语句，sql语句在 数据库工具均可执行，
平台报错，忽略又正常，查询到的总数确实是2条，总数语句如下


SELECT COUNT(*) AS 行数 FROM (
SELECT d.xcode AS 订单编号,
e.xcustomerName AS 客户名称,
b.xproductName AS 产品名称,
d.xnumber AS 订单数量,
d.xprice AS 报价单价,
isnull(sum(o.xselectSum), 0) AS 已入库,
isnull(sum(o.xweekday), 0) AS 已发货,
(isnull(d.xnumber, 0) - isnull(sum(o.xweekday), 0)) AS 待发货,
(isnull(d.xnumber, 0) - isnull(sum(o.xselectSum), 0)) AS 待入库,
(isnull(d.xprice, 0) * isnull(sum(o.xweekday), 0)) AS 应付款,
isnull(aa.已付款, 0) AS 已付款,
(isnull(d.xprice * sum(o.xweekday), 0) - isnull(aa.已付款, 0)) AS 待付款
FROM QRY_DYN_ORDERINFO d--dingdan
LEFT JOIN QRY_DYN_SENDANDRECEIVEINFORMATION o--shoufa
ON o.xorderFormCode = d.xcode
AND o.xstate = '审核'
LEFT JOIN QRY_DYN_PRODUCTTABLE b--chanpin
ON d.xproductName = b.xcode
LEFT JOIN QRY_DYN_CUSTOMERTABLE e--kehu
ON d.xcustomerName = e.xcustomerId

LEFT JOIN(
SELECT f.xorderFormCode, SUM(f.xselectSum) AS 已付款 FROM QRY_DYN_FINANCIALINFORMATION f GROUP BY f.xorderFormCode
) AS aa ON aa.xorderFormCode = d.xcode
GROUP BY d.xcode, d.xnumber, b.xproductName, e.xcustomerName, d.xprice, aa.已付款
) AS 结果集

论坛管理员

您是什么版本的o2server？

houdongen

论坛管理员 发表于 2023-9-28 09:25
您是什么版本的o2server？

8.1.1版本，官网下载的

xadmin

houdongen 发表于 2023-9-28 10:04
8.1.1版本，官网下载的

总数语句不支持groupBy

houdongen

xadmin 发表于 2023-10-7 09:35
总数语句不支持groupBy

SELECT count(xid) FROM QRY_DYN_ORDERINFO(最左的表)，这样也不对，class java.lang.Integer cannot be cast to class java.lang.Long java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap)