SQL: Comment pourrais-je transformer ceci en une requête?

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!