c# - 单个SQL查询可处理多个一对多关系，并且仅输出一次结果

我有3个数据库表，它们之间有2个完全独立的一对多关系，我很难编写一个SQL查询来为每条相关信息输出一条记录。

也许最后，由于2个关系是独立的，这意味着我需要编写2个独立的连接和SQL查询，但我想无论如何还是要徒劳地希望有人在那里解决了类似的问题（然后在解决这个问题时，我可以将相同的原理应用于整个数据库中发生的其他单独的一对多关系，并且可以“理想地”创建一个大型查询，而不是创建更多单独的连接/查询。

我到目前为止的查询是：

SELECT C.court_id, court_email_desc, court_email_addr, court_opening_type_desc, court_opening_desc
FROM court C
JOIN court_email CE ON C.court_id = CE.court_id
JOIN court_opening CO ON C.court_id = CO.court_id
JOIN court_opening_type COT ON CO.court_opening_type_id = COT.court_opening_type_id
WHERE C.court_id = '" + court_id_no + "'
ORDER BY C.court_id

它输出以下内容：

查询：[e-mail]
法院大楼开放时间：上午8.30

执达主任：[e-mail]
法院大楼开放时间：上午8.30

听证会：听证会@ hearings.email
法院大楼开放时间：上午8.30

提交电子文档：[e-mail]
法院大楼开放时间：上午8.30

公众咨询电子邮件：[e-mail]
法院大楼开放时间：上午8.30

查询：[e-mail]
法院大楼关闭：下午5.00

执达主任：[e-mail]
法院大楼关闭：下午5.00

等等...（注意：电子邮件地址重复4次，每种开头描述都重复一次）

所需的输出将是这样的：

查询：[e-mail]
执达主任：[e-mail]
听证会：听证会@ hearings.email
提交电子文档：[e-mail]
公众咨询电子邮件：[e-mail]

法院大楼开放时间：上午8.30
法院大楼关闭：下午5.00
电话咨询从：9.00am
电话查询至：5.00pm

有没有一种方法可以我聪明地嵌套查询以输出每个电子邮件地址和打开类型/时间一次（无论我选择显示哪个法院）？在输出结果时，这在SQL查询中是否可能出现？还是在C＃中出现了聪明的地方？

我的C＃代码当前如下所示：

myDataReader3 = myCommand3.ExecuteReader();

        if (myDataReader3.HasRows) {

            string last_id = string.Empty;
            string last_id2 = string.Empty;

            while (myDataReader3.Read()) {

                string court_id = myDataReader3["court_id"].ToString();
                string court_email_desc = myDataReader3["court_email_desc"].ToString();
                string court_email_addr = myDataReader3["court_email_addr"].ToString();
                string court_opening = myDataReader3["court_opening_desc"].ToString();
                string court_opening_type = myDataReader3["court_opening_type_desc"].ToString();

                if (last_id != court_id) {

                    Response.Write("<br><strong>Email address</strong><br>" + court_email_desc + ": " + court_email_addr + "<br>");

                } else {

                    Response.Write(court_email_desc + ": " + court_email_addr + "<br>");

                }

                last_id = court_id;

                if (last_id2 != court_id) {

                    Response.Write("<br><strong>Opening times</strong><br>" + court_opening_type + ": " + court_opening + "<br>");

                } else {

                    Response.Write(court_opening_type + ": " + court_opening + "<br>");

                }

                last_id2 = court_id;

            }

        }

在此先感谢您的宝贵时间和我的帮助。

最佳答案

您应该能够将row_number() over()添加到您的SQL查询中，然后应用CASE语句：

select court_id,
  court_email_desc,
  case when rn=1 then court_email_addr else '' end court_email_addr,
  court_opening_type_desc,
  court_opening_desc
from
(
  SELECT C.court_id,
    court_email_desc,
    court_email_addr,
    court_opening_type_desc,
    court_opening_desc,
    row_number() over(partition by c.court_id order by c.court_id) rn
  FROM court C
  JOIN court_email CE
    ON C.court_id = CE.court_id
  JOIN court_opening CO
    ON C.court_id = CO.court_id
  JOIN court_opening_type COT
    ON CO.court_opening_type_id = COT.court_opening_type_id
  WHERE C.court_id = '" + court_id_no + "'
) src
order by court_id

这样做是在row_number()的原始查询中添加court_id。然后，将CASE语句应用于要返回的字段，以检查row_number是否等于1。如果是，则将显示该列的值。如果不是，那么它将显示一个空字符串。

编辑＃1：如果只希望Open和closed值出现一次，则也可以PIVOT类似于以下的数据：

SELECT c.court_id,
  court_email_desc,
  court_email_addr,
  max(case when court_opening_type_desc = 'Court building open' then court_opening_desc end) Opens,
  max(case when court_opening_type_desc = 'Court building closed' then court_opening_desc end) Closes
FROM court C
JOIN court_email CE
  ON C.court_id = CE.court_id
JOIN court_opening CO
  ON C.court_id = CO.court_id
JOIN court_opening_type COT
  ON CO.court_opening_type_id = COT.court_opening_type_id
WHERE C.court_id = '" + court_id_no + "'
group by c.court_id, court_email_desc, court_email_addr;

编辑＃2：如果您不使用单独的查询，我的最终建议是将row_number()和PIVOT实现为单个解决方案：

SELECT court_id,
  court_email_desc,
  court_email_addr,
  max(case when court_opening_type_desc = 'Court building open' and rn=1 then court_opening_desc end) CourtOpens,
  max(case when court_opening_type_desc = 'Court building closed' and rn=1 then court_opening_desc end) CourtCloses
FROM
(
    SELECT c.court_id,
        court_email_desc,
        court_email_addr,
        court_opening_type_desc,
        court_opening_desc,
        row_number() over(partition by c.court_id, court_opening_type_desc order by court_email_desc) rn
    FROM court C
    JOIN court_email CE
      ON C.court_id = CE.court_id
    JOIN court_opening CO
      ON C.court_id = CO.court_id
    JOIN court_opening_type COT
      ON CO.court_opening_type_id = COT.court_opening_type_id
    WHERE C.court_id = '" + court_id_no + "'
) src
group by court_id, court_email_desc, court_email_addr;

来自查询的数据应显示为：

court_id    | court_email_desc          | court_email_addr                      | CourtOpens    | CourtCloses
------------------------------------------------------------------------------------------------------------------------
1           | Bailiffs                  | [e-mail]               | 8:30am        | 5:00pm
1           | Public Enquiries Email    | [e-mail]   |               |
1           | Filing e-documents        | [e-mail]            |               |
1           | Enquiries                 | [e-mail]             |               |
1           | Hearings                  | [e-mail]               |               |