SQLite Count（*）问题 [英] SQLite Count(*) problem

问题描述

我正在尝试确定/计算与此组件记录关联的附件数量，但如果没有组件，那么我将使用AttachmentCount = 0返回单个记录，而我真正想要的是不获取任何记录不是组件，但是如果有附件，AttachmentCount字段应该反映计数。

我正在尝试做什么或是否有另一种方法这样做？

  SELECT 组件。*，类别。[标题]  as  CategoryStr，子类别。[标题]  as  SubCategoryStr，
 COUNT（*） as  AttachmentCount  FROM 组件
  JOIN 类别 ON  Component。[CategoryId] = Category.ObjectId 
  JOIN 子类别 ON 组件。[SubCategoryId] = Subcategory.ObjectId 
  JOIN 附件 组件。[ObjectId] =附件。[ParentId] 

谢谢

解决方案

试试这个：

  SELECT  Com。*，Cat。[Title]  as  CategoryStr，Sct。[Title]  as  SubCategoryStr， （ SELECT  COUNT（*） FROM 附件 WHERE  ParentId = Com.ObjectID） as  AttachmentCount   
  FROM 组件 AS  Com   LEFT   < span class =code-keyword> JOIN 类别 AS  Cat  ON  Com。[CategoryId] = Cat.ObjectId 
   LEFT    JOIN 子类别 AS  Sct  ON  Com。[SubCategoryId] = Sct.ObjectId 

如果你想只在组件存在的情况下获取数据，你需要改变加入方向;）

看看这里： SQL连接的可视化表示 [ ^ ]

[/ EDIT]



如需了解更多信息，请参阅：

SQLite - 子查询 [ ^ ]

SQLite - ALIAS语法 [ ^ ]

I am attempting to determine/calculate the number of attachments associated with this component record but if there are no Components then I get a single record back with the AttachmentCount = 0 and what I really want is to not get any records if there are no componenta but if there are attachments the AttachmentCount field should reflect the count.
Is what I am trying to do possible or is there another way of doing this?

SELECT Component.*, Category.[Title] as CategoryStr, Subcategory.[Title] as SubCategoryStr, 
                    COUNT(*) as AttachmentCount FROM Component 
                   JOIN Category ON Component.[CategoryId] = Category.ObjectId
                   JOIN Subcategory ON Component.[SubCategoryId] = Subcategory.ObjectId                   
                   JOIN Attachment on Component.[ObjectId] = Attachment.[ParentId]

Thanks

解决方案

Try this:

SELECT Com.*, Cat.[Title] as CategoryStr, Sct.[Title] as SubCategoryStr, (SELECT COUNT(*) FROM Attachment WHERE ParentId = Com.ObjectID) as AttachmentCount
FROM Component AS Com LEFT JOIN Category AS Cat ON Com.[CategoryId] = Cat.ObjectId
    LEFT JOIN Subcategory AS Sct ON Com.[SubCategoryId] = Sct.ObjectId

[EDIT]
If you want to get data only if component exists, you need to change Join direction ;)
Have a look here: Visual Representation of SQL Joins[^]
[/EDIT]

For further information, please see:
SQLite - Sub Queries[^]
SQLite - ALIAS Syntax[^]

这篇关于SQLite Count（*）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！