{"id":232914,"date":"2023-01-31T13:49:00","date_gmt":"2023-01-31T10:49:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232914"},"modified":"2023-02-07T13:25:17","modified_gmt":"2023-02-07T10:25:17","slug":"kuidas-loetleda-sql-i-abil-aasta-enimhaeaele-saanud-postitusi","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/et\/kuidas-loetleda-sql-i-abil-aasta-enimhaeaele-saanud-postitusi\/","title":{"rendered":"Kuidas loetleda SQL-i abil aasta enimh\u00e4\u00e4le saanud postitusi?"},"content":{"rendered":"\n

Selles postituses<\/a> \u00f5pime kasutama SQL-i k\u00f5ige populaarsemate postituste loendit (vastavalt iga postituse kommentaaride arvule). Sellest postitusest saame 2016. aasta postituste p\u00f5histatistikat ja samuti saame teada 10 parimat postitust, mis on h\u00e4\u00e4letuste alusel sorteeritud.<\/p>\n

Postituste koguarv<\/h2>\n

Selle SQL<\/a> -i p\u00f5hjal :<\/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
Teame, et 2016. aastal on avaldatud 245 postitust.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
Enim arutatud postitused<\/h2>\n
Ja SQL-i<\/a> p\u00f5hjal on meil selle aasta ehk 2016. aasta enim arutatud postitusi.<\/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
Teame 2016. aasta 10 enim arutatud (kui k\u00f5ige populaarsemat) postitust. Muutke piirangut 10<\/strong>, et kuvada rohkem postitusi, nt 20. piirang.<\/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
Need on (kirjutamise ajal):<\/p>\n
    \n
  1. C\/C++ kodeerimisharjutus \u2013 teisendada arv kuueteistk\u00fcmnends\u00fcsteemiks?<\/a><\/li>\n
  2. D\u00fcnaamiline programmeerimine \u2013 mitu v\u00f5imalust torude \u00fchendamiseks?<\/a><\/li>\n
  3. Kui x==x hindab C\/C++ v\u00e4\u00e4rtuseks FALSE?<\/a><\/li>\n
  4. Kuidas arvutada kahe t\u00e4isarvu summat ilma pluss+ ja miinusoperaatoriteta?<\/a><\/li>\n
  5. Kuidas leida C++-s kahe massiivi ristumiskohta?<\/a><\/li>\n
  6. Bittide manipuleerimine: kuidas m\u00e4\u00e4rata k\u00f5ik bitid i ja j vahel N-s v\u00f5rdseks M-ga?<\/a><\/li>\n
  7. Kuidas kontrollida kehtivat anagrammi C\/C++-s?<\/a><\/li>\n
  8. C++ kodeerimisharjutus \u2013 1 bittide arv (\u00fcle vaadatud)<\/a><\/li>\n
  9. D\u00fcnaamiline programmeerimine \u2013 t\u00e4isarvu murdmine<\/a><\/li>\n
  10. C\/C++ kodeerimise harjutus \u2013 leidke duplikaatnumber<\/a><\/li>\n<\/ol>\n
    Enim h\u00e4\u00e4letatud postitused SQL-i kaudu<\/h2>\n
    N\u00fc\u00fcd saame \u00fchendada GD Star Rating<\/a> Plugini h\u00e4\u00e4letusstatistika ja saada veel \u00fche huvitava edetabeli.<\/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
    See annab aasta 10 enim h\u00e4\u00e4li saanud postitust:<\/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. Kui x==x hindab C\/C++ v\u00e4\u00e4rtuseks FALSE?<\/a><\/li>\n
    2. Kuidas leida maksimaalselt kaks t\u00e4isarvu ilma v\u00f5rdlusoperaatorit kasutamata?<\/a><\/li>\n
    3. Kuidas binaarsest otsingupuust s\u00f5lme kustutada?<\/a><\/li>\n
    4. C++ kodeerimisharjutus: kuidas kontrollida, kas suur t\u00e4isarv jagub 11-ga?<\/a><\/li>\n
    5. Microsofti intervjuu k\u00fcsimus \u2013 hankige kolmnurga pindala<\/a><\/li>\n
    6. PPAP C++ keeles ja Javascript algajatele<\/a><\/li>\n
    7. Kuidas binaarpuud C\/C++-s \u00fcmber p\u00f6\u00f6rata?<\/a><\/li>\n
    8. Kuidas p\u00f6\u00f6rata bitte 32-bitise m\u00e4rgita t\u00e4isarvu jaoks C\/C++ keeles?<\/a><\/li>\n
    9. C++ kodeerimisharjutus \u2013 kuidas leida esimene puuduv number?<\/a><\/li>\n
    10. D\u00fcnaamiline programmeerimine \u2013 t\u00e4isarvu murdmine<\/a><\/li>\n<\/ol>\n
      Muidugi v\u00f5ite \u00fclaltoodud m\u00f5lemat SQL-p\u00e4ringut<\/a> muuta nii, et see annaks iga kuu kohta 12 postitust, st \u00fche parima. Peate lihtsalt kuu j\u00e4rgi r\u00fchmitama j\u00e4rgmiselt:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Ja lisaks saate luua PHP lehe<\/a>, mis annab k\u00f5ige ajakohasemad postituste edetabelid, kuid siin n\u00e4idatud on staatilised, st edetabel genereeritakse sellisena, nagu see praegu on.<\/p>\n
      SQL<\/p>\n
      :  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      Kuidas loetleda SQL-i abil aasta enimh\u00e4\u00e4le saanud postitusi?<\/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":[833,894,916,842,802,994,863],"tags":[1165],"class_list":["post-232914","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-juhend-algajatele","category-kood","category-muud","category-opetused","category-php-4","category-sisuturundus","category-wordpress-4","tag-affiai-et"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/232914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/comments?post=232914"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/232914\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media?parent=232914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/categories?post=232914"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/tags?post=232914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}