UPDATE a SET CountOfAA=dt.CountOf FROM @MediaResurce a INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku WHERE somecol = 0 GROUP BY aa.Sku ) dt ON a.sku=dt.sku
/////////////
UPDATE a SET CountOfBB=dt.CountOf FROM @MediaResurce a INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku WHERE somecol = 1 GROUP BY aa.Sku ) dt ON a.sku=dt.sku
Cela devrait être ce que vous cherchez:
UPDATE a SET CountOfAA = dt.CountOfAA, CountOfBB = dt.CountOfBB FROM @MediaResurce a INNER JOIN ( SELECT SUM( case somecol when 0 then 1 else 0 end ) AS CountOfAA, SUM( case somecol when 1 then 1 else 0 end ) AS CountOfBB FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku ) dt ON a.sku=dt.sku
Au lieu de deux requêtes distinctes pour countr chaque instance, vous pouvez SUM "1" ou "0" en fonction des exigences de votre "somecol".
Eh bien, vous pouvez facilement le faire comme
UPDATE a SET CountOfAA=dt.CountOf, CountOfBB=dt2.CountOf FROM @MediaResurce a INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku WHERE somecol = 0 GROUP BY aa.Sku ) dt ON a.sku=dt.sku INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku WHERE somecol = 1 GROUP BY aa.Sku ) dt2 ON a.sku=dt2.sku
Quel est le moyen paresseux et inefficace, une autre méthode pourrait être quelque chose comme ceci:
SUM(somecol) AS 'count where somecol=1' count(*) - SUM(somecol) AS 'count where somecol=0' ... where somecol in (0,1) //only need if somecol can be something other than 0 or 1!