获取表A中对表B的引用的行数 [英] Get count of rows in table A that have a reference to table B

问题描述

我剩下两个表，即设备"和单位".设备定义了设备的类型，单元是一个唯一的设备，它指向设备表中的一行，定义了设备的类型.

I am left joining two tables, 'device' and 'unit'. Device defines the type of device and an unit is an unique device that points to a row in the device table, defining its type.

我现在正在寻找一种解决方案，以从设备"中选择每种设备类型，并计算指向该设备的设备数量.我目前的解决方案中遇到的问题，

I am now searching for a solution to select every device type from 'device' and counting how many units are there pointing to this device. The problem I am confronted with in my current solution,

SQL :

SELECT device.*, COUNT(unit.id) 
FROM device LEFT JOIN unit ON device.id = unit.device_id GROUPBY device.id

Scala Slick :

def devicesWithUnitCount = for {
    (device, unit) <- TableQuery[TDDevice] joinLeft TableQuery[TDUnit] 
                                           on (_.id === _.deviceID) 
                                           groupBy (_._1)
} yield (device, unit.size)

即使没有单位指向设备类型，我得到的计数结果至少为1.

is that I get a count result of at least 1, even if there is no unit pointing to the device type.

如何在SQL中或最好在Slick中实现此计数?

How can I achieve this counting in SQL, or preferably Slick?

推荐答案

您的SQL对您要执行的操作是正确的. Slick可能正在生成这样的查询:

Your SQL is correct for what you want to do. Slick is probably producing a query like this:

SELECT device.*, COUNT(device.id) 
-----------------------^
FROM device LEFT JOIN
     unit
     ON device.id = unit.device_id
GROUP BY device.id;

也许有一种方法可以对device_id进行计数，这与unit毫无疑问.

Maybe there is a way to get it to count device_id, which is unamibiguously from unit.

这篇关于获取表A中对表B的引用的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！