我想进行特定查询以将有关2个表的特定信息分组

2024-05-21 • 问答

所以我有2个看起来像这样的表：

Factory
| name | timeoperating | operatedpieces |

Pieces
| serial | piecetype | unit (foreign key referencing factory.name) |

“部件”中的“单位”是引用“工厂”中“名称”的外键。我想查询一个包含“名称”，“时间操作”，“操作件”的查询，然后再查询9列，每种类型（从P1到P9）有多少件连接了“单位”或“名称”的列。当然，每行一个名字。

我已经尝试了许多不同的方法，但是我从未得到过查询以返回与我想要的东西相似的东西，甚至有可能吗？

输出（第一行）：

| factory.name | factory.timeoperating | factory.operatedpieces |
  count(pieces.piecetype) where piecetype=P1 and unit=factory.name | 
  (then other 8 columns like the last one 
   but with piecetype = Px where X is the column number)

啊，我怕我自己不够清楚...

select f.name,f.timeoperating,f.operatedpieces,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 1) as c1,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 2) as c2,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 3) as c3,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 4) as c4,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 5) as c5,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 6) as c6,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 7) as c7,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 8) as c8,(select count(*) from pieces p where p.unit = f.name and p.piecetype = 9) as c9 from factory f

我想进行特定查询以将有关2个表的特定信息分组

Robert1973 回答：我想进行特定查询以将有关2个表的特定信息分组

大家都在问