求大神指导SQL

Question

 Public Function LoadEQPDailyReport(Optional ByVal AreaNo As String = defString, Optional ByVal EquipmentNo As String = defString, _
        Optional ByVal StartTime As Date = defDateTime, Optional ByVal EndTime As Date = defDateTime, Optional ByVal AdditionalXml As String = "") As String
        Dim cnnTemp As OleDb.OleDbConnection
        Dim daTemp As OleDb.OleDbDataAdapter
        Dim dsTemp As DataSet
        Dim drTemp As OleDb.OleDbDataReader
        Dim strSQL As String
        Dim StartEndTime As Double
        Dim Rows() As DataRow
        Try
            '//Create connection
            cnnTemp = objLic.CreateConnection(strConnectionString)
            strSQL = "Select * From tblEQPStateBasis Order By EquipmentState"
            cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
            drTemp = cmmTemp.ExecuteReader()
            strSQL = " Select (Select ContainareaNo From tblSMDAreaRelation Where ObjectNo = Z.EquipmentNo And ObjectType = 2) AreaNo," & _
                     "        (Select EquipmentType From tblEQPEquipmentBasis Where EquipmentNo = Z.EquipmentNo) EquipmentType," & _
                     "        Z.EquipmentNo," & _
                     "        0 Moves"
            StartEndTime = DateDiff(DateInterval.Second, StartTime, EndTime)
            Do While drTemp.Read
                If strDataBaseType = "oracle" Then
            
                    strSQL = strSQL & "       ,Nvl(Round((   Select Sum(Least(A.EndTime,To_Date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')) -" & _
                                      "                                 Greatest(A.StartTime,To_Date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')))*24*60*60" & _
                                      "                        From (Select  EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime From tblEMSEquipmentStateLog" & _
                                      "                              UNION All" & _
                                      "                              Select  EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime From tblEMSEquipmentState" & _
                                      "                             ) A" & _
                                      "                       Where A.StartTime <= to_date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')" & _
                                      "                         And A.EndTime > to_date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')" & _
                                      "                         And A.EquipmentState = " & drTemp("EquipmentState") & _
                                      "                         And A.EquipmentNo = Z.EquipmentNo" & _
                                      "                    Group By A.EquipmentNo, A.EquipmentState)/" & StartEndTime & "*100,2),0) """ & drTemp("StateName") & """"
                ElseIf DataBaseType = "mssql" Or DataBaseType = "msaccess" Then
                    strSQL = strSQL & "       ,ISNull(Round((CAST((Select Sum(DateDiff(Second," & _
                                      "                                            (Case" & _
                                      "                                               When A.StartTime > '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "' Then A.StartTime" & _
                                      "                                               Else '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                                      "                                             End)," & _
                                      "                                            (Case" & _
                                      "                                               When A.EndTime < '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "' Then A.EndTime" & _
                                      "                                               Else '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                                      "                                             End)))" & _
                                      "                          From (Select  EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime From tblEMSEquipmentStateLog" & _
                                      "                                UNION All" & _
                                      "                                Select  EquipmentType, EquipmentNo, EquipmentState, StartTime, GetDate() EndTime From tblEMSEquipmentState" & _
                                      "                               ) A" & _
                                      "                         Where A.StartTime <= '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                                      "                           And A.EndTime > '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                                      "                           And A.EquipmentState = " & drTemp("EquipmentState") & _
                                      "                           And A.EquipmentNo = Z.EquipmentNo" & _
                                      "                      Group By A.EquipmentNo, A.EquipmentState) as float) /CAST(" & StartEndTime & " AS float))*100,2),0) " & drTemp("StateName")
                End If
            Loop
            drTemp.Close()
            cmmTemp.Dispose()
            strSQL = strSQL & "   From tblEQPEquipmentBasis Z" & _
                              "  Where Z.EquipmentNo Is Not Null"
        
            If AreaNo <> defString Then
                strSQL = strSQL & " And (Select ContainareaNo From tblSMDAreaRelation Where ObjectNo = Z.EquipmentNo And ObjectType = 2) = '" & AreaNo & "'"
            End If
        
            If EquipmentNo <> defString Then
                strSQL = strSQL & " And EquipmentNo In ('" & Replace(EquipmentNo, ",", "','") & "')"
            End If
      
            If AdditionalXml <> "" Then
                '//additionalcondition
                strSQL = strSQL & SeparateAddXML_Condition(AdditionalXml)
            End If
            strSQL = strSQL & " Order By EquipmentNo"
            '//Select Data
            cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
            daTemp = New OleDb.OleDbDataAdapter(cmmTemp)
            dsTemp = New DataSet
            daTemp.Fill(dsTemp, "EQPDailyReport")
            If strDataBaseType = "oracle" Then
                strSQL = "  Select EquipmentNo, Sum(B.GoodQty) Moves" & _
                         "    From tblWIPCont_Equipment A, tblWIPLotLog_Report B" & _
                         "   Where A.LogGroupSerial = B.LogGroupSerial" & _
                         "     And A.EndTime Is Not Null " & _
                         "     And A.EndTime >= To_Date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS') " & _
                         "     And A.EndTime < To_Date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS') " & _
                         "Group By EquipmentNo"
            ElseIf DataBaseType = "mssql" Or DataBaseType = "msaccess" Then
                strSQL = "  Select EquipmentNo, Sum(B.GoodQty) Moves" & _
                         "    From tblWIPCont_Equipment A, tblWIPLotLog_Report B" & _
                         "   Where A.LogGroupSerial = B.LogGroupSerial" & _
                         "     And A.EndTime Is Not Null " & _
                         "     And A.EndTime >= '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                         "     And A.EndTime < '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
                         "Group By EquipmentNo"
            End If
            cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
            drTemp = cmmTemp.ExecuteReader()
            Do While drTemp.Read
                Rows = dsTemp.Tables("EQPDailyReport").Select("EquipmentNo = '" & drTemp("EquipmentNo") & "'")
                If Rows.Length <> 0 Then
                    Rows(0).Item("Moves") = drTemp("Moves")
                End If
            Loop
            drTemp.Close()
            cmmTemp.Dispose()
            '//Combine Return Value
            LoadEQPDailyReport = CombineXMLReturnValue("loadeqpdailyreport", "EQPDailyReport", _
             "DataSet", FormatXMLSchema(dsTemp.GetXmlSchema), dsTemp.GetXml, "")
        Catch e1 As Exception
            LoadEQPDailyReport = "fail"
       
            Throw 'New Exception("clsReport.LoadEQPDailyReport: " & e1.Message)
        Finally
            Call objLic.CloseConnection(cnnTemp)
            If Not daTemp Is Nothing Then
                daTemp.Dispose()
            End If
            If Not dsTemp Is Nothing Then
                dsTemp.Dispose()
            End If
        End Try
    End Function
谁能帮我从里面抠出最终的SQL来，用的是ORACLE数据库

Answer 1

你自己在程序的适当位置打印一下 strSQL  不就ok了。

Answer 2

抠出SQL是一个力气活！我试了一下，问题很大，老兄对SQL的非过程性质没有概念，这样的程序没有使用价值。
我理出的大致逻辑是：Select :AreaNo,
       Z.EquipmentType
       Z.EquipmentNo,
       0 Moves,
       Nvl(Round(SUM(计算时间SELECT）* 24*60*60 / :StartEndTime * 100 ,2),0)  StateName
   From tblEQPEquipmentBasis Z
  Where Z.EquipmentNo Is Not Null
    AND EXISTS (SELECT 1 
                  FROM tblSMDAreaRelation 
                 Where ObjectNo = Z.EquipmentNo 
                   And ObjectType = 2 
                   AND ContainareaNo = :AreaNo )
    And EquipmentNo In (:EquipmentNo) -- :EquipmentNo 的数据类似于:'aa','bb','cc'
  Order By EquipmentNo---- 计算时间SELECT
Select Sum(Least(A.EndTime,:EndTime) - Greatest(A.StartTime,:StartTime)) 
  From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime 
          From tblEMSEquipmentStateLog
         UNION All
        Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime 
          From tblEMSEquipmentState ) A
         Where A.StartTime <= :EndTime
           And A.EndTime   >  :StartTime)
           And A.EquipmentState = :EquipmentState
           And A.EquipmentNo    = Z.EquipmentNo)

Answer 3

顶楼上  将SQL抠出来了