select Member_Id , Elem_Class as Num ,Member_Id,Elem_Class , AskNum,Count(*) as ComNum from (select Member_Id , b.ELEM_CLASS ,
Commodity_Id ,(select count(*) from Bid_Elem_Book with (nolock)
where Book_Id = b.Book_Id and len(Elem_Code) = 4 and
isobjectivity = 1 and ELEM_CLASS = b.ELEM_CLASS ) as AskNum from Bid_ObjElem_Score a with (nolock)
join Bid_Elem_Book b with (nolock) on b.Elem_Book_Id = a.Elem_Book_Id
where b.book_Id = 78 and ChkState in (1,2) and ((b.ELEM_CLASS = 1 and a.commodity_id is null)
or (b.Elem_Class = 3 and exists(select * from Bid_Apply_Method y with (nolock)
join Bid_Item i with (nolock) on i.Item_Id = y.Item_Id
where i.Book_Id = 78 and y.Commodity_Id = a.Commodity_Id and y.apply_method=1 )))
and b.isobjectivity = 1 ) aaa group by Member_Id,Elem_Class,AskNum,Commodity_Id
Commodity_Id ,(select count(*) from Bid_Elem_Book with (nolock)
where Book_Id = b.Book_Id and len(Elem_Code) = 4 and
isobjectivity = 1 and ELEM_CLASS = b.ELEM_CLASS ) as AskNum from Bid_ObjElem_Score a with (nolock)
join Bid_Elem_Book b with (nolock) on b.Elem_Book_Id = a.Elem_Book_Id
where b.book_Id = 78 and ChkState in (1,2) and ((b.ELEM_CLASS = 1 and a.commodity_id is null)
or (b.Elem_Class = 3 and exists(select * from Bid_Apply_Method y with (nolock)
join Bid_Item i with (nolock) on i.Item_Id = y.Item_Id
where i.Book_Id = 78 and y.Commodity_Id = a.Commodity_Id and y.apply_method=1 )))
and b.isobjectivity = 1 ) aaa group by Member_Id,Elem_Class,AskNum,Commodity_Id
错误的结果集为:
Member_Id Num Member_Id Elem_Class AskNum ComNum
24237 1 24237 1 3 3
24237 1 24237 3 7 7
24730 3 24730 1 3 3
24730 3 24730 3 3 7
24730 3 24730 3 3 7
24730 3 24730 3 3 7
25754 3 25754 1 3 1
25754 3 25754 3 3 2
25754 3 25754 3 3 2
25754 3 25754 3 3 7
你没仔细看我两个结果集,Num列和Elem_Class列是不同的...
Num是计算字段,而Elem_Class列是物理上存在的字段
我也知道理论上不会,但你观察
第二个脚本中Elem_Class = 1 对应的AskNum = 3
Elem_Class = 7 对应 AskNum = 7
当两个数据集左联后却发生了变化,问题在这
第二个脚本的结果中Elem_Class = 1 对应的AskNum = 3
Elem_Class = 3 对应 AskNum = 7
当两个数据集左联后却发生了变化,问题在这
Member_Id Num Member_Id Elem_Class AskNum ComNum
24237 1 24237 1 3 3
24237 1 24237 3 7 7
24730 3 24730 1 3 3
24730 3 24730 3 3 7
24730 3 24730 3 3 7
24730 3 24730 3 3 7
25754 3 25754 1 3 1
25754 3 25754 3 3 2
25754 3 25754 3 3 2
25754 3 25754 3 3 7