使用较少的查询检索具有相关标签的博客文章列表

发布于 2024-07-12 10:36:28 字数 189 浏览 4 评论 0原文

想象这两个表：

Table: Item
Columns: ItemID, Title

Table: Tag
Columns: TagID, ItemID, Title

这是最好的方法（不改变表结构（是的，我不介意它们是否没有标准化））使用不太可能的查询（即不做 11检索 10 个项目的查询）？

原文

image this two tables:

Table: Item
Columns: ItemID, Title

Table: Tag
Columns: TagID, ItemID, Title

Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10 items)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

春夜浅 2024-07-19 10:36:28

这是一个简单的外部连接。这就是你所追求的吗？

SELECT
  It.ItemID,
  It.Title [ItemTitle],
  Tg.TagID,
  Tg.Title [TagTitle]
FROM Item It
LEFT OUTER JOIN Tag Tg
ON It.ItemID = Tg.ItemID

Here's a simple outer join. Is that what you are after?

SELECT
  It.ItemID,
  It.Title [ItemTitle],
  Tg.TagID,
  Tg.Title [TagTitle]
FROM Item It
LEFT OUTER JOIN Tag Tg
ON It.ItemID = Tg.ItemID

回复收藏 0 原文

鲸落 2024-07-19 10:36:28

我不完全确定你在追求什么，但这有帮助吗？

select Item.ItemID, Item.Title, Tag.TagID, Tag.Title from Item, Tag 
  where Item.ItemID=Tag.ItemID

当您说您不想执行 11 个查询时，您的意思是您不想执行 11 个 SQL 查询，还是您不想收到 10 行结果？如果是后者，那么我认为这实际上只是意味着您需要以您用来调用 SQL 的任何语言循环遍历结果。例如，在 PHP 中：

$query = "select Item.ItemID as i, Item.Title as t1, Tag.TagID as t, Tag.Title as t2 from Item, Tag where Item.ItemID=Tag.ItemID";
$dataset = mysql_query($query) or die(mysql_error());

$items = Array();

while ($data = mysql_fetch_assoc($dataset))
{
  $items[$data['i']] = Array($data['t1'], $data['t'], $data['t2']);
}

I'm not entirely sure what you're after, but does this help?

select Item.ItemID, Item.Title, Tag.TagID, Tag.Title from Item, Tag 
  where Item.ItemID=Tag.ItemID

When you say you don't want to do 11 queries, do you mean you don't want to do 11 SQL queries, or you don't want to receive your results in 10 rows? If the latter, then I think it really just means you need to loop through the results in whatever language you're using to call SQL. E.g., in PHP:

$query = "select Item.ItemID as i, Item.Title as t1, Tag.TagID as t, Tag.Title as t2 from Item, Tag where Item.ItemID=Tag.ItemID";
$dataset = mysql_query($query) or die(mysql_error());

$items = Array();

while ($data = mysql_fetch_assoc($dataset))
{
  $items[$data['i']] = Array($data['t1'], $data['t'], $data['t2']);
}

回复收藏 0 原文

~没有更多了~