SQLDF EXAMPLE2

gistfile1.r

good_ing <- sqldf("
                  SELECT a.*,b.max,
                    CASE                                              #這一段是說用max來recode變項
                      WHEN b.max >= 80 THEN '80up'                    #
                      WHEN b.max >= 60 and b.max < 80 THEN '60-80'    #
                      WHEN b.max >= 40 and b.max < 60 THEN '40-60'    #
                      WHEN b.max <  40 THEN '40under'                 #
                    END AS ingLevel #<--將recode的變項重新命名
                  FROM goodInfo as a,
                        (SELECT id, max(per) as max
                        FROM goodInfo
                        GROUP BY id
                        ORDER BY id) as b #<--這邊是子查詢
                  WHERE a.id = b.id  #<--這邊是說我把兩個表格合併的條件變項
                  GROUP BY id
                  ORDER BY id
                  ")