mysql - 如何编写存储过程

Question

我正在尝试写一个sp。我的要求如下:

表A:

Id 是主键。

+----+---------+| Id |  Col1   |+----+---------+|  1 | Sample1 ||  2 | Sample2 |+----+---------+

表B:

Id 是主键。Col1_Id是TableA的外键

+----+---------+-------+| Id | Col1_Id | Col2  |+----+---------+-------+|  1 |       1 | TestA ||  2 |       1 | TestB ||  3 |       2 | TestC |+----+---------+-------+

表C:

Id 是主键。

+----+------------------------+| Id |     QusetionText       |+----+------------------------+|  1 | Sample Question One?   ||  2 | Sample Question Two?   ||  3 | Sample Euestion Three? |+----+------------------------+

我的输出应该是；表D:

Id 是主键。Col1_Id是TableA的外键Col2_Id是TableB的外键

+----+------------+---------+---------+------------------------+| Id | QuestionId | Col1_Id | Col2_Id |      QusetionText      |+----+------------+---------+---------+------------------------+|  1 | Q_2013     |       1 |       1 | Sample Question One?   ||  2 | Q_2013     |       1 |       1 | Sample Question Two?   ||  3 | Q_2013     |       1 |       1 | Sample Question Three? ||  4 | Q_2013     |       1 |       2 | Sample Question One?   ||  5 | Q_2013     |       1 |       2 | Sample Question Two?   ||  6 | Q_2013     |       1 |       2 | Sample Question Three? ||  7 | Q_2013     |       2 |       3 | Sample Question One?   ||  8 | Q_2013     |       2 |       3 | Sample Question Two?   ||  9 | Q_2013     |       2 |       3 | Sample Question Three? |+----+------------+---------+---------+------------------------+

请告诉我如何编写查询来获取TableD。

提前感谢大家的回复。

Answer 1

看看这个，

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Prathiba
-- Create date: 
-- Description: 
-- =============================================
 CREATE PROCEDURE SampleProc 

 AS
 BEGIN
SET NOCOUNT ON;
           SELECT dbo.TableA.Id, dbo.TableB.Col1_Id, dbo.TableC.Id AS Col2_Id, dbo.TableC.QuestionText
           FROM   dbo.TableA INNER JOIN
                  dbo.TableB ON dbo.TableA.Id = dbo.TableB.Col1_Id CROSS JOIN
                  dbo.TableC
 END
 GO

请确保与您的TableC没有关系，即为什么使用交叉连接。