MySQL查询问题 [英] Mysql query problems

问题描述

我有三个表，一个表中包含有关客户端的信息(name，user name)，另一个表中执行了服务的是car(points(由于该服务而获得)，service made)和另一个由公司代表客户工作的地方(name，location).
我正在尝试进行查询，以汇总公司客户的工作，客户以及所获得的所有积分的总和.
我已经尝试了几种方法，但是都失败了.其中一些使我重复行，而另一些则丢失.
我想显示客户信息，即使它没有任何意义.

谁能帮我吗?

这是我已经尝试过的:

I have three tables one with the information about the client (name, user name), another table with the service performed to is car (points (obtained because of the service), service made) and another with the company representing where the client works(name, location).
I''m trying to make a query to get together the company client works, the client, and the sum of all the points obtained.
I already tried several approaches, but all are failing. Some of them gets me duplicated rows and others missing.
I would like to display a client information even if it doesn''t have any points.

Can anyone help me please?

This is what I already tried:

SELECT *FROM client c
         INNER JOIN company b ON c.company_idcompany  = b.idcompany
         INNER JOIN (select sum(pointos) as pointos From services) d

SELECT *FROM client c
        INNER JOIN company b ON c.company_idcompany  = b.idcompany
        INNER JOIN service d ON c.idclient = d.client_idclient

推荐答案

类似以下的内容应该会有所帮助(+-一些语法错误:))

Something like below should help (+- some syntax errors :))

SELECT
   c.*,
   b.*,
   (SELECT SUM(pointos) FROM services WHERE services.client_idclient = c.client_idclient)  sum_points
FROM client c
LEFT JOIN company b ON c.company_idcompany  = b.idcompany

您需要一个UNION:子查询将那些拥有积分的客户的积分汇总，另一个查询将获得没有积分的客户.

You need a UNION: a sub-query sums up the points for those clients having points, and another query gets the clients without points.

SELECT client.name as clientname, company.name as companyname, company.location, SUM(pointos) as points
FROM client 
INNER JOIN company ON client.company_idcompany  = company.idcompany
INNER JOIN service ON client.idclient = service.client_idclient
GROUP BY client.name

UNION

SELECT client.name as clientname, company.name as companyname, company.location, 0 as points
FROM client
INNER JOIN company ON client.company_idcompany  = company.idcompany
LEFT JOIN service ON client.idclient = service.client_idclient
WHERE service.client_idclient IS NULL

这篇关于MySQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！