iT邦幫忙

0

mysql slect 如何選出出現最多的不重複次數

id□□□date□□□subject
1□□□2015-04-26□□□國文
2□□□2015-04-27□□□英文
3□□□2015-05-01□□□數學
4□□□2015-05-01□□□數學
5□□□2015-05-01□□□英文
6□□□2015-05-01□□□英文
7□□□2015-05-14□□□社會
8□□□2015-05-14□□□社會
9□□□2015-05-15□□□國文
10□□□2015-05-15□□□國文
11□□□2015-05-15□□□數學
12□□□2015-05-15□□□數學
13□□□2015-05-15□□□英文
14□□□2015-05-15□□□英文

php + mysql 如何選出
本月,同一天中,科目,最多的《次數》

根據以上資料表,2015-05-15有最多的3個不同科目,所以希望輸出為→3
初次發問,如有錯誤,懇請惠予指正

2 個回答

8
一級屠豬士
iT邦高手 1 級 ‧ 2015-05-22 09:01:31
最佳解答
<pre class="c" name="code">CREATE TABLE ithelp150522a(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`date` DATE NOT NULL,
subject CHAR(6) NOT NULL
);

INSERT INTO ithelp150522a(`date`, subject) VALUES
('2015-04-26', '國文'),
('2015-04-27', '英文'),
('2015-05-01', '數學'),
('2015-05-01', '數學'),
('2015-05-01', '英文'),
('2015-05-01', '英文'),
('2015-05-14', '社會'),
('2015-05-14', '社會'),
('2015-05-15', '國文'),
('2015-05-15', '國文'),
('2015-05-15', '數學'),
('2015-05-15', '數學'),
('2015-05-15', '英文'),
('2015-05-15', '英文');

SELECT MAX(sbjt_cnt)   
  FROM (SELECT `date` as 'CDate'
             , COUNT(DISTINCT subject) AS 'sbjt_cnt'
          FROM ithelp150522a
         WHERE MONTH(`date`) = MONTH(CURDATE())
         GROUP BY `date`) a;

+---------------+
| MAX(sbjt_cnt) |
+---------------+
|             3 |
+---------------+
看更多先前的回應...收起先前的回應...

簽名筆記拍手

<pre class="c" name="code">變化一下
SELECT `date` as 'CDate'
     , COUNT(DISTINCT subject) AS 'max_cnt'
  FROM ithelp150522a
 WHERE MONTH(`date`) = MONTH(CURDATE())
 GROUP BY `date`
HAVING COUNT(DISTINCT subject) = (SELECT MAX(sbjt_cnt)   
                                     FROM (SELECT `date` as 'CDate'
                                                , COUNT(DISTINCT subject) AS 'sbjt_cnt'
                                             FROM ithelp150522a
                                            WHERE MONTH(`date`) = MONTH(CURDATE())
                                            GROUP BY `date`) a);


+------------+---------+
| CDate      | max_cnt |
+------------+---------+
| 2015-05-15 |       3 |
+------------+---------+

上面的程式碼這樣排

感謝大大解惑,東繞西繞好幾天,終於豁然開朗..

用以下 PHP 輸出方式,是否完美?

$sql="
SELECT MAX(sbjt_cnt)
FROM (SELECT date as 'CDate'
, COUNT(DISTINCT subject) AS 'sbjt_cnt'
FROM ithelp150522a
WHERE MONTH(date) = MONTH(CURDATE())
GROUP BY date) a";

$rows = mysql_query( $sql );
$row = mysql_fetch_row( $rows );

echo $row[0];

0
enen1980
iT邦研究生 1 級 ‧ 2015-05-22 09:03:25

感謝大大指點,初學SQL,真的才感覺《學而後知不足》

臉紅

我要發表回答

立即登入回答