JAVA代码里有这样一句SQL语句,我想问一下里面个别 语句 的意思,希望各位不吝赐教!select o.orgid,date_format(o.orderdate,'%Y'),count(*),sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)as totalmoney,sum(case when o.payamount is not NULL then o.payamount else '0.00' end) as paymoney, (sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)-sum(case when o.payamount is not NULL and (case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)!='0.00' then o.payamount else '0.00' end)) as nopaymoney from htglview o where 1=1 and o.contractState in (5,8,9,10,11) and o.contractType='0' group by o.orgid, date_format(o.orderdate,'%Y') order by o.orgid,date_format(o.orderdate,'%Y')
这里用到了hibernate3作为持久层,所以 o.orgid 就是 查询orgid这个字段,然后表的别名是 o , 是这样么? 然后这句 [color=#FF0000]from htglview o [/color] 我不太懂了,我的数据库里没有htglview这个表,那意思就是新建一个htglview的临时表存入select的数据么?
疑问就是这些,谢谢,或者哪位高手能深入的解释一下这段话,感激不尽。
这里用到了hibernate3作为持久层,所以 o.orgid 就是 查询orgid这个字段,然后表的别名是 o , 是这样么? 然后这句 [color=#FF0000]from htglview o [/color] 我不太懂了,我的数据库里没有htglview这个表,那意思就是新建一个htglview的临时表存入select的数据么?
疑问就是这些,谢谢,或者哪位高手能深入的解释一下这段话,感激不尽。
解决方案 »
- 删除sql里面的书名号
- 存储过程定义变量时需要其传入参数加1
- 继续讨论,一条sql语句引发的问题
- 有关mysql数据同步问题,各位请进!!!
- Error:ost 'pt' is not allowed to connect to this MySQL server 请教众高手怎么解决,谢谢!
- 数据类型的问题
- 通过java向Mysql中插入图片
- 这种设计一定要多表关联吗?
- mysql如何将A(172.20.0.1)服务器上A1表的数据导入到B(172.20.1.1)服务器上的B1表中
- MySQL数据库,数据表删除时,删除不了,提示出错。
- mysql 5.1按月建立分区表出现问题
- 大家一般都用什么工具debug Mysql的函数procedure
SELECT o.orgid,
Date_format(o.orderdate, '%Y'),
COUNT(*),
SUM(CASE
WHEN o.finalamount != '0.00' THEN o.finalamount
ELSE o.contractamount
END) AS totalmoney,
SUM(CASE
WHEN o.payamount IS NOT NULL THEN o.payamount
ELSE '0.00'
END) AS paymoney,
( SUM(CASE
WHEN o.finalamount != '0.00' THEN o.finalamount
ELSE o.contractamount
END) - SUM(CASE
WHEN o.payamount IS NOT NULL
AND ( CASE
WHEN o.finalamount != '0.00' THEN
o.finalamount
ELSE o.contractamount
END ) != '0.00' THEN o.payamount
ELSE '0.00'
END) ) AS nopaymoney
FROM htglview o
WHERE 1 = 1
AND o.contractstate IN ( 5, 8, 9, 10, 11 )
AND o.contracttype = '0'
GROUP BY o.orgid,
Date_format(o.orderdate, '%Y')
ORDER BY o.orgid,
Date_format(o.orderdate, '%Y')
什么结果
htglview CREAT ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `htglview` AS select `o`.`ID` AS `id`,`o`.`ContractCode` AS `contractCode`,`o`.`OrgId` AS `orgId`,`o`.`ContractType` AS `contractType`,`o`.`ProjectName` AS `projectName`,`o`.`ContractAmount` AS `contractAmount`,`o`.`FinalAmount` AS `finalAmount`,`o`.`OrderDate` AS `orderDate`,`o`.`ContractState` AS `contractState`,`o`.`Contractcompany` AS `contractcompany`,`custa`.`CustomerName` AS `aname`,`custa`.`Alias` AS `aalias`,`custb`.`CustomerName` AS `bname`,`custb`.`Alias` AS `balias`,(select sum(`contractpay`.`PayAmount`) AS `sum(payAmount)` from `contractpay` where ((`contractpay`.`ContractID` = `o`.`ID`) and (`contractpay`.`State` in (2,4,5))) group by `contractpay`.`ContractID`) AS `payAmount`,(select sum(`contractinvoice`.`InvoiceAmount`) AS `sum(invoiceAmount)` from `contractinvoice` where ((`contractinvoice`.`ContractID` = `o`.`ID`) and (`contractinvoice`.`State` in (2,4,5))) group by `contractinvoice`.`ContractID`) AS `invoiceAmount` from ((`contractinfo` `o` join `customerinfo` `custa`) join `customerinfo` `custb`) where ((1 = 1) and (`o`.`PartyAID` = `custa`.`CustomerId`) and (`o`.`PartyBID` = `custb`.`CustomerId`))