{"id":232877,"date":"2023-01-31T13:19:00","date_gmt":"2023-01-31T10:19:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232877"},"modified":"2023-02-07T13:05:11","modified_gmt":"2023-02-07T10:05:11","slug":"hur-listar-man-de-mest-roestade-inlaeggen-paa-ett-aar-med-sql","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/sv\/hur-listar-man-de-mest-roestade-inlaeggen-paa-ett-aar-med-sql\/","title":{"rendered":"Hur listar man de mest r\u00f6stade inl\u00e4ggen p\u00e5 ett \u00e5r med SQL?"},"content":{"rendered":"\n

I det h\u00e4r inl\u00e4gget<\/a> l\u00e4r vi oss hur du anv\u00e4nder SQL listar de mest popul\u00e4ra inl\u00e4ggen (enligt antalet kommentarer f\u00f6r varje inl\u00e4gg). Detta inl\u00e4gg kommer vi att f\u00e5 lite grundl\u00e4ggande statistik f\u00f6r inl\u00e4ggen under 2016 och vi l\u00e4r oss ocks\u00e5 de 10 b\u00e4sta inl\u00e4ggen sorterade efter r\u00f6stning.<\/p>\n

Totalt antal inl\u00e4gg<\/h2>\n

Baserat p\u00e5 denna SQL<\/a> :<\/p>\n

SELECT \n\u00a0 count(1) \nFROM \n\u00a0 wp_posts\nWHERE \n\u00a0 post_type = \"post\" and \n\u00a0 post_status = \"publish\" and \n\u00a0 date_format(post_date_gmt, \"%Y\") = \"2016\"<\/code><\/pre>\n
Vi vet att det finns 245 inl\u00e4gg publicerade under 2016.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
Topp mest diskuterade inl\u00e4gg<\/h2>\n
Och baserat p\u00e5 SQL<\/a>, har vi de mest diskuterade inl\u00e4ggen f\u00f6r i \u00e5r, allts\u00e5 2016.<\/p>\n
SELECT \n\u00a0 post_name, post_title \nFROM \n\u00a0 wp_posts \nWHERE \n\u00a0 post_type = 'post' and \n\u00a0 post_status = 'publish' and\n\u00a0 date_format(post_date_gmt, \"%Y\") = '2016' \nORDER BY\n\u00a0comment_count DESC \nLIMIT 10<\/code><\/pre>\n
Vi k\u00e4nner till de 10 mest diskuterade (som mest popul\u00e4ra) inl\u00e4ggen under 2016. \u00c4ndra gr\u00e4nsen 10<\/strong> f\u00f6r att till\u00e5ta att fler inl\u00e4gg visas, t.ex. gr\u00e4ns 20.<\/p>\n
+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n| post_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| post_title \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n| cc-coding-exercise-convert-a-number-to-hexadecimal \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C\/C++ Coding Exercise - Convert a Number to Hexadecimal? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| dynamic-programming-how-many-ways-to-connect-the-pipes \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Dynamic Programming - How many ways to connect the pipes? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| when-xx-evaluates-to-false-in-cc \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | When x==x Evaluates to FALSE in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| how-to-compute-sum-of-two-integers-without-plus-and-minus-operators \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| How to Compute Sum of Two Integers without Plus+ and Minus- Operators? |\n| how-to-find-intersection-of-two-arrays-in-c \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| How to Find Intersection of Two Arrays in C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| bit-manipulation-how-to-set-all-bits-between-i-and-j-in-n-equal-to-m-cplusplus | Bit Manipulation: How to Set All Bits Between i and j in N equal to M? |\n| how-to-check-valid-anagram-in-cc \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | How to Check Valid Anagram in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| c-coding-exercise-number-of-1-bits-revisited \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C++ Coding Exercise - Number of 1 Bits (Revisited) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| dynamic-programming-integer-break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| Dynamic Programming - Integer Break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| cc-coding-exercise-find-the-duplicate-number \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C\/C++ Coding Exercise - Find the Duplicate Number \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n10 rows in set (0.01 sec)<\/code><\/pre>\n
Dessa \u00e4r (i skrivande stund):<\/p>\n
    \n
  1. C\/C++ Kodnings\u00f6vning \u2013 Konvertera ett tal till hexadecimalt?<\/a><\/li>\n
  2. Dynamisk programmering \u2013 Hur m\u00e5nga s\u00e4tt att ansluta r\u00f6ren?<\/a><\/li>\n
  3. N\u00e4r x==x utv\u00e4rderas till FALSK i C\/C++?<\/a><\/li>\n
  4. Hur ber\u00e4knar man summan av tv\u00e5 heltal utan Plus+ och Minus-operatorer?<\/a><\/li>\n
  5. Hur hittar man sk\u00e4rningspunkten mellan tv\u00e5 matriser i C++?<\/a><\/li>\n
  6. Bitmanipulation: Hur st\u00e4ller man in alla bitar mellan i och j i N lika med M?<\/a><\/li>\n
  7. Hur kontrollerar jag ett giltigt anagram i C\/C++?<\/a><\/li>\n
  8. C++-kodnings\u00f6vning \u2013 antal 1 bitar (\u00e5terbes\u00f6kt)<\/a><\/li>\n
  9. Dynamisk programmering \u2013 heltalsbrytning<\/a><\/li>\n
  10. C\/C++ Kodnings\u00f6vning \u2013 Hitta dubblettnumret<\/a><\/li>\n<\/ol>\n
    Mest r\u00f6stade inl\u00e4gg via SQL<\/h2>\n
    Nu kan vi kombinera r\u00f6ststatistiken fr\u00e5n GD Star Rating<\/a> Plugin och f\u00e5 ytterligare en intressant ranking.<\/p>\n
    SELECT \n\u00a0 \u00a0`p`.`ID`, \n\u00a0 \u00a0`p`.`post_title` as `title`, \n\u00a0 \u00a0`visitor_votes` + `user_votes` as `total_votes`, \n\u00a0 \u00a0`visitor_votes`, \n\u00a0 \u00a0`user_votes` \u00a0\nFROM \u00a0\n\u00a0 `wp_gdsr_data_article` as `da` \nINNER JOIN \n\u00a0 `wp_posts` as `p` ON `da`.`post_id` = `p`.`ID` \nWHERE\n\u00a0 `p`.post_type = 'post' and \n\u00a0 `p`.post_status = 'publish' and \u00a0\n\u00a0 date_format(`p`.`post_date_gmt`, \"%Y\") = '2016'\nHAVING\n\u00a0 `total_votes` > 0\nORDER BY\n\u00a0 `total_votes` desc \nLIMIT 10<\/code><\/pre>\n
    Detta ger f\u00f6ljande 10 mest r\u00f6stade inl\u00e4gg under \u00e5ret:<\/p>\n
    +------+----------------------------------------------------------------------------+-------------+---------------+------------+\n| ID \u00a0 | title \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| total_votes | visitor_votes | user_votes |\n+------+----------------------------------------------------------------------------+-------------+---------------+------------+\n| 4793 | When x==x Evaluates to FALSE in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 105.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a095.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4679 | How to Find the Maximum of Two Integers without Using Comparison Operator? | \u00a0 \u00a0 \u00a0 \u00a081.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a081.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4191 | How to Delete a Node from a Binary Search Tree? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a068.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a058.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4380 | C++ Coding Exercise: How to Check if a Large Integer is divisible by 11? \u00a0 | \u00a0 \u00a0 \u00a0 \u00a067.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a057.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4329 | Microsoft Interview Question - Get the Area of the Triangle \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a066.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a066.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4740 | PPAP in C++ and Javascript for Beginner \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a062.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a052.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4166 | How to Invert a Binary Tree in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a048.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a048.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4176 | How to Reverse Bits for 32-bit Unsigned Integer in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a046.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a046.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4178 | C++ Coding Exercise - How to Find First Missing Number? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a040.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a040.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4227 | Dynamic Programming - Integer Break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a031.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a031.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n+------+----------------------------------------------------------------------------+-------------+---------------+------------+\n10 rows in set (0.01 sec)<\/code><\/pre>\n
      \n
    1. N\u00e4r x==x utv\u00e4rderas till FALSK i C\/C++?<\/a><\/li>\n
    2. Hur hittar man maximalt tv\u00e5 heltal utan att anv\u00e4nda j\u00e4mf\u00f6relseoperator?<\/a><\/li>\n
    3. Hur tar man bort en nod fr\u00e5n ett bin\u00e4rt s\u00f6ktr\u00e4d?<\/a><\/li>\n
    4. C++-kodnings\u00f6vning: Hur kontrollerar man om ett stort heltal \u00e4r delbart med 11?<\/a><\/li>\n
    5. Microsoft-intervjufr\u00e5ga \u2013 F\u00e5 arean av triangeln<\/a><\/li>\n
    6. PPAP i C++ och Javascript f\u00f6r nyb\u00f6rjare<\/a><\/li>\n
    7. Hur man inverterar ett bin\u00e4rt tr\u00e4d i C\/C++?<\/a><\/li>\n
    8. Hur v\u00e4nder man bitar f\u00f6r 32-bitars osignerat heltal i C\/C++?<\/a><\/li>\n
    9. C++ Kodnings\u00f6vning \u2013 Hur hittar man det f\u00f6rsta saknade numret?<\/a><\/li>\n
    10. Dynamisk programmering \u2013 heltalsbrytning<\/a><\/li>\n<\/ol>\n
      Naturligtvis kan du modifiera ovanst\u00e5ende b\u00e5da SQL-fr\u00e5gor<\/a> s\u00e5 att det ger 12 inl\u00e4gg, dvs ett b\u00e4sta f\u00f6r varje m\u00e5nad. Du beh\u00f6ver bara gruppera efter m\u00e5naden s\u00e5 h\u00e4r:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Och du kan ocks\u00e5 skapa en PHP-sida<\/a> som ger de mest uppdaterade rankningstabellerna f\u00f6r inl\u00e4ggen, men det som har visats h\u00e4r \u00e4r statiskt, dvs rankningstabellen genereras som den \u00e4r idag.<\/p>\n
      SQL<\/p>\n
      Inspelningsk\u00e4lla:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      Hur listar man de mest r\u00f6stade inl\u00e4ggen p\u00e5 ett \u00e5r med SQL?<\/p>\n","protected":false},"author":1,"featured_media":224083,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wp_rev_ctl_limit":""},"categories":[838,848,999,901,922,807,868],"tags":[1173],"class_list":["post-232877","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guide-foer-nyboerjare","category-handledningar","category-innehallsmarknadsforing","category-koda","category-oevrig","category-php-9","category-wordpress-9","tag-affiai-sv"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/posts\/232877","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/comments?post=232877"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/posts\/232877\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/media?parent=232877"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/categories?post=232877"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/sv\/wp-json\/wp\/v2\/tags?post=232877"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}