{"id":721,"date":"2006-11-05T02:06:07","date_gmt":"2006-11-04T17:06:07","guid":{"rendered":"http:\/\/zone.maple4ever.net\/blog\/archives\/721\/"},"modified":"2006-11-05T02:21:11","modified_gmt":"2006-11-04T17:21:11","slug":"%e4%bc%81%e7%94%bb%e5%80%92%e3%82%8c%e3%81%aesql","status":"publish","type":"post","link":"http:\/\/zone.maple4ever.net\/blog\/archives\/721\/","title":{"rendered":"\u4f01\u753b\u5012\u308c\u306eSQL"},"content":{"rendered":"

MySQL 4,0 \u7cfb\u306f\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\u3064\u304b\u3048\u3093\u306e\u304b\u3002\u3002 orz<\/p>\n

WP \u3067\u307f\u306a\u3055\u3093\u304b\u3089\u3044\u305f\u3060\u3044\u305f\u30b3\u30e1\u30f3\u30c8\/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u96c6\u8a08\u304b\u3089\u3001\u30ea\u30f3\u30af\u96c6\u3092\u3064\u304f\u308b\u30fb\u30fb\u30fb\u3068\u3044\u3046\u30d7\u30e9\u30b0\u30a4\u30f3\u3092\u8003\u3048\u3066\u3044\u305f\u306e\u3067\u3059\u304c\u3001\u305b\u3063\u304b\u304f\u66f8\u3044\u305f SQL \u304c\u4f01\u753b\u5012\u308c\u3067\u3057\u305f\u3002\u3000\u3068\u307b\u307b\u3002<\/p>\n

MySQL 4.1 \u4ee5\u964d\u306e\u304b\u305f\u306f\u3046\u3054\u304f\u3068\u601d\u3046\u306e\u3067\u904a\u3093\u3067\u304f\u3060\u3055\u3044\u3002 \ud83d\ude42<\/p>\n

\"phpMyAdmin\"<\/p>\n

  1.  SELECT\r<\/span><\/li>\n
  2.      <\/span>all_comments<\/span>.<\/span>author<\/span>,\r<\/span><\/li>\n
  3.      <\/span>all_comments<\/span>.<\/span>author_url<\/span>,\r<\/span><\/li>\n
  4.      <\/span>all_comments<\/span>.<\/span>hostname<\/span>,\r<\/span><\/li>\n
  5.      <\/span>MAX<\/span>(<\/span>all_comments<\/span>.<\/span>comment_count<\/span>) <\/span>as <\/span>comment_count<\/span>,\r<\/span><\/li>\n
  6.      <\/span>MAX<\/span>(<\/span>all_comments<\/span>.<\/span>trackback_count<\/span>) <\/span>as <\/span>trackback_count<\/span>,\r<\/span><\/li>\n
  7.      <\/span>(<\/span>comment_count<\/span>+<\/span>trackback_count<\/span>) <\/span>as <\/span>all_count\r<\/span><\/li>\n
  8.  <\/span>FROM\r<\/span><\/li>\n
  9.      <\/span>((<\/span>SELECT\r<\/span><\/li>\n
  10.          <\/span>comment<\/span>.<\/span>comment_author <\/span>as <\/span>author<\/span>,\r<\/span><\/li>\n
  11.          <\/span>comment<\/span>.<\/span>comment_author_url <\/span>as <\/span>author_url<\/span>,\r<\/span><\/li>\n
  12.          <\/span>count<\/span>(<\/span>comment<\/span>.<\/span>comment_author<\/span>) <\/span>as <\/span>comment_count<\/span>,\r<\/span><\/li>\n
  13.          <\/span>'' <\/span>as  <\/span>trackback_count<\/span>,\r<\/span><\/li>\n
  14.          <\/span>SUBSTRING_INDEX<\/span>(<\/span>REPLACE<\/span>(<\/span>comment<\/span>.<\/span>comment_author_url<\/span>, <\/span>'<\/span>http:\/\/<\/span>'<\/span>, <\/span>''<\/span>)<\/span>, <\/span>'<\/span>\/<\/span>'<\/span>, <\/span>1<\/span>) <\/span>as <\/span>hostname\r<\/span><\/li>\n
  15.      <\/span>FROM\r<\/span><\/li>\n
  16.          <\/span>wp_comments <\/span>as <\/span>comment\r<\/span><\/li>\n
  17.      <\/span>WHERE\r<\/span><\/li>\n
  18.          <\/span>comment_approved<\/span> = <\/span>'<\/span>1<\/span>' <\/span>AND\r<\/span><\/li>\n
  19.          <\/span>comment_author_url<\/span> <> <\/span>'' <\/span>AND\r<\/span><\/li>\n
  20.          <\/span>comment_type<\/span> = <\/span>''\r<\/span><\/li>\n
  21.      <\/span>GROUP BY\r<\/span><\/li>\n
  22.          <\/span>hostname\r<\/span><\/li>\n
  23.      <\/span>ORDER BY\r<\/span><\/li>\n
  24.          <\/span>comment<\/span>.<\/span>comment_count <\/span>desc<\/span>,\r<\/span><\/li>\n
  25.          <\/span>hostname<\/span>)\r<\/span><\/li>\n
  26.      <\/span>UNION\r<\/span><\/li>\n
  27.      <\/span>(<\/span>SELECT\r<\/span><\/li>\n
  28.          <\/span>trackback<\/span>.<\/span>comment_author <\/span>as <\/span>author<\/span>,\r<\/span><\/li>\n
  29.          <\/span>trackback<\/span>.<\/span>comment_author_url <\/span>as <\/span>author_url<\/span>,\r<\/span><\/li>\n
  30.          <\/span>'' <\/span>as  <\/span>comment_count<\/span>,\r<\/span><\/li>\n
  31.          <\/span>count<\/span>(<\/span>trackback<\/span>.<\/span>comment_author<\/span>) <\/span>as <\/span>trackback_count<\/span>,\r<\/span><\/li>\n
  32.          <\/span>SUBSTRING_INDEX<\/span>(<\/span>REPLACE<\/span>(<\/span>trackback<\/span>.<\/span>comment_author_url<\/span>, <\/span>'<\/span>http:\/\/<\/span>'<\/span>, <\/span>''<\/span>)<\/span>, <\/span>'<\/span>\/<\/span>'<\/span>, <\/span>1<\/span>) <\/span>as <\/span>hostname\r<\/span><\/li>\n
  33.      <\/span>FROM\r<\/span><\/li>\n
  34.          <\/span>wp_comments <\/span>as <\/span>trackback\r<\/span><\/li>\n
  35.      <\/span>WHERE\r<\/span><\/li>\n
  36.          <\/span>trackback<\/span>.<\/span>comment_approved<\/span> = <\/span>'<\/span>1<\/span>' <\/span>AND\r<\/span><\/li>\n
  37.          <\/span>trackback<\/span>.<\/span>comment_author_url<\/span> <> <\/span>'' <\/span>AND\r<\/span><\/li>\n
  38.          <\/span>trackback<\/span>.<\/span>comment_type<\/span> = <\/span>'<\/span>trackback<\/span>'\r<\/span><\/li>\n
  39.      <\/span>GROUP BY\r<\/span><\/li>\n
  40.          <\/span>hostname\r<\/span><\/li>\n
  41.      <\/span>ORDER BY\r<\/span><\/li>\n
  42.          <\/span>trackback<\/span>.<\/span>comment_count <\/span>desc<\/span>,\r<\/span><\/li>\n
  43.          <\/span>hostname<\/span>)) <\/span>as <\/span>all_comments\r<\/span><\/li>\n
  44.  <\/span>GROUP BY\r<\/span><\/li>\n
  45.      <\/span>all_comments<\/span>.<\/span>hostname\r<\/span><\/li>\n
  46.  <\/span>ORDER BY\r<\/span><\/li>\n
  47.      <\/span>all_count <\/span>desc<\/span><\/li><\/ol><\/div>\n

    #\u884c\u756a\u53f7\u306f\u524a\u9664\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n

    \u5b9f\u306f \u30b3\u30e1\u30f3\u30c8+\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u306e\u5408\u8a08\u304c\u30d0\u30b0\u3063\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u3001\u3001\u306a\u3093\u3067\u3060\u308d\u3046\u3002<\/p>\n

    \u3064\u304f\u308b\u3068\u3057\u305f\u3089 PHP \u306e\u30e1\u30e2\u30ea\u3067\u30b5\u30de\u30eb\u3057\u304b\u306a\u3044\u304b\u306a\uff5e\u3002\u3000\u826f\u3044\u6848\u601d\u3044\u3064\u3044\u305f\u304b\u305f\u306f\u6559\u3048\u3066\u304f\u3060\u3055\u3044\u3002 \ud83d\ude1b<\/p>\n","protected":false},"excerpt":{"rendered":"

    MySQL 4,0 \u7cfb\u306f\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\u3064\u304b\u3048\u3093\u306e\u304b\u3002\u3002 orz WP \u3067\u307f\u306a\u3055\u3093\u304b\u3089\u3044\u305f\u3060\u3044\u305f\u30b3\u30e1\u30f3\u30c8\/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u96c6\u8a08\u304b\u3089\u3001\u30ea\u30f3\u30af\u96c6\u3092\u3064\u304f\u308b\u30fb\u30fb\u30fb\u3068\u3044\u3046\u30d7\u30e9\u30b0\u30a4\u30f3\u3092\u8003\u3048\u3066\u3044\u305f\u306e\u3067\u3059\u304c\u3001\u305b\u3063\u304b\u304f\u66f8\u3044\u305f SQL \u304c\u4f01\u753b\u5012\u308c\u3067\u3057\u305f\u3002\u3000\u3068\u307b\u307b\u3002 MySQL 4.1 \u4ee5\u964d\u306e\u304b\u305f\u306f\u3046\u3054\u304f\u3068\u601d\u3046\u306e\u3067\u904a\u3093\u3067\u304f\u3060\u3055\u3044\u3002 \ud83d\ude42  SELECT      all_comments.author,      all_comments.author_url,      all_comments.hostname,      MAX(all_comments.comment_count) as comment_count,      MAX(all_comments.trackback_count) as trackback_count,      (comment_count+trackback_count) as all_count  FROM      ((SELECT          comment.comment_author as author,          comment.comment_author_url as author_url, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/posts\/721"}],"collection":[{"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/comments?post=721"}],"version-history":[{"count":0,"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/posts\/721\/revisions"}],"wp:attachment":[{"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/media?parent=721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/categories?post=721"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/zone.maple4ever.net\/blog\/wp-json\/wp\/v2\/tags?post=721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}