sql - SQL 存储过程多维查询

Question

我有两个数据库表 LandGradingData 和 LandGradingImages。LandGradingData 有一个唯一的 id 列（称为 id），LandGradingImages 有一个称为landGradingID 的列，它引用 LandGradingData 的 id 列

土地分级数据

+----+----------+----------+
| id | column a | column b |
+----+----------+----------+
|    |          |          |
+----+----------+----------+
|    |          |          |
+----+----------+----------+
|    |          |          |
+----+----------+----------+

土地分级图像

+-----------------------+------------+---------------+
| LandingGradingImageID | ImageBytes | landGradingID |
+-----------------------+------------+---------------+
|                       |            |               |
+-----------------------+------------+---------------+
|                       |            |               |
+-----------------------+------------+---------------+
|                       |            |               |
+-----------------------+------------+---------------+

我创建了一个从 LandGradingData 获取数据的存储过程，现在我想在 LandGradingImages 中添加 landGradingID = id

这就是我卡住的地方，因为 LandGradingImages 将返回landGradingID = id 的多行，从 LandGradingData 返回所有数据以及 LandGradingImages 相关行的最佳方法是什么

这是我到目前为止得到的

ALTER PROCEDURE [dbo].[LandGradingProcedure]
    -- Add the parameters for the stored procedure here
    @communityDesc varchar(255) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT id, columnA, columnB From LandGradingData
END

我将在 ASP.NET MVC 中调用这个存储过程，而我的 .NET 需要一个数组

public List<Reports> LandGradingReport(string community)
        {
            List<Reports> landGrading = new List<Reports>();

            try
            {
                using (connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("LandGradingProcedure", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        SqlParameter parameter1 = new SqlParameter("@communityDesc", SqlDbType.VarChar);
                        parameter1.Value = community;
                        parameter1.Direction = ParameterDirection.Input;
                        command.Parameters.Add(parameter1);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Reports item = new Reports();

                                item.id = reader.GetValue(0);
                                item.columnA = reader.GetValue(1).ToString().Trim();
                                item.columnB = reader.GetValue(2).ToString().Trim();
                                item.images = reader.GetValue(3).ToString().ToArray();

                                landGrading.Add(item);

                            }
                        }
                    }
                }
                return landGrading;
            }
            finally
            {
                connection.Close();
            }
        }

这是我的课：

public class Reports
 {
    public int id { get; set; }
    public string columnA { get; set; }
    public string columnB { get; set: }
    public char[] images { get; set; }
}

任何帮助或建议将不胜感激。

标签： sqlsql-serverstored-procedures

Answer 1

这不起作用吗？

SELECT a.ID, a.ColumnA, a.ColumnB, b.LandingGradingImageID, b.ImageBytes
FROM LandGradingData a
INNER JOIN LandGradingImages b
on a.id = b.landGradingID