{"id":456,"date":"2021-11-24T19:00:00","date_gmt":"2021-11-24T10:00:00","guid":{"rendered":"https:\/\/zeoninc.com\/blog\/?p=456"},"modified":"2021-11-24T16:35:49","modified_gmt":"2021-11-24T07:35:49","slug":"web%e3%82%a8%e3%83%b3%e3%82%b8%e3%83%8b%e3%82%a2%e3%83%a9%e3%82%a4%e3%83%88%e3%81%aa%e7%b5%8c%e9%a8%93%e8%80%85%e5%90%91%e3%81%91%e3%81%absql%e3%82%92%e8%aa%9e%e3%82%8b%e3%80%82%ef%bc%88%e6%98%ad","status":"publish","type":"post","link":"https:\/\/zeoninc.com\/blog\/2021\/11\/24\/web%e3%82%a8%e3%83%b3%e3%82%b8%e3%83%8b%e3%82%a2%e3%83%a9%e3%82%a4%e3%83%88%e3%81%aa%e7%b5%8c%e9%a8%93%e8%80%85%e5%90%91%e3%81%91%e3%81%absql%e3%82%92%e8%aa%9e%e3%82%8b%e3%80%82%ef%bc%88%e6%98%ad\/","title":{"rendered":"WEB\u30a8\u30f3\u30b8\u30cb\u30a2[\u30e9\u30a4\u30c8\u306a\u7d4c\u9a13\u8005]\u5411\u3051\u306bSQL\u3092\u8a9e\u308b\u3002\uff08\u662d\u548c\u306e\u30d2\u30fc\u30ed\u30fc\u3067\u30a2\u30f3\u30b1\u30fc\u30c8\u96c6\u8a08\uff09"},"content":{"rendered":"\n

\u672c\u6295\u7a3f\u306b\u304a\u3044\u3066\u306f\u5b9f\u969b\u306b\u30a2\u30f3\u30b1\u30fc\u30c8\u3092\u884c\u3046\u308f\u3051\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3002\u30a2\u30f3\u30b1\u30fc\u30c8\u306e\u56de\u7b54\u96c6\u8a08\u3092\u901a\u3058\u3066SQL\u3092\u8a9e\u308b\u56de\u3067\u3059\u3002
\u4eca\u56de\u306e\u30cd\u30bf\u306fSQL\uff0bPHP\u3067\u306e\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u306b\u3064\u3044\u3066\u304a\u8a71\u3057\u3092\u3057\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u30a2\u30f3\u30b1\u30fc\u30c8\u306e\u5f97\u7968\u6570\u30ab\u30a6\u30f3\u30c8\u306fSQL\u306b\u3088\u308b\u96c6\u8a08\u306e\u57fa\u790e\u3067\u3042\u308a\u306a\u304c\u3089\u5965\u304c\u6df1\u3044\u3067\u3059\u3002<\/p>\n\n\n\n

1.question\uff08\u8cea\u554f\uff09 \u30c6\u30fc\u30d6\u30eb?id:2\u3092\u984c\u6750\u306b\u3057\u307e\u3059\u3002
2.answer\uff08\u56de\u7b54\uff09 \u30c6\u30fc\u30d6\u30eb
3.vote_history\uff08\u6295\u7968\u5c65\u6b74\uff09\u30c6\u30fc\u30d6\u30eb
\u3053\u306e\uff13\u679a\u306e\u30c6\u30fc\u30d6\u30eb\uff08\u5b9f\u969b\u306b\u306f\u300c2.\u56de\u7b54\u300d\u3068\u300c3.\u6295\u7968\u5c65\u6b74\u300d\u306e2\u679a\u306e\u30c6\u30fc\u30d6\u30eb\uff09\u3092\u4f7f\u3063\u3066\u3001\u96c6\u8a08SQL\uff0bPHP\u306e\u66f8\u304d\u65b9\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n

id\uff08\u4e3b\u30ad\u30fc1\uff09<\/td>name\uff08\u8cea\u554f\u306e\u540d\u79f0\uff09<\/td>start\uff08\u958b\u59cb\u65e5\uff09<\/td>finish\uff08\u7d42\u4e86\u65e5\uff09<\/td><\/tr>
1<\/td>\u4eee\u9762\u30e9\u30a4\u30c0\u30fc\u306e\u4e2d\u3067\u597d\u304d\u306a\u306e\u306f\uff1f<\/td>2021\/11\/22<\/td>2021\/11\/29<\/td><\/tr>
2<\/td>\u30b4\u30ec\u30f3\u30b8\u30e3\u30fc\u306e\u4e2d\u3067\u597d\u304d\u306a\u306e\u306f\uff1f<\/td>2021\/12\/06<\/td>2021\/12\/13<\/td><\/tr>
3<\/td>\u30b8\u30e3\u30c3\u30ab\u30fc\u96fb\u6483\u968a\u306e\u4e2d\u3067\u597d\u304d\u306a\u306e\u306f\uff1f<\/td>2021\/12\/20<\/td>2021\/12\/27<\/td><\/tr><\/tbody><\/table>
?1.question\uff08\u8cea\u554f\uff09\u30c6\u30fc\u30d6\u30eb<\/figcaption><\/figure>\n\n\n\n
q_id
\uff08\u8cea\u554fid:\u4e3b\u30ad\u30fc1\u3001\u5916\u90e8\u53c2\u7167\uff09<\/td>		id\uff08\u4e3b\u30ad\u30fc2\uff09<\/td>name\uff08\u9078\u629e\u80a2\uff09<\/td> align\uff08\u4e26\u3073\u9806\uff09<\/td><\/tr>
2<\/td>1<\/td>\u30a2\u30ab\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>1<\/td><\/tr>
2<\/td>2<\/td>\u30a2\u30aa\u30ec\u30b8\u30e3\u30fc<\/td>2<\/td><\/tr>
2<\/td>3<\/td>\u30ad\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>3<\/td><\/tr>
2<\/td>4<\/td>\u30df\u30c9\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>5<\/td><\/tr>
2<\/td>5<\/td>\u30e2\u30e2\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>4<\/td><\/tr><\/tbody><\/table>
?2.answer\uff08\u56de\u7b54\uff09\u30c6\u30fc\u30d6\u30eb\u306e\u4e00\u90e8<\/figcaption><\/figure>\n\n\n\n
id\uff08\u4e3b\u30ad\u30fc1\uff09<\/td>question_id
\uff08\u8cea\u554fid\u3001\u5916\u90e8\u53c2\u7167\uff09<\/td>		answer_id
\uff08\u56de\u7b54id\u3001\u5916\u90e8\u53c2\u7167\uff09<\/td>		created\uff08\u6295\u7968\u65e5\u6642\uff09<\/td><\/tr>
91<\/td>2<\/td>1<\/td>2021\/12\/06 03:00:00<\/td><\/tr>
92<\/td>2<\/td>2<\/td>2021\/12\/06 06:00:00 <\/td><\/tr>
93<\/td>2<\/td>2<\/td>2021\/12\/06 09:00:00 <\/td><\/tr>
94<\/td>2<\/td>1<\/td>2021\/12\/06 15:00:00 <\/td><\/tr>
95<\/td>2<\/td>5<\/td>2021\/12\/06 21:00:00 <\/td><\/tr>
96<\/td>2<\/td>4<\/td>2021\/12\/07 03:00:00 <\/td><\/tr>
97<\/td>2<\/td>2<\/td>2021\/12\/07 09:00:00 <\/td><\/tr>
98<\/td>2<\/td>1<\/td>2021\/12\/07 12:00:00 <\/td><\/tr>
99<\/td>2<\/td>1<\/td>2021\/12\/08 03:00:00 <\/td><\/tr>
100<\/td>2<\/td>2<\/td>2021\/12\/09 06:00:00 <\/td><\/tr>
101<\/td>2<\/td>4<\/td>2021\/12\/10 09:00:00 <\/td><\/tr>
102<\/td>2<\/td>2<\/td>2021\/12\/11 03:00:00 <\/td><\/tr><\/tbody><\/table>
?3.vote_history\uff08\u6295\u7968\u5c65\u6b74\uff09\u30c6\u30fc\u30d6\u30eb\u306e\u4e00\u90e8<\/figcaption><\/figure>\n\n\n\n

\u2193<\/span><\/p>\n\n\n\n

question_id
\uff08\u8cea\u554fid\uff09<\/td>		id
\uff08\u56de\u7b54id\uff09<\/td>		name
\uff08\u56de\u7b54\u540d\u79f0\uff09<\/td>		 align
\uff08\u8868\u793a\u9806\uff09<\/td>		cnt
\uff08\u5f97\u7968\u6570\uff09<\/td><\/tr>
2<\/td>1<\/td>\u30a2\u30ab\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>1<\/td>4<\/td><\/tr>
2<\/td>2<\/td>\u30a2\u30aa\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>2<\/td>5<\/td><\/tr>
2<\/td>3<\/td>\u30ad\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>3<\/td>0<\/td><\/tr>
2<\/td>5<\/td>\u30e2\u30e2\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>4<\/td>1<\/td><\/tr>
2<\/td>4<\/td>\u30df\u30c9\u30ec\u30f3\u30b8\u30e3\u30fc<\/td>5<\/td>2<\/td><\/tr><\/tbody><\/table>
?4.vote_count\uff08\u96c6\u8a08\uff09\u7d50\u679c\u8868<\/figcaption><\/figure>\n\n\n\n

\u300c2.\u56de\u7b54\u300d\u3068\u300c3.\u6295\u7968\u5c65\u6b74\u300d\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u300c4.\u96c6\u8a08\u7d50\u679c\u306e\u8868\u300d\u3092\u51fa\u529b\u3057\u305f\u3044\u308f\u3051\u3067\u3059\u306d\u3002\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\uff1f\u540c\u4e00\u4eba\u7269\u306e\u4e8c\u91cd\u6295\u7a3f\u5bfe\u7b56\u306f\uff1f\u3068\u3044\u3046\u58f0\u3082\u3042\u308b\u304b\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u307e\u305f\u6b21\u56de\u4ee5\u964d\u306b<\/p>\n\n\n\n

0.\u307e\u305a\u306fPHP\u306e\u30eb\u30fc\u30d7\u3067\u96c6\u8a08\u3092\u9811\u5f35\u308b\u30d1\u30bf\u30fc\u30f3<\/h3>\n\n\n\n

<?php
$sql = \"select * AS cnt from vote_history where question_id = 2\"; \/\/ 1.\u8cea\u554fid:2\u306e\u5c65\u6b74\u3092\u5168\u53d6\u5f97<\/span>
$stmt = $dbh->query($sql);
$logs = array();
while ($row = $stmt->fetchRow()) {
\u3000<\/em>$logs[] = $row;
}
$sql = \"select * from answer where question_id = 2 order by align asc\"; \/\/ 2.\u56de\u7b54\u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u3092\u5168\u53d6\u5f97<\/span>
$stmt = $dbh->query($sql);
$answer_count = array();
while ($row = $stmt->fetchRow()) {
\u3000<\/em><\/code> $row['c_cnt'] = 0;
\u3000<\/em><\/code> foreach ($logs AS $log) {
\u3000<\/em><\/code> \u3000<\/em><\/code> if ($log['qeustion_id'] == $row['qeustion_id'] && $log['qeustion_id'] == $row['id']) {
\u3000<\/em><\/code> \u3000<\/em><\/code> \u3000<\/em><\/code> ++ $row['v_cnt'];
\u3000<\/em><\/code> \u3000<\/em><\/code> }
\u3000<\/em><\/code> }
\u3000<\/em><\/code> $answer_count[] = $row;
}
?><\/code>

PHP\u3067\u306eSQL\u95a2\u6570\u306f\u500b\u3005\u3067\u9055\u3046\uff08\u9593\u9055\u3063\u3066\u3044\u308b\uff1f\uff09\u3068\u601d\u308f\u308c\u308b\u306e\u3067\u30b9\u30eb\u30fc\u304a\u9858\u3044\u3057\u307e\u3059\u3002select\u53e5\u3068from\u53e5\u3068where\u53e5\u3068order\u53e5\u3092\u99c6\u4f7f\u3057\u3066\u96c6\u8a08\u3057\u307e\u3057\u305f\u3002\u3042\u307e\u308a\u898b\u304b\u3051\u306a\u3044\u3067\u3059\u306d\u3002PHP\u5074\u3067\u304c\u3093\u3070\u3063\u3066\u96c6\u8a08\u3059\u308b\u65b9\u6cd5\u3068\u3057\u3066\u6709\u308a\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n\n\n\n

1.\u30eb\u30fc\u30d7\u5185\u3067\u96c6\u5408\u95a2\u6570\u3092\u4f7f\u3063\u305fSQL\u3092\u6295\u3052\u308b\u30d1\u30bf\u30fc\u30f3<\/h3>\n\n\n\n

<?php
\/\/ 1.\u56de\u7b54\u30c6\u30fc\u30d6\u30eb\u3092\u53d6\u5f97<\/span>
$sql = \"select * from answer where question_id = 2 order by align asc\";
$stmt = $dbh->query($sql);
$answer_count = array();
while ($row = $stmt->fetchRow()) {
\u3000<\/em><\/code> \/\/ 2.\u30eb\u30fc\u30d7\u5185\u3067\u96c6\u8a08<\/span>
\u3000<\/em><\/code> $sql = \"select count(*) AS cnt from vote_history where question_id=? and answer_id=?\";
\u3000<\/em><\/code> $stmt2 = $dbh->query($sql, array($row['question_id'], $row['id']));
\u3000<\/em><\/code> $row['v_cnt'] = $stmt2->fetchfield('cnt');
\u3000<\/em><\/code> $answer_count[] = $row;
}
?>
<\/code>
\u307e\u3041\u3001\u3053\u3061\u3089\u306e\u30d5\u30ed\u30fc\u306f\u3088\u304f\u898b\u304b\u3051\u308b\u30d1\u30bf\u30fc\u30f3\u3067\u3059\u3002\u96c6\u8a08\u95a2\u6570count\u306e\u77e5\u8b58\u304c\u3042\u308c\u3070\u554f\u984c\u306a\u304f\u96c6\u8a08\u51fa\u6765\u307e\u3059\u304c\u3001\u30eb\u30fc\u30d7\u306e\u4e2d\u3067\u30af\u30a8\u30ea\u30fc\u3092\u6295\u3052\u308b\u306e\u3067RDB\u3078\u306e\u30b9\u30c8\u30ec\u30b9\u304c\u61f8\u5ff5\u3055\u308c\u307e\u3059\u3002\u5b9f\u306f\u30af\u30a8\u30ea\u30fc\u56de\u6570\u304c\u591a\u3044\u3068\u3044\u3046\u70b9\u3067\u500b\u4eba\u7684\u306b\u306f\u30d1\u30bf\u30fc\u30f3\u300c0\u300d\u3088\u308a\u304a\u52e7\u3081\u3057\u307e\u305b\u3093\u3002\u300c\u753b\u9762\u306e\u30ec\u30b9\u30dd\u30f3\u30b9\u304c\u30e4\u30b1\u306b\u9045\u3044\u306a\u3041\u300d\u3068\u3044\u3046\u3068\u304d\u306b\u306f\u3053\u306e\u30d1\u30bf\u30fc\u30f3\u3067\u66f8\u304b\u308c\u3066\u3044\u308b\u30b1\u30fc\u30b9\u304c\u591a\u3044\u3067\u3059\u3002<\/p>\n\n\n\n

2.\u76f8\u95a2\u526f\u554f\u3044\u5408\u308f\u305b\u3092\u4f7f\u3046\u30d1\u30bf\u30fc\u30f3<\/h3>\n\n\n\n

SQL\u6587\u304c\u4e00\u500b\u3067\u6295\u3052\u308b\u30af\u30a8\u30ea\u30fc\u304c\u4e00\u56de\u306a\u306e\u3067\u30b3\u30fc\u30c9\u90e8\u5206\u306f\u7701\u7565\u3057\u3001SQL\u6587\u306e\u307f\u3092\u63b2\u8f09\u3057\u307e\u3059\u3002

select A.*,(
\u3000<\/em> select count(*) AS v_cnt from vote_history
\u3000<\/em> where question_id = A. question_id and answer_id = A.id -- \u76f8\u95a2\u526f\u554f\u3044\u5408\u308f\u305b <\/span>
) AS v_cnt<\/code>
from answer AS A
where question_id = 2
order by align asc
<\/code>
PHP\u306e\u30b3\u30fc\u30c9\u304b\u3089\u898b\u308b\u3068\u3053SQL\uff11\u6587\u306e\u307f\u3067\u96c6\u8a08\u307e\u3067\u3057\u3066\u304f\u308c\u307e\u3059\u3002PHP\u306e\u30b3\u30fc\u30c9\u306f\u6574\u7136\u3068\u3057\u307e\u3059\u304c\u56de\u7b54\uff11\u30ec\u30b3\u30fc\u30c9\u304b\u3089\u4e00\u56de\u305a\u3064\u96c6\u8a08\u7528\u306e\u30af\u30a8\u30ea\u30fc\u3092RDB\u5185\u90e8\u3067\u9023\u767a\u3057\u3066\u3044\u308b\u70b9\u306f\u30d1\u30bf\u30fc\u30f3\u300c1\u300d\u3068\u5909\u308f\u3089\u306a\u3044\u3067\u3059\u3002\u56de\u7b54\u30c6\u30fc\u30d6\u30eb\u306e\u51fa\u529b\u30ec\u30b3\u30fc\u30c9\uff11\u884c\u306b\u3064\u304d\u5185\u90e8\u3067\u30af\u30a8\u30ea\u3092\u6295\u3052\u3066\u3044\u307e\u3059\u306e\u3067\u3001\u5185\u90e8\u7684\u306b\u306f\u5916\u90e8\u8868\u30671\u56de\u3001\u76f8\u95a2\u526f\u554f\u3044\u5408\u308f\u305b\u30675\u56de\u306e\u8a086\u56de\u3067\u3001\u3042\u307e\u308a\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306f\u3088\u304f\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n\n\n\n

3.group by\u3092\u4f7f\u3063\u3066\u307f\u308b\u3002<\/h3>\n\n\n\n

0\u3068\u540c\u69d8\u306b\u30eb\u30fc\u30d7\u5185\u3067\u5207\u308a\u8cbc\u308a\u3057\u3066\u63a5\u5408\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u304c\u3001PHP\u306e\u30b3\u30fc\u30c9\u306f\u7701\u7565\u3057\u307e\u3059\u3002

1.\u6295\u7968\u7d50\u679c\u3092\u96c6\u8a08\uff081\u56de\u76ee\u306e\u30af\u30a8\u30ea\u30fc\uff09<\/span>
select question_id , answer_id ,count(*) AS v_cnt
from vote_history
where question_id = 2
group by question_id , answer_id
<\/code>2.\u56de\u7b54\u30c6\u30fc\u30d6\u30eb\u306e\u4e00\u89a7\uff082\u56de\u76ee\u306e\u30af\u30a8\u30ea\u30fc\uff09<\/span>
select *
from answer
where question_id = 2
order by align asc
<\/code>
\uff12\u56de\u306e\u30af\u30a8\u30ea\u30fc\u3092\u6295\u3052\u3001PHP\u5074\u3067\u8cea\u554fid\u3001\u56de\u7b54id\u3092\u5143\u306b\u5207\u308a\u8cbc\u308a\u51e6\u7406\u3092\u3057\u3066\u300c4.\u96c6\u8a08\u8868\u300d\u306e\u3088\u3046\u306a\u8868\u304c\u51fa\u6765\u4e0a\u304c\u308a\u307e\u3059\u3002\u4e00\u56de\u76ee\u306e\u30af\u30a8\u30ea\u30fc\u3067\u306f\u30ad\u30ec\u30f3\u30b8\u30e3\u30fc\u306e\u5f97\u7968\u6570\u304c0\u3067\u5f53\u8a72\u884c\u304c\u51fa\u529b\u3055\u308c\u306a\u3044\u305f\u3081\u5de5\u592b\u304c\u5fc5\u8981\u3067\u3059\u3002SQL\u306e\u66f8\u304d\u63db\u3048\u306b\u3088\u308a\u3001\u753b\u9762\u306e\u30ec\u30b9\u30dd\u30f3\u30b9\u7684\u306b\u306f\u30d1\u30bf\u30fc\u30f3\u300c2\u300d\u3088\u308a\u6539\u5584\u3055\u308c\u308b\u30b1\u30fc\u30b9\u304c\u591a\u3005\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n

4.\u526f\u554f\u3044\u5408\u308f\u305b\u306e\u7d50\u679c\u3092\u5143\u306bleft join\u3092\u3057\u3066\u307f\u308b\u3002<\/h3>\n\n\n\n

\u809d\u3068\u306a\u308b\u306e\u306fSQL\u6587\u306a\u306e\u3067\u5f15\u304d\u7d9a\u304dPHP\u306e\u30b3\u30fc\u30c9\u306f\u7701\u7565\u3057\u307e\u3059\u3002

select A.*,if_null(V.CNT,0) AS v_cnt
from answer AS A
left join (
\u3000<\/em><\/code> select question_id , answer_id ,count(*) AS CNT
\u3000<\/em><\/code> from vote_history
\u3000<\/em><\/code> where question_id = 2
\u3000<\/em><\/code> group by question_id, answer_id
) AS V
ON V.question_id = A. question_id AND V.answer_id = A.id
where question_id = 2
order by align asc

<\/code>\u898b\u3065\u3089\u3044\u306e\u3067\u3001\u4e00\u884c\u4e00\u53e5\u3067\u526f\u554f\u3044\u5408\u308f\u305b\u306b\u306f\u30a4\u30f3\u30c7\u30f3\u30c8\u3092\u7d44\u307f\u5408\u308f\u305b\u307e\u3057\u305f\u3002\u300cleft join\u300d\u306e\u30ab\u30c3\u30b3\u5185\u306e\u30af\u30a8\u30ea\u30fc\u304c\u526f\u554f\u3044\u5408\u308f\u305b\u3067\u3059\u3002\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\u3068\u3082\u8a00\u3044\u307e\u3059\u3002\u30af\u30a8\u30ea\u30fc\u306e\u7d50\u679c\u3092\u30c6\u30fc\u30d6\u30eb\u306b\u898b\u7acb\u3066\u3066\u5229\u7528\u3059\u308b\u3053\u3068\u304c\u51fa\u6765\u307e\u3059\u3002\u300cif_null(V.CNT,0)\u300d\u306fid:3\u300c\u30ad\u30ec\u30f3\u30b8\u30e3\u30fc\u300d\u306e\u6295\u7968\u6570\u304c\u300cNULL\u300d\u306b\u306a\u3063\u3066\u3057\u307e\u3046\u306e\u3067\u300c0\u300d\u306b\u3059\u308b\u3088\u3046\u95a2\u6570\u3092\u631f\u3093\u3067\u3044\u307e\u3059\u3002\u300cleft join\u300d\u306e\u30ab\u30c3\u30b3\u5185\u306e\u300ewhere question_id = 2\u300f\u306f\u8981\u3089\u306a\u3044\u3093\u3058\u3083\u306a\u3044\u304b\uff1f\u3068\u3082\u601d\u3044\u307e\u3059\u304c\u3001 \u30d1\u30bf\u30fc\u30f3\u300c0\u300d\u306e\u4e00\u500b\u76ee\u3067\u300e where question_id = 2 \u300f\u3092\u66f8\u304b\u306a\u3044\u306e\u3068\u540c\u3058\u3067\u3059\u3002\u5168\u30ec\u30b3\u30fc\u30c9\u304b\u3089\u96c6\u8a08\u3092\u304b\u3051\u308b\u3053\u3068\u306b\u306a\u308a\u6642\u9593\u304c\u304b\u304b\u308a\u307e\u3059\u3002<\/p>\n\n\n\n

5.left join\u3092\u3057\u3066\u307f\u308b\u3002\u305d\u306e2<\/h3>\n\n\n\n

\u6b21\u306f\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\u3092\u4f7f\u308f\u305a\u306b\u300cleft join\u300d\u3067\u66f8\u3044\u3066\u307f\u307e\u3059\u3002PHP\u306e\u30b3\u30fc\u30c9\u306f\u7701\u7565\u3057\u307e\u3059\u3002

SELECT A.question_id, A.id, A.name, A.align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id, A.name, A.align
order by align asc<\/code>

group by\u53e5\u306b\u5168\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u5165\u308c\u3066\u3044\u308b\u306e\u3067\u9762\u5012\u3067\u3059\u3088\u306d\u3002group by\u306b\u5165\u308c\u308b\u30d5\u30a3\u30fc\u30eb\u30c9\u306e\u30d0\u30a4\u30c8\u6570\u5408\u8a08\u306e\u30ea\u30df\u30c3\u30c8\u3082\u3042\u308b\u306e\u3067\u3001group by\u5185\u3067\u30d1\u30f3\u30af\u3059\u308b\u30ea\u30b9\u30af\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n

6.left join\u3092\u3057\u3066\u307f\u308b\u3002\u305d\u306e3<\/h3>\n\n\n\n

\u30d1\u30bf\u30fc\u30f3\u300c5\u300d\u3067\u306fGROUP BY\u306b\u5168\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u5165\u308c\u307e\u3057\u305f\u304c\u3001\u96c6\u5408\u95a2\u6570\u306b\u5165\u308c\u3066\u3057\u307e\u3048\u3070GROUP BY\u306b\u51fa\u529b\u7528\u306e\u5168\u30ab\u30e9\u30e0\u5165\u308c\u306a\u304f\u3066\u3082\u6e08\u307f\u307e\u3059\u3002

SELECT A.question_id, A.id, MAX(A.name) AS name, MAX(A.align) AS align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id
order by align asc
<\/code>
\u306a\u3093\u304balign\uff08\u8868\u793a\u9806\uff09\u3092MAX\u95a2\u6570\u306b\u5165\u308c\u308b\u306e\u306f\u307e\u3060\u3057\u3082\u3001name\uff08\u56de\u7b54\u306e\u9078\u629e\u80a2\u540d\u79f0\uff09\u3092MAX\u95a2\u6570\u306b\u5165\u308c\u308b\u306e\u306f\u9055\u548c\u611f\u304c\u3042\u308a\u307e\u3059\u304c\u3001group by\u53e5\u306b\u7121\u3044\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u51fa\u3059\u306b\u306f\u3053\u3046\u3059\u308b\u3057\u304b\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n\n\n\n

7.left join\u3092\u3057\u3066\u307f\u308b\u3002\u305d\u306e4<\/h3>\n\n\n\n

\u30d1\u30bf\u30fc\u30f3\u300c5\u300d\u306e\u3088\u3046\u306bselect\u53e5\u306b\u96c6\u5408\u95a2\u6570\u3092\u5165\u308c\u305a\u3001\u30d1\u30bf\u30fc\u30f3\u300c6\u300d\u306e\u3088\u3046\u306bGROUP BY\u53e5\u306b\u3082\u30d5\u30a3\u30fc\u30eb\u30c9\u6307\u5b9a\u3092\u305b\u305a\u306b\u66f8\u3044\u3066\u307f\u308b\u3002

SELECT A.question_id, A.id, A.name, A.align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id
order by align asc<\/code>

\u4e00\u610f\u6027\u304c\u4fdd\u8a3c\u3055\u308c\u308b\u5834\u5408\u3067\u3042\u308c\u3070\u3001\u3053\u306e\u3088\u3046\u306a\u66f8\u304d\u65b9\uff08\u30d1\u30bf\u30fc\u30f3\u300c5\u300d\u304b\u3089group by\u53e5\u3078\u306e\u8a18\u8ff0\u3092\u7701\u7565\uff09\u3067\u3082\u5b9f\u884c\u3067\u304d\u307e\u3059\u304c\u53e4\u3044\u53b3\u683c\u306a\u30bf\u30a4\u30d7\u306eSQL\u3067\u306f\u30eb\u30fc\u30eb\u9055\u53cd\u306a\u306e\u3067SQL\u30a8\u30e9\u30fc\u3067\u7d42\u4e86\u3057\u307e\u3059\u306d\u2026\u3002<\/p>\n\n\n\n

8.inner join\u3092\u3057\u3066\u307f\u308b\u3002<\/h3>\n\n\n\n

\u30d1\u30bf\u30fc\u30f3\u300c7\u300d\u3067\u306f\u53e4\u3044\u30d0\u30fc\u30b8\u30e7\u30f3\u306eRDB\u3067\u306f\u52d5\u304b\u306a\u3044\u30ea\u30b9\u30af\u304c\u3042\u308a\u307e\u3059\u304c\u3001\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3059\u308c\u3070\u30d1\u30bf\u30fc\u30f3\u300c5\u300d\u306e\u3088\u3046\u306bGROUP BY\u53e5\u306b\u5168\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u5165\u308c\u308b\u5fc5\u8981\u3082\u3042\u308a\u307e\u305b\u3093\u3057\u3001\u30d1\u30bf\u30fc\u30f3\u300c6\u300d\u306e\u3088\u3046\u306b\u5f37\u5f15\u306b\u96c6\u5408\u95a2\u6570\u3092\u4f7f\u3046\u5fc5\u8981\u3082\u3042\u308a\u307e\u305b\u3093\u3002

SELECT A.*, A2.v_cnt
FROM answer AS A
inner join (
\u3000<\/em><\/code> select A3.question_id, A3.id count(*) AS v_cnt
\u3000<\/em><\/code> from answer AS A3
\u3000<\/em><\/code> left join vote_history AS V
\u3000<\/em><\/code> ON V.question_id = A3. question_id AND V.answer_id = A3.id AND V.question_id = 2
\u3000<\/em><\/code> where A3.question_id = 2
\u3000<\/em><\/code> GROUP BY A3.question_id, A3.id
) AS A2
ON A2.question_id = A.question_id AND A2.id = A.id
order by A.align asc
<\/code>
\u7d50\u679c\u7684\u306b\u306f\u30d1\u30bf\u30fc\u30f3\u300c4\u300d\u306e\u66f8\u304d\u65b9\u306b\u4f3c\u3066\u3044\u307e\u3059\uff08\u3088\u308a\u8907\u96d1\u306b\u3057\u305f\uff1f\uff09\u304c\u3001ifnull\u95a2\u6570\u3092\u631f\u307e\u305a\u306b\u6e08\u3080\u70b9\u3001\u306e\u30793\u679a\uff08answer\u30922\u679a\uff0bvote_history\u30921\u679a\uff09\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f7f\u7528\u3057\u3066\u3044\u308b\u70b9\u304c\u9055\u3044\u307e\u3059\u3002\u3042\u3048\u3066inner join\u3092\u4f7f\u3046\u305f\u3081\u306b\u66f8\u3044\u305f\u611f\u304c\u5f37\u3044\u3067\u3059\u306d\u3002<\/p>\n\n\n\n

9.\u30d3\u30e5\u30fc<\/em>\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u4f7f\u3046\u30d1\u30bf\u30fc\u30f3<\/h3>\n\n\n\n

\u526f\u554f\u3044\u5408\u308f\u305b\u3068\u306b\u3066\u3044\u307e\u3059\u304c\u3001\u4e3b\u30ad\u30fc\u30fb\uff08\u30e6\u30cb\u30fc\u30af\uff09\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306a\u3069\u306e\u691c\u7d22\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u304c\u7121\u52b9\u306b\u306a\u308a\u307e\u305b\u3093\u3002\uff08\u526f\u554f\u3044\u5408\u308f\u305b\u306e\u7d50\u679c\u306b\u306f\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u304c\u4f55\u3082\u52b9\u3044\u3066\u307e\u305b\u3093\uff09\u5177\u4f53\u7684\u306a\u66f8\u304d\u65b9\u306f\u7701\u7565\u3057\u307e\u3059\u3002<\/strong><\/p>\n\n\n\n

10.TEMPORARY TABLE\uff08\u4e00\u6642\u30c6\u30fc\u30d6\u30eb\uff09\u3092\u4f7f\u3046\u30d1\u30bf\u30fc\u30f3<\/h3>\n\n\n\n

\u7ba1\u7406\u753b\u9762\u306a\u3069\u3001\u30ea\u30af\u30a8\u30b9\u30c8\u6570\u304c\u5897\u3048\u306a\u3044\u30b7\u30b9\u30c6\u30e0\u306a\u3089\u6709\u308a\u304b\u3068\u601d\u3044\u307e\u3059\u3002\u81ea\u5206\u3060\u3051\u306e\u30bb\u30c3\u30b7\u30e7\u30f3\u3067\u8352\u3089\u3057\u3066\u3082\u3001\u4ed6\u306e\u30bb\u30c3\u30b7\u30e7\u30f3\u306b\u306f\u5f71\u97ff\u3092\u4e0e\u3048\u307e\u305b\u3093\u3057\u3001\u4ed6\u306e\u30bb\u30c3\u30b7\u30e7\u30f3\u304b\u3089\u81ea\u5206\u3060\u3051\u306e\u30bb\u30c3\u30b7\u30e7\u30f3\u3067\u5024\u3092\u6539\u3056\u3093\u3055\u308c\u308b\u30ea\u30b9\u30af\u3082\u3042\u308a\u307e\u305b\u3093\u3002\u5177\u4f53\u7684\u306a\u66f8\u304d\u65b9\u306f\u7701\u7565\u3057\u307e\u3059\u3002<\/strong><\/p>\n\n\n\n

\u6700\u5f8c\u306b\u3001\u7d50\u5c40\u30d1\u30bf\u30fc\u30f30~10\u3067\u3069\u308c\u304c\u304a\u30b9\u30b9\u30e1\u304b\uff1f<\/h3>\n\n\n\n

\u3069\u308c\u3082\u5b9f\u904b\u7528\u306b\u306f\u304a\u30b9\u30b9\u30e1\u3067\u306f\u306a\u3044\u3067\u3059\u306d\u3002\u30e9\u30a4\u30c8\u306a\u96c6\u8a08\u30fb\u8868\u793a\u3067\u3042\u308c\u3070\u3069\u308c\u3067\u3082\u69cb\u3044\u307e\u305b\u3093\u3002\u304a\u597d\u307f\u306b\u5408\u308f\u305b\u3066\u66f8\u304for\u65e2\u5b58\u306e\u66f8\u304d\u65b9\u306b\u6e96\u3048\u308b\u306a\u3069\u304c\u5b9c\u3057\u3044\u304b\u3068\u601d\u3044\u307e\u3059\u3002\u7279\u306b\u6700\u8fd1\u306eSNS\u3067\u6d41\u884c\u308a\u306e\u300c\u3044\u3044\u306d\u300d\u300c\u9ad8\u8a55\u4fa1\u30fb\u4f4e\u8a55\u4fa1\u300d\u306a\u3069\u3001\u591a\u6570\u306e\u30e6\u30fc\u30b6\u30fc\u304c\u30dc\u30bf\u30f3\u3092\u62bc\u3059\u3060\u3051\u3067\u30ec\u30b3\u30fc\u30c9\u3092\u633f\u5165\u3059\u308b\u3053\u3068\u306b\u306a\u308a\u3001\u30ed\u30fc\u30c7\u30a3\u30f3\u30b0\u6642\u9593\u304c\u9577\u304f\u306a\u308a\u30b5\u30a4\u30c8\u5229\u7528\u8005\u306f\u95b2\u89a7\u30b9\u30c8\u30ec\u30b9\u304c\u84c4\u7a4d\u3055\u308c\u3066\u3044\u304f\u4e8b\u3067\u3057\u3087\u3046\u3002Twitter\u3001YouTube\u306a\u3069\u306e\u300c\u3044\u3044\u306d\u300d\u300c\u9ad8\u8a55\u4fa1\u300d\u306f\u30ec\u30b3\u30fc\u30c9\u884c\u6570\u304c\u6570\u5343\u5104\u304f\u3089\u3044\u884c\u3063\u3066\u308b\u3068\u601d\u308f\u308c\u307e\u3059\u3002\u305d\u308c\u306a\u306e\u306b\u6570\u5104\u884c\u30ec\u30d9\u30eb\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u96c6\u8a08SQL\u3092\u6295\u3052\u308b\u306a\u3093\u3066\u73fe\u5b9f\u7684\u3067\u306f\u306a\u3044\u3088\u3046\u306a\u6c17\u304c\u3057\u307e\u3059\u3002<\/p>\n\n\n\n

\u3053\u3046\u3044\u3063\u305f\u554f\u984c\u306f\u30ea\u30ea\u30fc\u30b9\u76f4\u5f8c\u306f\u5feb\u9069\u306b\u52d5\u3044\u3066\u3044\u3066\u3082\u3001\u5e74\u6708\u306e\u7d4c\u904e\u3068\u3068\u3082\u306b\u52d5\u304d\u304c\u30b9\u30ed\u30fc\u306b\u611f\u3058\u3089\u308c\u308b\u3088\u3046\u306b\u306a\u3063\u3066\u304d\u307e\u3059\u306e\u3067\u65e5\u9803\u306e\u76e3\u8996or\u5b9a\u671f\u7684\u306a\u30c1\u30e5\u30fc\u30cb\u30f3\u30b0\u3082\u5fc5\u8981\u3067\u3059\u3002<\/p>\n\n\n\n

\u666e\u6bb5\u3042\u307e\u308a\u6c17\u306b\u306f\u3057\u3066\u3044\u307e\u305b\u3093\u304c\u3001\u8a18\u4e8b\u306b\u3057\u3066\u307f\u3088\u3046\u3068\u601d\u3044\u8d77\u3053\u305b\u3070\u540c\u3058\u7d50\u679c\u3092\u51fa\u3059\u305f\u3081\u306eSQL\u3067\u308211\u30d1\u30bf\u30fc\u30f3\u3042\u308b\u306e\u306b\u306f\u81ea\u5206\u3067\u3082\u9a5a\u304d\u307e\u3057\u305f\u3002\u666e\u6bb5\u306f\u982d\u306e\u4e2d\u3067\u8003\u3048\u305f\u308a\u3001\u53e3\u982d\u3067\u4f1d\u3048\u308b\u30ec\u30d9\u30eb\u306a\u306e\u3067\u4f55\u4ef6\u304f\u3089\u3044\u3042\u308b\u304b\u306f\u8a18\u4e8b\u306b\u3057\u3066\u307f\u308b\u4e8b\u3067\u6539\u3081\u3066\u5b9f\u611f\u3057\u305f\u6b21\u7b2c\u3067\u3059\u3002<\/p>\n\n\n\n

\u4eca\u56de\u306f\u30ac\u30e9\u30b1\u30fc\u3001\u30cd\u30aa\u30d2\u30b9\u30c8\u30ea\u30c3\u30af\u30ab\u30fc\u3001windows 2000\u3001VHS\u306e\u30d3\u30c7\u30aa\u30c6\u30fc\u30d7\u3001CD\u30e9\u30b8\u30ab\u30bb\u3001\u30d5\u30a1\u30df\u30b3\u30f3\uff06\u30c7\u30a3\u30b9\u30af\u30b7\u30b9\u30c6\u30e0\u304c\u5927\u597d\u304d\u306a\u304a\u3058\u3055\u3093\u304cSQL\uff08\u300cgroup by\u300d\u3001\u300c\u7d50\u5408\uff08join\uff09\u300d\u3001\u300c\u526f\u554f\u3044\u5408\u308f\u305b\uff08\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\uff09\u300d\u3001\u300c\u96c6\u5408\u95a2\u6570\uff08count\u3001max\uff09\u300d\uff09\u306b\u3064\u3044\u3066\u8a9e\u308b<\/span><\/strong>\u56de\u3067\u3057\u305f\u3002\u6b21\u56de\u306e\u6295\u7a3f\u3067\u306fUNIX\/LINUX\u306e\u30b3\u30de\u30f3\u30c9<\/strong><\/span>\u306b\u3064\u3044\u3066\u8a9e\u308d\u3046\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n\n\n\n

\"\"
\u30ac\u30bd\u30ea\u30f3\u8eca\u3092\u697d\u3057\u3081\u308b\u306e\u306f\u3042\u3068\u4f55\u5e74\u304b\uff1f<\/figcaption><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"

\u672c\u6295\u7a3f\u306b\u304a\u3044\u3066\u306f\u5b9f\u969b\u306b\u30a2\u30f3\u30b1\u30fc\u30c8\u3092\u884c\u3046\u308f\u3051\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3002\u30a2\u30f3\u30b1\u30fc\u30c8\u306e\u56de\u7b54\u96c6\u8a08\u3092\u901a\u3058\u3066SQL\u3092\u8a9e\u308b\u56de\u3067\u3059\u3002\u4eca\u56de\u306e\u30cd\u30bf\u306fSQL\uff0bPHP\u3067\u306e\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u306b\u3064\u3044\u3066\u304a\u8a71\u3057\u3092\u3057\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u30a2\u30f3\u30b1\u30fc\u30c8\u306e\u5f97\u7968\u6570\u30ab\u30a6\u30f3\u30c8\u306fSQL\u306b\u3088 […]<\/p>\n","protected":false},"author":3,"featured_media":689,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,8],"tags":[9],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/posts\/456"}],"collection":[{"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/comments?post=456"}],"version-history":[{"count":159,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/posts\/456\/revisions"}],"predecessor-version":[{"id":850,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/posts\/456\/revisions\/850"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/media\/689"}],"wp:attachment":[{"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/media?parent=456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/categories?post=456"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zeoninc.com\/blog\/wp-json\/wp\/v2\/tags?post=456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}