我一辈子都不知道如何结合这两个 MYSQL 查询。 下面代码的唯一区别是它们选择单独的字段并将它们分配给单独的别名。我尝试使用 WITH 子句,但无法弄清楚。 任何帮助将不胜感激。
(
SELECT TOP 1 ArticlePath
FROM [blog].[TBL_BlogArticles] suba WITH (NOLOCK)
INNER JOIN [blog].[TBL_BlogArticlesLanguages] subl WITH (NOLOCK) ON subl.LanguageCode = @LanguageCode AND subl.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogArticleCategory] subac WITH (NOLOCK) ON subac.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogCategories] subc WITH (NOLOCK) ON subc.CategoryID = subac.CategoryID
WHERE subc.BlogID = @BlogID AND [TimeStamp] < l.[TimeStamp] AND Enabled = 1
ORDER BY a.Priority DESC, [TimeStamp] DESC
)
AS PreviousArticlePath,
(
SELECT TOP 1 ArticleTitle
FROM [blog].[TBL_BlogArticles] suba WITH (NOLOCK)
INNER JOIN [blog].[TBL_BlogArticlesLanguages] subl WITH (NOLOCK) ON subl.LanguageCode = @LanguageCode AND subl.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogArticleCategory] subac WITH (NOLOCK) ON subac.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogCategories] subc WITH (NOLOCK) ON subc.CategoryID = subac.CategoryID
WHERE subc.BlogID = @BlogID AND [TimeStamp] < l.[TimeStamp] AND Enabled = 1
ORDER BY a.Priority DESC, [TimeStamp] DESC
)
AS PreviousArticleTitle,
如果您尝试将其放在同一行上,只需添加 SELECT,例如:
SELECT 1 AS PreviousArticlePath, 2 AS PreviousArticleTitle
结果
PreviousArticlePath PreviousArticleTitle
1 2
1 和 2 将是您的子查询
对于您的查询将是
SELECT
(
SELECT TOP 1 ArticlePath
FROM [blog].[TBL_BlogArticles] suba WITH (NOLOCK)
INNER JOIN [blog].[TBL_BlogArticlesLanguages] subl WITH (NOLOCK) ON subl.LanguageCode = @LanguageCode AND subl.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogArticleCategory] subac WITH (NOLOCK) ON subac.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogCategories] subc WITH (NOLOCK) ON subc.CategoryID = subac.CategoryID
WHERE subc.BlogID = @BlogID AND [TimeStamp] < l.[TimeStamp] AND Enabled = 1
ORDER BY a.Priority DESC, [TimeStamp] DESC
) AS PreviousArticlePath,
(
SELECT TOP 1 ArticleTitle
FROM [blog].[TBL_BlogArticles] suba WITH (NOLOCK)
INNER JOIN [blog].[TBL_BlogArticlesLanguages] subl WITH (NOLOCK) ON subl.LanguageCode = @LanguageCode AND subl.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogArticleCategory] subac WITH (NOLOCK) ON subac.ArticleID = suba.ArticleID
INNER JOIN [blog].[TBL_BlogCategories] subc WITH (NOLOCK) ON subc.CategoryID = subac.CategoryID
WHERE subc.BlogID = @BlogID AND [TimeStamp] < l.[TimeStamp] AND Enabled = 1
ORDER BY a.Priority DESC, [TimeStamp] DESC
) AS PreviousArticleTitle
结果
PreviousArticlePath PreviousArticleTitle
"Some ArticlePath value" "Some ArticleTitle value"