//第一个集合为所有的数据
var specilist = new List<Me.SpecificationsInfo>();
var resultall = (from a in dbContext.by_sku_items
join b in dbContext.by_attributes on a.by_attributes_id equals b.by_attributes_id join c in dbContext.by_attribute_values on a.by_attribute_values_id equals c.by_attribute_values_id where a.by_product_id == proid select new Me.OrderEditAll { skuid = a.by_sku_items_id, colorid = b.by_attributes_id, color = b.attributes_name, sizeid = c.by_attribute_values_id, size = c.value_str }).ToList();//第二个集合为需要合并的集合 var orderitemlist = dbContext.by_order_item.Where(t => t.order_id == _orderid && t.by_product_id == proid).ToList(); var resultorder = (from a in orderitemlist join b in dbContext.by_sku_items on a.by_sku_items_id equals b.by_sku_items_id join c in dbContext.by_attributes on b.by_attributes_id equals c.by_attributes_id join d in dbContext.by_attribute_values on b.by_attribute_values_id equals d.by_attribute_values_id //where b.status == statusWorking && c.status == statusWorking && d.status == statusWorking orderby c.index, d.index select new Me.OrderEdit { skuid = a.by_sku_items_id, id = a.by_order_item_id, nums = a.nums, colorid = c.by_attributes_id, color = c.attributes_name, sizeid = d.by_attribute_values_id, size = d.value_str }).ToList();//第三个为最终结果,将不为空的数据合并到所有集合中 var result = (from all in resultall join order in resultorder on all.skuid equals order.skuid into temp from tt in temp.DefaultIfEmpty() select new Me.OrderEdit { skuid = all.skuid, id = tt != null ? tt.id : 0, nums = tt != null ? tt.nums : 0, colorid = all.colorid, color = all.color, sizeid = all.sizeid, size = all.size}).ToList();
//第二种为sql实现方式
select by_sku_items_id,sum(t1.by_order_items_id) as by_order_items_id,sum(t1.nums) as nums,t1.by_attributes_id,t1.attributes_name,t1.by_attribute_values_id,t1.value_str from
(select * from (select * from (select a.by_sku_items_id,0 as by_order_items_id,0 as nums,b.by_attributes_id,b.attributes_name,c.by_attribute_values_id,c.value_str from by_sku_items ajoin by_attributes b on a.by_attributes_id=b.by_attributes_idjoin by_attribute_values c on a.by_attribute_values_id=c.by_attribute_values_idwhere by_product_id=9 ) aaUNION select * from (select a.by_sku_items_id,a.by_order_item_id,a.nums,c.by_attributes_id,c.attributes_name,d.by_attribute_values_id,d.value_strfrom by_order_item ajoin by_sku_items b on a.by_sku_items_id=b.by_sku_items_idjoin by_attributes c on b.by_attributes_id=c.by_attributes_idjoin by_attribute_values d on b.by_attribute_values_id=d.by_attribute_values_idwhere b.status=2 and c.status=2 and d.status=2 and a.order_id=1459428164000004 and a.by_product_id=9) bb) t ) t1 group by by_sku_items_id,by_attributes_id,by_attribute_values_id,attributes_name,value_str