相关表的Mysql记录数

Question

我正在尝试从相关表中获取记录数。我能够通过中断查询和合并数组来获得我需要的结果，但我知道这是低效的。

我正在寻找一种更清洁、更高效的方法来执行此操作。所有表都是一对多的。例子:-一个用户有很多对象-一个对象有很多项

Table Users ______________| ID   |  UID  | ______________|  1   |  U1   ||  2   |  U2   | ______________

Table Objects ______________| ObjID | UID  | ______________|  o1   |  U1  ||  o2   |  U1  ||  o3   |  U1  |   ______________

Table Items _________________| itemID |  ObjID | _________________|  i1    |  o1    ||  i2    |  o1    ||  i3    |  o1    ||  i4    |  o1    ||  i5    |  o1    ||  i6    |  o2    | _________________

我为 U1 寻找的结果是:

| Objects |  Items  ||   3     |    6    |

这个 sql 是我卡住的地方:

    select count(objects.id), count(items.id)    from users    left join Objects on objects.uid = users.uid    left join Items on items.objID = objects.objID     where users.uid = 'U1'

Answer 1

考虑以下几点:

create table users
(user_id serial primary key,name char(2) unique
);

insert into users values (101,'U1'),(102,'U2');

create table user_objects
(user_id int not null
,object_id int not null
,primary key(user_id,object_id)
);

insert into user_objects values
(101,1),
(101,2),
(101,3);

create table object_items
(object_id int not null
,item_id int not null
,primary key(object_id,item_id)
);

insert into object_items values
(1,1001),
(1,1002),
(1,1003),
(1,1004),
(1,1005),
(2,1001);

select u.name
     , count(distinct uo.object_id) objects
     , count(oi.item_id) items
  from users u
  left
  join user_objects uo 
    on uo.user_id = u.user_id
  left 
  join object_items oi
    on oi.object_id = uo.object_id
group
    by u.user_id;

http://sqlfiddle.com/#!9/d2285/1