如何使用一个表在一个数据集中接收两个sql语句 [英] How to receive two sql statement in one dataset with one table

问题描述

我制作具有两个sql语句的存储过程，如下所示:

Hi,

I make stored procedure that has two sql statement as like that:

CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent from  Topics where Topics.TopicID=@TopicID

select count(FavoriteID) as BookmarkNum from Favorites where TopicID=@TopicID
GO

我想在具有一个这样的表的一个数据集中接收数据:

ourDataAdapter.Fill(SpecificTopicDataSet, "topic");


但这给了我一个错误:

I want to receive the data in one dataset that has one table like that:

ourDataAdapter.Fill(SpecificTopicDataSet, "topic");


but it gives me an error that:

Column ''BookmarkNum'' does not belong to table topic.

推荐答案

使用ourDataAdapter.Fill(SpecificTopicDataSet);，第一个结果集将在数据集中的第一个数据表中，第二个结果集将在第二个数据表中().
作为替代更改，您的查询如下:

Use ourDataAdapter.Fill(SpecificTopicDataSet); and the first result set will be in the first datatable in your dataset and the second will be in the second datatable(SpecificTopicDataSet.tables(1)).
As an alternate change your query like:

select 
  TopicAddress,TopicContent,
  (select count(FavoriteID) from Favorites where TopicID=@TopicID)
   as BookmarkNum
from Topics 
where Topics.TopicID=@TopicID

这将在一个结果集中获取结果

This will fetch the result in one result set

CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent from  Topics where Topics.TopicID=@TopicID
select count(FavoriteID) as BookmarkNum from Favorites where TopicID=@TopicID
GO

如果我正确插入，那么您想要带有列的结果
1-TopicAddress
2-TopicContent
3-BookMarkNumber(来自收藏夹"表中带有TopicID = @ topicID的主题)

对于此特定问题，您可以使用子查询

If I interperated correctly then You want the result with columns
1 - TopicAddress
2 - TopicContent
3 - BookMarkNumber (of the topic with TopicID=@topicID From Favourite Table)

For this particular Question you can use Sub Query

CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent, 
(select count(FavoriteID) from Favorites where Favorites.TopicID=Topics.TopicID) as BookmarkNum
 from  Topics where Topics.TopicID=@TopicID

GO;

对于eaxmple，此查询可用于获取具有总书签的所有主题(这就是为什么条件"Favorites.TopicID = Topics.TopicID")

For eaxmple This Query can be used to get all the topics with total Bookmarks (thats why the condition "Favorites.TopicID=Topics.TopicID" )

select TopicAddress,TopicContent,
(select count(FavoriteID) from Favorites where Favorites.TopicID=Topics.TopicID) as BookmarkNum
 from  Topics
GO;

为什么不为此使用JOIN.

Hi,

Why you are not using JOIN for this.

这篇关于如何使用一个表在一个数据集中接收两个sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！