| 网站首页 | 业界新闻 | 小组 | 交易 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
共有 1040 人关注过本帖
标题:如何让六段SQL查询整合在一起?
只看楼主 加入收藏
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
结帖率:97.5%
收藏
 问题点数:0 回复次数:5 
如何让六段SQL查询整合在一起?
因为每段SQL查询到的资料是下一段查询语句的条件,再加上必须跑回圈去执行?
边查资料边插入资料。

1. mySQL = "Select MAX(ProjectNo) AS MaxProjectNo From tblProject Where ProjectNo Like '" & strPrefix & strSerialYearMonth & "%'" '(取得ProjectNo (Prefix + YYYYMM + SSS 共10码, eg: S201109001))

2. mySQL1 = "SELECT ROW_NUMBER() OVER (ORDER BY C.CateMainID) AS RowNo,A.ProjectNo FROM tblProjectDevpHistory AS A "
   mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
   mySQL1 += "INNER JOIN tblBasCategory AS C ON B.CateMainID = C.CateMainID "
   mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
   mySQL1 += "WHERE A.DHDate >= '2020/05/01' And A.DHDate <= '2020/05/31' And A.ProjectNo < 'S2020' "
   mySQL1 += "GROUP BY A.ProjectNo,C.CateMainID" '(取得ProjectNo List 2020/05/01 ~ 2020/05/31) - 共10多笔

3. mySQL2 += "SELECT A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo "
   mySQL2 += "FROM tblProject AS A "
   mySQL2 += "INNER JOIN tblBasCategory AS B ON A.CateMainID = B.CateMainID "
   mySQL2 += "INNER JOIN tblProjectItem AS C ON A.ProjectNo = C.ProjectNo "
   mySQL2 += "INNER JOIN tblProjectDevpHistory AS D ON A.ProjectNo = D.ProjectNo AND C.ItemNo = D.ItemNo AND D.DHDate >= '2020/05/01' AND D.DHDate <= '2020/05/31' "
   mySQL2 += "WHERE A.ProjectNo = '" & myDR("ProjectNo").ToString & "' AND A.ProjectNo < 'S2020' "
   mySQL2 += "GROUP BY A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo" '(依据2取得的ProjectNo查询Project基本资料)

4. mySQL = "Insert Into tblProject (ProjectNo, CateMainID, CateSubID, ProjectName, KickOffDate, PlanDate, DepartmentNo, CreatorNo) "
   mySQL += "Values ('" & strNewProjectNo & "','" & myDR("CateMainID").ToString & "','" & myDR("CateSubID").ToString & "','" & myDR("ProjectName").ToString & "','" & myDR("KickOffDate").ToString & "','" & myDR("PlanDate").ToString & "','"
            & myDR("DepartmentNo").ToString & "','" & myDR("CreatorNo").ToString & "') "

5. mySQL3 += "SELECT A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo "
   mySQL3 += "FROM tblProjectItem AS A"
   mySQL3 += "INNER JOIN tblProjectDevpHistory AS B ON A.ProjectNo = B.ProjectNo AND A.ItemNo = B.ItemNo AND B.DHDate >= '2020/05/01' AND B.DHDate <= '2020/05/31'"
   mySQL3 += "WHERE A.ProjectNo= '" & myDR2("ProjectNo").ToString & "' AND A.ProjectNo < 'S2020'"
   mySQL3 += "GROUP BY A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo" '(依据2取得的ProjectNo查询Item基本资料) - 笔数不定

6. mySQL = "Insert Into tblProject (ProjectNo, ItemID, ItemName, DepartmentNo, PropertyDesc, KickOffDate, PlanDate, EngineerNo, AdvisorNo, CreatorNo) "
   mySQL += "Values ('" & strNewProjectNo & "','" & myDR("ItemID").ToString & "','" & myDR("ItemName").ToString & "','" & myDR("DepartmentNo").ToString & "','" & myDR("PropertyDesc").ToString & "','" & myDR("KickOffDate").ToString & "','"
            & myDR("PlanDate").ToString & "','" & myDR("EngineerNo").ToString & "','" & myDR("AdvisorNo").ToString & "','" & myDR("CreatorNo").ToString & "') " (1+3+4+5+6须依据2跑回圈执行)

还是有人能将六段SQL整合成一段SQL语法执行?
写到3层While myDR.Read ... End While回圈,总觉得不太对,上​​来问问。
搜索更多相关主题的帖子: ToString 查询 And JOIN INNER 
2020-05-14 15:47
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
收藏
得分:0 
我也会再想想有没有其他写法,也许我会比版上高手快想出来也不一定。

不要投我
2020-05-14 15:52
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
收藏
得分:0 
语法可以写成这样吗?查询时又同时执行指令,一层一层的叠上去?
程序代码:
                mySQL="..."
                mySQL1="..."
                mySQL2="..."
                myCnn.Open()
                Dim myCmd As SqlCommand
                myCmd = New SqlCommand(mySQL, myCnn)
                Using myDR As SqlDataReader = myCmd.ExecuteReader
                    While myDR.Read
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL1, myCnn)
                        Using myDR1 As SqlDataReader = myCmd.ExecuteReader
                            While myDR1.Read
                                .....
                                myCmd = New SqlCommand(mySQL2, myCnn)
                                myCmd.ExecuteNonQuery()

                                myCnn.Open()
                                myCmd = New SqlCommand(mySQL3, myCnn)
                                Using myDR2 As SqlDataReader = myCmd.ExecuteReader
                                    While myDR2.Read
                                        ....
                                    End While
                                End Using
                            End While
                        End Using
                    End While
                End Using

不要投我
2020-05-14 16:04
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
收藏
得分:0 
要不是为了整体写法一致,想要都使用SQL语法处理的话,
用阵列和For回圈,基本上直接就能解决了,无难度

不要投我
2020-05-14 18:47
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
收藏
得分:0 
一个按钮的功能,大概是这种感觉吧,透过大脑运算后,逻辑上应该差不多是这样写。
只是用上了非SQL方式完成,多声明了好多变量,感觉蛮丑的。希望能征求更简洁的写法。
myCnn一直.Open()没做.Close()不知道会不会有问题?

程序代码:
    '年度转移去年度未完成的Project成今年度新的Project(一年做一次)
    Protected Sub btnAdd1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd1.Click
        If CInt(Session("LevelSD")) > 99 Then
            Dim iYear As String = Format(Now, "yyyy")
            Dim strSerialYearMonth As String = String.Format("{0:yyyyMM}", Now)
            Dim mySQL As String = ""
            Dim mySQL1 As String = "" 'Project List
            Dim mySQL2 As String = "" 'Project
            Dim mySQL3 As String = "" 'Project ITEM
            Dim mySQL4 As String = "" '
            Dim strNewProjectNo As String = ""
            Dim Temp() As String = {""}
            Dim Temp1() As String = {""}
            Dim Temp2() As String = {""}
            Dim i As Integer = 0
            Dim j As Integer = 0
            Dim k As Integer = 0
            Dim strPrefix As String = "S"
            Dim myCmd As SqlCommand
            Try
                '取得ProjectNo (Prefix + YYYYMM + SSS 共10码, eg: S201109001)
                mySQL = "Select MAX(ProjectNo) AS MaxProjectNo From tblProject Where ProjectNo Like '" & strPrefix & strSerialYearMonth & "%'"
                '取得ProjectNo List (2020/05/01 ~ 2020/05/31)
                mySQL1 = "SELECT ROW_NUMBER() OVER (ORDER BY C.CateMainID) AS RowNo,A.ProjectNo FROM tblProjectDevpHistory AS A "
                mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
                mySQL1 += "INNER JOIN tblBasCategory AS C ON B.CateMainID = C.CateMainID "
                mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
                mySQL1 += "WHERE A.DHDate >= '2020/05/01' And A.DHDate <= '2020/05/31' And A.ProjectNo < 'S2020' "
                mySQL1 += "GROUP BY A.ProjectNo,C.CateMainID"
                Using myCnn As New SqlConnection(ConfigurationManager.ConnectionStrings(strCnnType & "dbSD").ToString)
                    myCnn.Open()
                    myCmd = New SqlCommand(mySQL, myCnn)
                    Using myDR As SqlDataReader = myCmd.ExecuteReader
                        If myDR.HasRows Then
                            If myDR.Read() Then
                                If Len(myDR("MaxProjectNo").ToString) > 0 Then
                                    strNewProjectNo = strPrefix & strSerialYearMonth & String.Format("{0:000}", CInt(Right(myDR("MaxProjectNo").ToString, 3)) + 1)
                                Else
                                    strNewProjectNo = strPrefix & strSerialYearMonth & "001"
                                End If
                            Else
                                strNewProjectNo = strPrefix & strSerialYearMonth & "001"  '不会发生
                            End If
                        Else
                            strNewProjectNo = strPrefix & strSerialYearMonth & "001"      '不会发生
                        End If
                    End Using
                    myCnn.Open()
                    myCmd = New SqlCommand(mySQL1, myCnn)
                    Using myDR As SqlDataReader = myCmd.ExecuteReader
                        While myDR.Read
                            '储存每个ProjectNo List (2020/05/01 ~ 2020/05/31)
                            ReDim Preserve Temp(j)
                            Temp(j) = myDR("ProjectNo").ToString
                            j += 1
                        End While
                    End Using
                    For i = 0 To UBound(Temp)
                        '取得每个ProjectNo内的资料
                        mySQL2 += "SELECT A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo "
                        mySQL2 += "FROM tblProject AS A "
                        mySQL2 += "INNER JOIN tblBasCategory AS B ON A.CateMainID = B.CateMainID "
                        mySQL2 += "INNER JOIN tblProjectItem AS C ON A.ProjectNo = C.ProjectNo "
                        mySQL2 += "INNER JOIN tblProjectDevpHistory AS D ON A.ProjectNo = D.ProjectNo AND C.ItemNo = D.ItemNo AND D.DHDate >= '2020/05/01' AND D.DHDate <= '2020/05/31' "
                        mySQL2 += "WHERE A.ProjectNo = '" & Temp(i).ToString & "' AND A.ProjectNo < 'S2020' "
                        mySQL2 += "GROUP BY A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo"
                        strNewProjectNo = strNewProjectNo + i '这部分做字串加减,在SQL上是OK,但是在程式上有疑虑,六月初还需再验证
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL2, myCnn)
                        Using myDR As SqlDataReader = myCmd.ExecuteReader
                            If myDR.HasRows Then
                                If myDR.Read() Then
                                    '插入新ProjectNo和资料
                                    mySQL = "Insert Into tblProject (ProjectNo, CateMainID, CateSubID, ProjectName, KickOffDate, PlanDate, DepartmentNo, CreatorNo) "
                                    mySQL += "Values ('" & strNewProjectNo & "','" & myDR("CateMainID").ToString & "','" & myDR("CateSubID").ToString & "','" & myDR("ProjectName").ToString & "','" & myDR("KickOffDate").ToString & "','" & myDR("PlanDate").ToString & "','" & myDR("DepartmentNo").ToString & "','" & myDR("CreatorNo").ToString & "') "
                                End If
                            End If
                        End Using
                        '取得每个ProjectNo内每个ITEM和资料
                        mySQL3 += "SELECT A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo "
                        mySQL3 += "FROM tblProjectItem AS A"
                        mySQL3 += "INNER JOIN tblProjectDevpHistory AS B ON A.ProjectNo = B.ProjectNo AND A.ItemNo = B.ItemNo AND B.DHDate >= '2020/05/01' AND B.DHDate <= '2020/05/31'"
                        mySQL3 += "WHERE A.ProjectNo= '" & Temp(i).ToString & "' AND A.ProjectNo < 'S2020'"
                        mySQL3 += "GROUP BY A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo"
                        j = 0
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL3, myCnn)
                        Using myDR As SqlDataReader = myCmd.ExecuteReader
                            While myDR.Read
                                '储存每个ProjectNo内每个ITEM和资料
                                ReDim Preserve Temp1(j)
                                Temp1(j) = Temp(i).ToString & "," & myDR("ItemNo").ToString & "," & myDR("ItemName").ToString & "," & myDR("DepartmentNo").ToString & "," & myDR("PropertyDesc").ToString & "," & _
                                           myDR("KickOffDate").ToString & "," & myDR("PlanDate").ToString & "," & myDR("EngineerNo").ToString & "," & myDR("AdvisorNo").ToString & "," & myDR("CreatorNo").ToString
                                j += 1
                            End While
                        End Using
                        For j = 0 To UBound(Temp1)
                            k = 0 : Temp2 = Split(Temp1(j), ",")
                            If UBound(Temp2) = 9 Then
                                '写入Item 资料表
                                mySQL4 = "Insert Into tblProjectItem (ProjectNo, ItemNo, Weight, ItemName, DepartmentNo, PropertyDesc, KickOffDate, PlanDate, EngineerNo, AdvisorNo, Consultant, CreatorNo) Values ("
                                mySQL4 += "'" & Temp(i).ToString & "'," & (j + 1) & ",100,'" & Temp2(k + 1) & "','" & Temp2(k + 2) & "','" & Temp2(k + 3) & "','" & Temp2(k + 4) & "','" & Temp2(k + 5) & "','" & _
                                     Temp2(k + 6) & "','" & Temp2(k + 7) & "','" & Temp2(k + 8) & "',Null,'" & Temp2(k + 9) & ")"
                                myCmd = New SqlCommand(mySQL4, myCnn)
                                myCmd.ExecuteNonQuery()

                                'Log 专案新增动作
                                mySQL = "Insert Into tblLogEvent (EventTime, EventType, EventUserNo, EventUserName, EventIP, EventDesc, EventMemo, ProjectNo, SerialNo) Values "
                                mySQL += "(GetDate(), 'A', '" & Session("LoginNo") & "', '" & Session("LoginName") & "', '" & Session("LoginIP") & "', '专案主档', "
                                mySQL += "'Project Add', '" & strNewProjectNo & "', Null)"
                                myCmd = New SqlCommand(mySQL, myCnn)
                                myCmd.ExecuteNonQuery()
                            End If
                            Erase Temp2
                        Next j
                        Erase Temp1
                        '发送通知信
                        Call SendEmail_ProjectItemAdd(strNewProjectNo, 1)
                    Next i
                    Erase Temp
                End Using
            Catch ex As Exception
                'XXXXX
            End Try
        Else
            'XXXXX
        End If
    End Sub


[此贴子已经被作者于2020-5-15 12:49编辑过]


不要投我
2020-05-15 12:44
yz1025
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:6
帖 子:455
专家分:915
注 册:2012-10-26
收藏
得分:0 
上面在Insert Into的时候部分资料有些问题,不过大致上都还好就不再贴了。

strNewProjectNo = strNewProjectNo + i '这部分做字串加减,在SQL上是OK,但是在程式上有疑虑,六月初还需再验证
改为
strNewProjectNo = strPrefix & CStr(CLng(Replace$(strNewProjectNo, strPrefix, "")) + i)

不要投我
2020-05-15 15:46
快速回复:如何让六段SQL查询整合在一起?
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.058219 second(s), 8 queries.
Copyright©2004-2020, BCCN.NET, All Rights Reserved