我有这个查询
SELECT a.datacadastro, <br>
e.nome AS solicitante, <br>
b.nome AS servico, <br>
d.valorservico, <br>
c.idlabpedido, <br>
f.dente <br>
FROM labpedido a <br>
INNER JOIN labservico b <br>
INNER JOIN labpedidoservico c
ON c.idlabservico = b.id
AND a.id = c.idlabpedido <br>
INNER JOIN labservicovalor d
ON (
c.idlabservico = d.idlabservico) <br>
INNER JOIN usuarios e
ON (
a.usuarioinsert = e.id) <br>
INNER JOIN labpedidoservicodente f
ON (
a.id = f.idlabpedido)<br>
where b.status='1' <br>
ORDER BY a.datacadastro<br>
它返回我需要的所有参数,除了"valoreServico"列。
我希望这一行是所有行的总和
143.00是每个"凹痕"的价格,因为我有6行,它应该会退还我8580.00(143.00*6)。
实现这一目标的最佳方式是什么?
它返回:
dataCadastro|solicitante|servico|valoServico|idLabpedido|dente
201701-30 nbsp nbsp nbsp;罗德里戈 nbsp nbsp nbsp 4 nbsp nbsp nbsp nbsp nbsp 143.00 nbsp nbsp nbsp nbsp nbsp;518 nbsp nbsp nbsp nbsp nbsp nbsp nbsp 13
2017-01-30 nbsp nbsp nbsp;罗德里戈 nbsp nbsp nbsp 4 nbsp nbsp nbsp nbsp nbsp 143.00 nbsp nbsp nbsp nbsp nbsp;518 nbsp nbsp nbsp nbsp nbsp nbsp nbsp 14
2017-01-30 nbsp nbsp 罗德里戈 nbsp nbsp nbsp 4 nbsp nbsp nbsp nbsp nbsp 143.00 nbsp nbsp nbsp nbsp nbsp;518 nbsp nbsp nbsp nbsp nbsp nbsp nbsp 15
我认为最简单的方法是:
select a.dataCadastro,
e.nome as solicitante,
b.nome as servico,
(select SUM(valorServico) from labservicovalor where idLabServico=c.idLabServico) as valorServico,
c.idLabPedido,
f.dente
from
Iabpedido a
inner join labservico b
inner join labpedidoservico c on c.idLabServico = b.id and e.id = c.idLabPedido
inner join usuorios e on (a.usuorioInsert = e.id)
inner join labpedidoservicodente f on (a.id = f.idLabPedido)
where
b.status='1'
order by a.dataCadostro