表中主要字段
Table : manLifeCount
LF_ID varchar(10) 主键
iBOT Int 外键
MachineID Varchar(20) 查询条件
Table : manLifeDetail
LF_ID Varchar(10) 外键
Lead_date1 Varchar(10) 行换成列 的列名
Lead_date datetime 条件时间
Number Int 行换成列 的内容
Table :sysBOT
iBOT Int 主键
vProduct Varchar(20) 查询条件用到字段1.我首先创建Tv_Test 试图
Create View Tv_test
as
SELECT a.LF_ID, a.MachineID, b.vProduct, b.vWork, b.ToolNo, b.Ora_Number, b.life, b.Qty, a.Saler, a.Up_Cutter, a.LastM_Count, a.Cut_Count, a.Multiple, a.Lift1, a.lift2, a.lift3, c.Staff_Name AS CrateBy, a.CrateDate, m.Lead_date1, m.Number, m.Lead_date, m.LF_ID AS LF_ID1
FROM manLifeCount AS a LEFT OUTER JOIN
sysBOT AS b ON a.iBOT = b.AutoID LEFT OUTER JOIN
sysUser AS c ON a.CrateBy = c.Staff_ID LEFT OUTER JOIN
manLifeDetail AS m ON a.LF_ID = m.LF_ID2.再根据条件查询 试图 Tv_Test
SQL_Join = " Select top 100000 LF_ID,MachineID,vProduct,vWork ,ToolNo,Ora_Number,life,Qty, " _
& " Saler ,Up_Cutter ,LastM_Count,Cut_Count ,Multiple ,Lift1 ,lift2 ,lift3 , " _
& " CrateBy,CrateDate, Lead_date1,Number from Tv_test Where "
If chk_MaID.Checked = True Then
SQL_Join = SQL_Join & " and MachineID like '" & _fun.RCO(txt_MaID.Text) & "%'
End If
If chk_pnNO.Checked = True Then
SQL_Join = SQL_Join & " and Ora_Number like '" & _fun.RCO(txt_pnNO.Text) & "%' "
End If
If chk_pro.Checked = True Then
SQL_Join = SQL_Join & " and vProduct like '" & _fun.RCO(txt_pro.Text) & "%' "
End If
If chk_word.Checked = True Then
SQL_Join = SQL_Join & " and vWork like '" & _fun.RCO(txt_work.Text) & "%' "
End If
If chk_r.Checked = True Then
SQL_Join = SQL_Join & " and Lead_date>='" & DTP_cStart.Text & "' and Lead_date < ='" & DTP_cEnd.Text & "' "
End If
SQL_Join = SQL_Join & " Order by LF_ID,MachineID "3.再根据该查询的sql语句重新创建新的试图 此时等到试图Tv_test2
Create view Tv_test2 as SQL_Join4.最后根据试图Tv_test2进行行转列
SqlStr = " declare @sql varchar(8000) " _
& " select @sql = ISNULL(@sql + '],[','') + Lead_date1 from Tv_test2 group by Lead_date1 order by Lead_date1 Asc" _
& " set @sql ='[' + @sql + ']' " _
& " exec('select * from (select * from Tv_test2 ) a pivot (max(Number) for Lead_date1 in (' + @sql + ')) b' )"
这就是我想要的结果,请问怎么优化一下 该Sql 语句 ?
Table : manLifeCount
LF_ID varchar(10) 主键
iBOT Int 外键
MachineID Varchar(20) 查询条件
Table : manLifeDetail
LF_ID Varchar(10) 外键
Lead_date1 Varchar(10) 行换成列 的列名
Lead_date datetime 条件时间
Number Int 行换成列 的内容
Table :sysBOT
iBOT Int 主键
vProduct Varchar(20) 查询条件用到字段1.我首先创建Tv_Test 试图
Create View Tv_test
as
SELECT a.LF_ID, a.MachineID, b.vProduct, b.vWork, b.ToolNo, b.Ora_Number, b.life, b.Qty, a.Saler, a.Up_Cutter, a.LastM_Count, a.Cut_Count, a.Multiple, a.Lift1, a.lift2, a.lift3, c.Staff_Name AS CrateBy, a.CrateDate, m.Lead_date1, m.Number, m.Lead_date, m.LF_ID AS LF_ID1
FROM manLifeCount AS a LEFT OUTER JOIN
sysBOT AS b ON a.iBOT = b.AutoID LEFT OUTER JOIN
sysUser AS c ON a.CrateBy = c.Staff_ID LEFT OUTER JOIN
manLifeDetail AS m ON a.LF_ID = m.LF_ID2.再根据条件查询 试图 Tv_Test
SQL_Join = " Select top 100000 LF_ID,MachineID,vProduct,vWork ,ToolNo,Ora_Number,life,Qty, " _
& " Saler ,Up_Cutter ,LastM_Count,Cut_Count ,Multiple ,Lift1 ,lift2 ,lift3 , " _
& " CrateBy,CrateDate, Lead_date1,Number from Tv_test Where "
If chk_MaID.Checked = True Then
SQL_Join = SQL_Join & " and MachineID like '" & _fun.RCO(txt_MaID.Text) & "%'
End If
If chk_pnNO.Checked = True Then
SQL_Join = SQL_Join & " and Ora_Number like '" & _fun.RCO(txt_pnNO.Text) & "%' "
End If
If chk_pro.Checked = True Then
SQL_Join = SQL_Join & " and vProduct like '" & _fun.RCO(txt_pro.Text) & "%' "
End If
If chk_word.Checked = True Then
SQL_Join = SQL_Join & " and vWork like '" & _fun.RCO(txt_work.Text) & "%' "
End If
If chk_r.Checked = True Then
SQL_Join = SQL_Join & " and Lead_date>='" & DTP_cStart.Text & "' and Lead_date < ='" & DTP_cEnd.Text & "' "
End If
SQL_Join = SQL_Join & " Order by LF_ID,MachineID "3.再根据该查询的sql语句重新创建新的试图 此时等到试图Tv_test2
Create view Tv_test2 as SQL_Join4.最后根据试图Tv_test2进行行转列
SqlStr = " declare @sql varchar(8000) " _
& " select @sql = ISNULL(@sql + '],[','') + Lead_date1 from Tv_test2 group by Lead_date1 order by Lead_date1 Asc" _
& " set @sql ='[' + @sql + ']' " _
& " exec('select * from (select * from Tv_test2 ) a pivot (max(Number) for Lead_date1 in (' + @sql + ')) b' )"
这就是我想要的结果,请问怎么优化一下 该Sql 语句 ?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货