各位大大，想請問一下
我有個資料表Message
date | message
2020-05-15 | 測試
2020-06-13 | 測試
2020-06-15 | 測試
2020-06-19 | 測試
可以怎麼寫統計"測試"這字串在當月份出現的次數呢?
謝謝!!
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE Temp(Xdate DATE, Xmessage varchar(20));
insert into Temp values ('2020-05-15','測試');
insert into Temp values ('2020-05-15','xx');
insert into Temp values ('2020-06-13 ','測試');
insert into Temp values ('2020-06-15','測試');
insert into Temp values ('2020-06-19','測試');
insert into Temp values ('2020-06-19','zz');
insert into Temp values ('2020-06-19','zz');
insert into Temp values ('2020-06-19','zz');
insert into Temp values ('2020-06-19','yy');
select Substring(Xdate,1,7) as XMonth,Xmessage,count(Xmessage) as XCount
from Temp
where Xmessage = '測試'
group by Substring(Xdate,1,7),Xmessage
感謝大大幫忙!!
抱歉大大~我跑出來不順~是哪邊錯誤呢?
<?php
// 建立MySQL的資料庫連接
$link = mysql_connect("localhost","root","12345");
mysql_select_db("message2",$link);
$sql = mysql_query("select Substring(datetime,1,7) as Month,message,count(message) as Count from message2 where message = '測試' group by Substring(datetime,1,7),message");
echo "<table border='1'>
<tr>
<th>Month</th>
<th>message</th>
<th>Count</th>
</tr>";
while($row = mysql_fetch_array($sql)){
echo "<tr>";
echo "<td>" . $row['Month'] . "</td>";
echo "<td>" . $row['message'] . "</td>";
echo "<td>" . $row['Count'] . "</td>";
echo "</tr>";
}
?>
<?php
$servername = "localhost";
$username = "root";
$password = "12345";
$dbname = "test";
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("資料庫連線失敗: " . mysqli_connect_error());
}
$sql = "select Substring(datetime,1,7) as Month,message,count(message) as Count from message2
where message = '測試'
group by Substring(datetime,1,7),message;";
$result = mysqli_query($conn, $sql);
echo "<table border='1'>
<tr>
<th>Month</th>
<th>message</th>
<th>Count</th>
</tr>";
if (mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['Month'] . "</td>";
echo "<td>" . $row['message'] . "</td>";
echo "<td>" . $row['Count'] . "</td>";
echo "</tr>";
}
}
echo "</table>";
mysqli_close($conn);
?>
$servername = "localhost";
連不起來的話改用 IP 參考如下
mysql 訪問許可權
$dbname = "test";
你的 dbname 不會是 "message2" 那是 TableName ,
可能會是 "mysql"
感謝大大
我剛再測試一下where message = '測試'不能為中文字
改成where message = 'test'
即可正常
不能中文字有甚麼方式可以解決嗎?
測試資料：
CREATE TABLE test(
DateField datetime Primary Key,
Message nvarchar(200)
);
INSERT INTO test (DateField, Message)
VALUES ('2020-05-15', '測試'),
('2020-06-13', '測試'),
('2020-06-15', '測試'),
('2020-06-19', '測試')
查詢：
SELECT CONCAT(YEAR(DateField),'/',MONTH(DateField)) AS YearMonth,
Message,
COUNT(Message)
FROM test
GROUP BY CONCAT(YEAR(DateField),'/',MONTH(DateField)),
Message
SELECT DATE_FORMAT(date,'%Y%m') AS ym,message,count(*) AS num FROM `user_agents` group BY ym,message
可善用mysql的函數 DATE_FORMAT 來幫你格式化好你要的日期。
以上的方式比較快速。
可能有人會問 DATE_FORMAT 對字串能否進行轉換。答案是可以的。
只要是正確的日期格式就行。它本身就是用字串格式轉換用的。
對了，忘了說，這招是因為你是日期才能這樣幹。
如果不是日期的話。就得還是用其它兩位字串切法才行。
iThome鐵人賽
看影片追技術