|
||
0
0
¾ÊÀ̾ÊÀÌ
ÆǸŻóÅ°¡ ('sale','notsale') ÀÌ°í »óÇ°ÄÚµå º°·Î ÆǸżö·®ÀÌ MAXÀΰªÀ» °¡Á®¿À°í ½ÍÀ¸½Å°Ç°¡¿ä?
2020-08-06 *
|
0
0
[* ºñȸ¿ø *]
select »óÇ°ÄÚµå, ÆǸŻóÅÂ, ÆǸżö·® from ( select a.* , rank() over(partition by »óÇ°ÄÚµå order by ÆǸŻóÅ desc ) as rank from »óÇ° a where a.ÆǸżö·® is not null and a.ÆǸŻóÅ in ('sale','notsale') ) where rank = 1
Ãʺ¸¶ó ´õ °£´ÜÈ÷ ÇÒ¼ö ÀÖÀ»Áø ¸ð¸£°Ú³×¿ä. |
0
0
Çѹø»ç´ÂÀλý!!
ÀÌ°ÍÀ» Á» ÀÀ¿ëÇؼ.
SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY »óÇ°ÄÚµå ORDER BY ÆǸŻóÅÂ) AS [rank], * FROM ( SELECT »óÇ°ÄÚµå, CASE ÆǸŻóÅ WHEN 'sale' THEN 1 ELSE 2 END AS ÆǸŻóÅ FROM »óÇ° WHERE ÆǸŻóÅ IN ('sale', 'notsale') ) ) WHERE [rank] = 1;
Çѹø´õ °¨½ÎÁà¾ßÇÒ²¨°°¾Æ¿ä. ±×¸®°í ÆǸŻóÅ °ªÀÌ sale / notsale / empty º¸´Ù´Â »óÅ°ªÀ¸·Î 1 / 2 / 4 ·Î ³Ö´Â °ÍÀÌ ´õ È¿À²ÀûÀÌÁö ¾ÊÀ»±î ½Í½À´Ï´Ù. ±× ÀÌÀ¯´Â ºñÆ®¿¬»êÀ» Çϸé IN Á¶°ÇÀ» »ç¿ëÇÏÁö ¾Ê°í ´õ È¿À²ÀûÀ¸·Î ¿øÇÏ´Â °ªÀ» °¡Á®¿Ã ¼ö Àֱ⠶§¹®ÀÔ´Ï´Ù. |
0
0
²¿¸¶À̽´
select »óÇ°ÄÚµå, case when sale=0 then 'notsale' else 'sale' end as ÆǸŻóÅÂ, case when sale=0 then notsale else sale end as ÆǸżö·® from( select »óÇ°ÄÚµå, case when ÆǸŻóÅÂ='sale' then ÆǸżö·® else 0 end as sale, case when ÆǸŻóÅÂ='notsale' then ÆǸżö·® else 0 end as notsale from µ¥ÀÌÅÍÅ×À̺í )a group by »óÇ°ÄÚµå
|
|
|
¡â ÀÌÀü±Û¡ä ´ÙÀ½±Û | ¸ñ·Ïº¸±â |