[Lot No.] = LotNo 中的LotNo应该是从 vw_sqe_headerQty right outer join (select DID, LotNo, IdxGroup, IdxPartNo, OPID from vw_sqe_HeaderQty Group by Did, LotNo, IdxGroup, IdxPartNo, OPID) a on a.did = dbo.vw_sqe_HeaderQty.did 这个结果集中得到!可我将它们加上括号之后加上别名,还是有错呀!(vw_sqe_headerQty right outer join (select DID, LotNo, IdxGroup, IdxPartNo, OPID from vw_sqe_HeaderQty Group by Did, LotNo, IdxGroup, IdxPartNo, OPID) a on a.did = dbo.vw_sqe_HeaderQty.did) b where.....
帮我看看好吗? select @s = '[Lot No.] = LotNo,[Check Times] = CheckTimes, [Operater Badge No.] = OperaUser,[Part Qty.] = LotQty ' +',[Visual] = sum( Case CheckSort When ''Visual'' then SampleQty else 0 end )' +',[Dimension] = sum( Case CheckSort When ''Dimension'' then SampleQty else 0 end )' +',[No.Of] = BadQty, [Inspector] = CheckUser, [Res] = Re 'exec('select ' + @s + ' from ((select max(did), IdxGroup, IdxPartNo ' +'from vw_sqe_headerQty Group by LotNo, IdxGroup, IdxPartNo)a ' +' left outer join vw_sqe_headerQty on a.did = vw_sqe_headerQty.did) b' +' where SampleDates = '''+ @inDate + ''' '+ @whereStr + ' Order by LotNo' )
给出表结构、查询要求、示例数据及相应的结果集,大伙帮你写SQL语句。看你的大段的代码太累眼睛。
这样呀!资料库只有一个视图test fieldID field1 field2 field3 field4 1 NO1 2004-6-18 type1 100 2 NO1 2004-6-18 type2 200 3 NO1 2004-6-18 type2 200 4 NO2 2004-6-18 type1 300 5 NO3 2004-6-18 type1 400 结果是field1 field2 type1 type2 NO1 2004-6-18 100 400 NO2 2004-6-18 300 0 NO3 2004-6-18 400 0 我的代码是:select @s = 'field1,field2' +',[type1] = sum( Case field3 When ''type1'' then field4 else 0 end )' +',[type2] = sum( Case field3 When ''type2'' then field4 else 0 end )' exec('select ' + @s + ' from (select max(fieldid), field1 ' +'from test Group by field1)a ' +' left outer join test on a.fieldID = test.fieldID) b' +' where field2 = '''+ @inDate + ''' '+ @whereStr + ' Order by field1' ) 不知道这样够不够清楚?
max(did) --未指定字段名 子查询定义不清晰,不知道谁跟谁
这样呀!资料库只有一个视图testfieldID field1 field2 field3 field4 1 NO1 2004-6-18 type1 100 2 NO1 2004-6-18 type2 200 3 NO1 2004-6-18 type2 200 4 NO2 2004-6-18 type1 300 5 NO3 2004-6-18 type1 400 结果是field1 field2 type1 type2 NO1 2004-6-18 100 400 NO2 2004-6-18 300 0 NO3 2004-6-18 400 0 我的代码是:select @s = 'field1,field2' +',[type1] = sum( Case field3 When ''type1'' then field4 else 0 end )' +',[type2] = sum( Case field3 When ''type2'' then field4 else 0 end )' exec('select ' + @s + ' from (select max(fieldid), field1 ' +'from test Group by field1)a ' +' left outer join test on a.fieldID = test.fieldID) b' +' where field2 = '''+ @inDate + ''' '+ @whereStr + ' Order by field1' ) 这样肯定更清楚些,我field3只有两种可能:type1/type2
--照我的理解,是这样改declare @s varchar(8000),@whereStr varchar(10),@inDate varchar(10) select @whereStr='',@inDate='@inDate'select @s = '[Lot No.] = LotNo,[Check Times] = CheckTimes, [Operater Badge No.] = OperaUser,[Part Qty.] = LotQty ' +',[Visual] = sum( Case CheckSort When ''Visual'' then SampleQty else 0 end )' +',[Dimension] = sum( Case CheckSort When ''Dimension'' then SampleQty else 0 end )' +',[No.Of] = BadQty, [Inspector] = CheckUser, [Res] = Re 'exec('select ' + @s + ' from (select did=max(did), IdxGroup, IdxPartNo ' +'from vw_sqe_headerQty Group by LotNo, IdxGroup, IdxPartNo)a ' +' left outer join vw_sqe_headerQty b on a.did = b.did ' +' where SampleDates = '''+ @inDate + ''' '+ @whereStr + ' Order by LotNo' )
vw_sqe_headerQty right outer join (select DID, LotNo, IdxGroup, IdxPartNo, OPID from vw_sqe_HeaderQty Group by Did, LotNo, IdxGroup, IdxPartNo, OPID) a on a.did = dbo.vw_sqe_HeaderQty.did
这个结果集中得到!可我将它们加上括号之后加上别名,还是有错呀!(vw_sqe_headerQty right outer join (select DID, LotNo, IdxGroup, IdxPartNo, OPID from vw_sqe_HeaderQty Group by Did, LotNo, IdxGroup, IdxPartNo, OPID) a on a.did = dbo.vw_sqe_HeaderQty.did) b where.....
select @s = '[Lot No.] = LotNo,[Check Times] = CheckTimes, [Operater Badge No.] = OperaUser,[Part Qty.] = LotQty '
+',[Visual] = sum( Case CheckSort When ''Visual'' then SampleQty else 0 end )'
+',[Dimension] = sum( Case CheckSort When ''Dimension'' then SampleQty else 0 end )'
+',[No.Of] = BadQty, [Inspector] = CheckUser, [Res] = Re 'exec('select ' + @s + ' from ((select max(did), IdxGroup, IdxPartNo '
+'from vw_sqe_headerQty Group by LotNo, IdxGroup, IdxPartNo)a '
+' left outer join vw_sqe_headerQty on a.did = vw_sqe_headerQty.did) b'
+' where SampleDates = '''+ @inDate +
''' '+ @whereStr + ' Order by LotNo' )
给出表结构、查询要求、示例数据及相应的结果集,大伙帮你写SQL语句。看你的大段的代码太累眼睛。
fieldID field1 field2 field3 field4
1 NO1 2004-6-18 type1 100
2 NO1 2004-6-18 type2 200
3 NO1 2004-6-18 type2 200
4 NO2 2004-6-18 type1 300
5 NO3 2004-6-18 type1 400 结果是field1 field2 type1 type2
NO1 2004-6-18 100 400
NO2 2004-6-18 300 0
NO3 2004-6-18 400 0 我的代码是:select @s = 'field1,field2'
+',[type1] = sum( Case field3 When ''type1'' then field4 else 0 end )'
+',[type2] = sum( Case field3 When ''type2'' then field4 else 0 end )' exec('select ' + @s + ' from (select max(fieldid), field1 '
+'from test Group by field1)a '
+' left outer join test on a.fieldID = test.fieldID) b'
+' where field2 = '''+ @inDate +
''' '+ @whereStr + ' Order by field1' )
不知道这样够不够清楚?
子查询定义不清晰,不知道谁跟谁
1 NO1 2004-6-18 type1 100
2 NO1 2004-6-18 type2 200
3 NO1 2004-6-18 type2 200
4 NO2 2004-6-18 type1 300
5 NO3 2004-6-18 type1 400 结果是field1 field2 type1 type2
NO1 2004-6-18 100 400
NO2 2004-6-18 300 0
NO3 2004-6-18 400 0 我的代码是:select @s = 'field1,field2'
+',[type1] = sum( Case field3 When ''type1'' then field4 else 0 end )'
+',[type2] = sum( Case field3 When ''type2'' then field4 else 0 end )' exec('select ' + @s + ' from (select max(fieldid), field1 '
+'from test Group by field1)a '
+' left outer join test on a.fieldID = test.fieldID) b'
+' where field2 = '''+ @inDate +
''' '+ @whereStr + ' Order by field1' )
这样肯定更清楚些,我field3只有两种可能:type1/type2
select @whereStr='',@inDate='@inDate'select @s = '[Lot No.] = LotNo,[Check Times] = CheckTimes, [Operater Badge No.] = OperaUser,[Part Qty.] = LotQty '
+',[Visual] = sum( Case CheckSort When ''Visual'' then SampleQty else 0 end )'
+',[Dimension] = sum( Case CheckSort When ''Dimension'' then SampleQty else 0 end )'
+',[No.Of] = BadQty, [Inspector] = CheckUser, [Res] = Re 'exec('select ' + @s + ' from (select did=max(did), IdxGroup, IdxPartNo '
+'from vw_sqe_headerQty Group by LotNo, IdxGroup, IdxPartNo)a '
+' left outer join vw_sqe_headerQty b on a.did = b.did '
+' where SampleDates = '''+ @inDate +
''' '+ @whereStr + ' Order by LotNo' )
我在运行存储过程时一样调试不了!
不然就不会出错?