时间戳

首页 > 解决方案 > 如何在 C# 中完全外连接或连接最多 6（六）个 SQL Server 数据库表？

sql - 如何在 C# 中完全外连接或连接最多 6（六）个 SQL Server 数据库表？

问题描述

我正在尝试加入 6 个表，如下面的代码所示，并在 SQL Server 数据库的一个 datagridview 中显示所有表。

但我得到这个错误

关键字 JOIN 的语法不正确

这是我尝试过的 - 有人可以帮我解决这个问题：

using(SqlConnection connection = new SqlConnection(shoolmanangmentconn))
{
    string sql = "SELECT s1.FirstName, s1.LastName, " +
                 "s2.EXAMSMATHEMATICS " +
                 "s3.TOTALMATHEMATICS" +
                 "s4.OVERALLTOTALMATHEMATICS" +
                 "s5.POSITIONMATHEMATICS," +
                 "s6.GRADEMATHEMATICS " +
                 "FROM(SELECT FirstName, LastName,ROW_NUMBER() OVER(ORDER BY FirstName)As rn1 FROM tbl_TestingTheApplicationsNAME)as s1 " +
                 "FULL OUTER JOIN(SELECT EXAMSMATHEMATICS,ROW_NUMBER() OVER (ORDER BY EXAMSMATHEMATICS)As rn2 FROM tbl_EXAMSSCORES)as s2 " +
                 "FULL OUTER JOIN(SELECT TOTALMATHEMATICS,ROW_NUMBER() OVER (ORDER BY TOTALMATHEMATICS)As rn3 FROM tbl_TOTALSCORES)as s3 " +
                 "FULL OUTER JOIN(SELECT OVERALLTOTALMATHEMATICS,ROW_NUMBER() OVER (ORDER BY OVERALLTOTALMATHEMATICS)As rn4 FROM tbl_OVERALLSCORES)as s4 " +
                 "FULL OUTER JOIN(SELECT POSITIONMATHEMATICS,ROW_NUMBER() OVER (ORDER BY POSITIONMATHEMATICS)As rn5 FROM tbl_POSITIONSCORES)as s5" +
                 "FULL OUTER JOIN(SELECT GRADEMATHEMATICS,ROW_NUMBER() OVER (ORDER BY GRADEMATHEMATICS)As rn6 FROM tbl_GRADESCORE)as s6 ON s1 ON s5 ON s4 ON s3 ON s2 As s6 ON s1.rn1 = s6.rn6=s5.rn5=s4.rn4=s3.rn3=s2.rn2";

    SqlCommand sqlCommand = new SqlCommand(sql, connection);

    DataTable dataTable = new DataTable();

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
    sqlDataAdapter.Fill(dataTable);

    dgvRESULTPRINTER.ItemsSource = dataTable.DefaultView;
}

标签： sqlsql-servertsql

解决方案

此 SQL 语句有两个主要问题。

  • 您需要ON在每次FULL OUTER JOIN描述数据集的任一侧是FULL OUTER JOIN如何关联的之后，而不是与最后的一系列相关联ON。
FROM
  (SELECT FirstName, LastName, ROW_NUMBER() OVER (ORDER BY FirstName) As rn1
   FROM tbl_TestingTheApplicationsNAME) AS s1
FULL OUTER JOIN
  (SELECT EXAMSMATHEMATICS, ROW_NUMBER() OVER (ORDER BY EXAMSMATHEMATICS) As rn2
   FROM tbl_EXAMSSCORES)as s2
ON s1.rn1 = s2.rn2
  • SELECT您需要在每列之间的语句中使用额外的逗号。
SELECT s1.FirstName, s1.LastName, s2.EXAMSMATHEMATICS, s3.TOTALMATHEMATICS, 
  s4.OVERALLTOTALMATHEMATICS, s5.POSITIONMATHEMATICS, s6.GRADEMATHEMATICS

我不知道这个查询是否有效，因为您没有提供数据库结构，但这应该可以解决您遇到的语法错误。

推荐阅读