首页 新闻 赞助 找找看

急:跨数据库查询统计 QQ 2587625886

1
悬赏园豆:80 [已关闭问题] 关闭于 2011-09-19 14:55

查询出每个社区医院发送短信的数量

数据库:SMS  HealthService

涉及到的数据表:HealthService数据库下面的表 ComHospital 社区医院表,Users 居民表,Yhry 医生表

        SMS 数据库下面的表  SentRecord 发送短信表,SMSClass 短信分类表

业务关系:

先从SMS数据库中的SentRecord 中查询出数据,判断数据中的Class_ID(从MSMClass中有分类Class_Name 判断它是居民还是医生,用Class_ID固定死判断它是居民还是医生也行,)是居民用户还是医生用户,如果是医生根据Send_User从HealthService数据库中查询Yhry医生表中的对应ID这一列数据,这一列数据中有一个字段yljgdm,在根据yljgdm中的数据查询出ComHospital 社区医院表 中comhospital_yljgdm 字段中的数据 和comhospital_name 社区医院名称   如果根据Class_Id判断是居民 就从Users表中查询出 ID 这一列数据,这一列数据中的 comhospital_yljgdm 字段中的数据

最后是要统计出每家医院中都用户(包括居民和医生用户)短信都发送量显示如下:

实现的效果:

 

讲武堂一书生的主页 讲武堂一书生 | 初学一级 | 园豆:74
提问于:2011-09-14 10:52
< >
分享
所有回答(4)
0

sql支持跨数据库连接查询的,建议先看下几个数据库之间的网络状况再决定解决方案。

today4king | 园豆:3499 (老鸟四级) | 2011-09-14 11:29

能帮我写出条SQL出来吗?

支持(0) 反对(0) 讲武堂一书生 | 园豆:74 (初学一级) | 2011-09-14 14:12
0

可以选择使用链接服务器 http://blog.csdn.net/HEROWANG/article/details/3869036

Devin Mao | 园豆:596 (小虾三级) | 2011-09-14 11:39

能帮我写出条SQL出来吗?

支持(0) 反对(0) 讲武堂一书生 | 园豆:74 (初学一级) | 2011-09-14 14:13
0
SELECT FROM [服务器].[数据库].[架构].[表或视图或其他对象名]
dotNetDR_ | 园豆:2078 (老鸟四级) | 2011-09-15 22:24
1

最后统计根据YLJGDM统计:

        public DataTable dtComHospital = new DataTable();

        /// <summary>
        /// 短信统计
        /// </summary>
        private void BindChildProtectedSMSStatistice()
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("select  count(*) as totalcount,send_user,UserFlag from dbo.SentRecord inner join  dbo.SMSClass on SentRecord.Class_ID=dbo.SMSClass.Class_ID ");
            strSql1.Append("group by Send_User,UserFlag");
            DataTable dtSMS = db.Query(strSql1.ToString()).Tables[0];
            dtComHospital.Columns.Add(new DataColumn("comhospital_yljgdm",typeof(string)));
            dtComHospital.Columns.Add(new DataColumn("comhospital_count",typeof(int)));

            foreach (DataRow dr in dtSMS.Rows)
            {
                StringBuilder strsql2 = new StringBuilder();

                if (dr["USerFlag"].ToString() == "1")
                {
                    strsql2.Append("select users.ID,users.comhospital_yljgdm as yljgdm,comhospital.comhospital_name from Users left join ComHospital on users.comhospital_yljgdm=ComHospital.comhospital_yljgdm where users.id='" + dr["Send_User"] + "'");
                }
                else if(dr["UserFlag"].ToString()=="0")
                {
                    strsql2.Append("select yhry.ID,yhry.yljgdm,comhospital.comhospital_name from Yhry left join ComHospital on yhry.yljgdm=ComHospital.comhospital_yljgdm  where yhry.id='" + dr["Send_User"] + "'");
                }
                DataTable dt = sqlU.Query(strsql2.ToString()).Tables[0];

                if (dt != null && dt.Rows.Count > 0)
                {
                    string yljgdm=dt.Rows[0]["yljgdm"].ToString();
                    DataRow drowComHospital=dtComHospital.NewRow();
                    if (dtComHospital.Rows.Count > 0)
                    {
                        if (String.IsNullOrEmpty(yljgdm))
                        {
                            continue;//跳出本次循环,继续下次循环
                        }
                        //查找是否存在此yljgdm,如果存在,更新此yljgdm的统计数量
                        DataRow[] row=dtComHospital.Select(" comhospital_yljgdm=" + yljgdm + "");
                        if (row.Length == 0)
                        {
                            drowComHospital["comhospital_yljgdm"] = yljgdm;
                            drowComHospital["comhospital_count"] = dr["totalcount"];
                            dtComHospital.Rows.Add(drowComHospital);
                        }
                        else
                        {
                            row[0].BeginEdit();
                            row[0]["comhospital_count"] = (Convert.ToInt32(row[0]["comhospital_count"]) + Convert.ToInt32(dr["totalcount"])).ToString();
                            row[0].EndEdit();
                        }
                    }
                    else
                    {
                        drowComHospital["comhospital_yljgdm"] = yljgdm;
                        drowComHospital["comhospital_count"] = dr["totalcount"];
                        dtComHospital.Rows.Add(drowComHospital);
                    }
                }
            }
            rptSMSStatistics.DataSource = dtComHospital;
            rptSMSStatistics.DataBind();
        }

 

      再根据yljgdm获取医院名称

   /// <summary>
        /// 获取社区医院名称
        /// </summary>
        /// <param name="comhospital_yljgdm"></param>
        /// <returns></returns>
        public string GetComHospitalName(string comhospital_yljgdm)
        {
            string comhospital_name = "";
            if (comhospital_yljgdm != "")
            {
                string strsql = "select comhospital_name from ComHospital where comhospital_yljgdm='" + comhospital_yljgdm + "'";
                DataTable dt = sqlU.Query(strsql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    comhospital_name = dt.Rows[0]["comhospital_name"].ToString();
                }
                else
                {
                    comhospital_name = "卫生局";
                }
            }
            return comhospital_name;
        }

页面显示:

        <td align="center" class="by1">
                                                <%#GetComHospitalName(Eval("comhospital_yljgdm").ToString())%>
                                                </td>
                                                <td align="center" class="by1">
                                                   <asp:Label runat="server" ID="lblTotalCount" Text='<%#Eval("comhospital_count")%>'></asp:Label>
                                                </td>

讲武堂一书生 | 园豆:74 (初学一级) | 2011-09-19 14:54
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册