来自相关表的mysql记录计数

c7rzv4ha 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(301)

我想从相关的表中得到一个记录数。我可以通过中断查询和合并数组来实现所需的结果，但我知道这样做效率很低。
我正在寻找一个更清洁和有效的方法来做这件事。所有的table都是一对多的。示例：-一个用户有多个对象-一个对象有多个项

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    |

这就是我陷入困境的地方：

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'

sql mysql Join

来源：https://stackoverflow.com/questions/52032962/mysql-record-count-from-related-tables

4条答案

按热度按时间

brjng4g31#

获得所需结果的一种快速方法是使用相关子查询：

select (select count(*) 
        from Objects 
        where UID = 'U1') as Objects,
       (select count(*)
        from Items as i
        inner join Objects as o on i.ObjID = o.ObjID
        where o.UID = 'U1') as Items

另一种方法是创建每个对象项的派生表，并连接到此表以获取每个用户项的总数：

SELECT COUNT(*) AS Objects, SUM(cnt_per_oID) AS Items
FROM Users AS u
JOIN Objects AS o ON u.UID = o.UID
LEFT JOIN 
(
   SELECT ObjID, COUNT(*) cnt_per_oID
   FROM Items
   GROUP BY ObjID
) AS i ON o.ObjID = i.ObjID
WHERE u.UID = 'U1'
GROUP BY u.UID

此处演示

赞(0）回复(0）举报 2021-06-21

pod7payv2#

试试这个：

select count(distinct ObjId) Objects,
       count(distinct ItemId) Items
from Items i
where exists(select 1 from Objects
             where ObjId = i.ObjId and UID = 'U1');

赞(0）回复(0）举报 2021-06-21

3ks5zfa03#

考虑以下几点：

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号

赞(0）回复(0）举报 2021-06-21

oalqel3c4#

试试这个：虽然我不确定你的物品数量

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

赞(0）回复(0）举报 2021-06-20

我来回答

来自相关表的mysql记录计数

4条答案

相关问题

热门标签

最新问答