mysql - Codeigniter PHP SQL 连接

Question

我有三个表来管理站点、地理围栏和这些站点中的 key 。

站点到地理围栏是一对多的。 Sites to Keys 是一对多的。

    Sites 
       id       int      PK
       name     varchar
       deleted  bool

    Geofences
       id       int      PK
       site_id  int 
       lat      double
       lon      double
       rad      double

    Keys
       id       int      PK
       site_id  int
       deleted  bool

选择语句:

    SELECT *, SUM(IF(kc.deleted = 0, 1, 0)) AS 'KeyCount'
    FROM Keys kc 
    INNER JOIN Geofences g ON kc.id = g.site_id
    INNER JOIN Sites s ON s.id = kc.site_id 
    GROUP BY kc.site_id;

我想计算每个站点中未删除 key 和站点的 key ，并在一条 MySQL 语句中取回每个站点的地理围栏数据。

示例表数据:

Sites -----------------------------------

id     name       deleted
1      site_one   0
2      site_two   0
3      site_three 0

Geofences -----------------------------------

id     site_id    lat     lon     rad
1      1          25.5    -123.4  50            
2      1          22.44   -123.3  50
3      1          20.1    -122.5  50
4      2          27.22   -120.5  50
5      2          28.999  -121.11 50
6      3          29.8    -122    75

Keys -----------------------------------

id     site_id    deleted
1      1          0
2      1          1
3      1          0
4      1          0
5      2          0 
5      3          0

任何帮助都会很棒，我已经尝试了 2 个小时了..

Answer 1

这将为您提供每个站点的键数，仅考虑未删除的站点和键以及地理围栏信息，但对于每个地理围栏，键数和站点信息(ID 和名称)显然(且浪费)重复。

select X.*, g.* from
(
    select s.id as Site_Id, s.name as SiteName, count(kc.id) key_count
    from sites s inner join keys kc on s.Id = kc.site_id
    where s.deleted = 0 and kc.deleted=0
    group by s.Id, S.name
) X inner join GeoFences g on X.Site_Id = g.site_id

这就是我能从您的问题中收集到的全部信息。如果您需要其他内容，请提供示例输出。