select dt=convert(varchar(13),[時間欄位],120),
qty=count(1)
from [表名]
group by convert(varchar(13),[時間欄位],120)
order by convert(varchar(13),[時間欄位],120)
同樣的陳述句 為什么SQL能正常執行，但MYsql確不能呢？
請教各位大神
uj5u.com熱心網友回復：
select dt=convert(varchar(13),event_time,120),qty=count(1)
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
group by convert(varchar(13),event_time,120)
order by convert(varchar(13),event_time,120)
[SQL]select dt=convert(varchar(13),event_time,120),
qty=count(1)
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
group by convert(varchar(13),event_time,120)
order by convert(varchar(13),event_time,120)
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(13),event_time,120),
qty=count(1)
from th_event_info
where ev' at line 1
下面是回傳報錯 但不知道哪里有問題
uj5u.com熱心網友回復：
Convert函式的用法不對：1. convert(value, 型別)
2. 可用型別有：Binary, char(), data, time, datetime, decimal, signed, unsigned
所以應該是： select convert(event_time, char(13)) *****
uj5u.com熱心網友回復：
select dt=convert(event_time,varchar(13),120),
qty=count(1)
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
group by convert(event_time,varchar(13),120)
order by convert(event_time,varchar(13),120)
[SQL]select dt=convert(event_time,varchar(13),120),
qty=count(1)
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
group by convert(event_time,varchar(13),120)
order by convert(event_time,varchar(13),120)
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(13),120),
qty=count(1)
from th_event_info
where event_time be' at line 1
是這樣改嗎 還是報錯。。。哪里有錯嗎
uj5u.com熱心網友回復：
MYSQL 中用 DATE_FORMAT 函式uj5u.com熱心網友回復：
select DATE_FORMAT(event_time,'%Y-%m-%%d %H:%i:%S') as dt,
count(1) as qty
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
group by DATE_FORMAT(event_time,'%Y-%m-%%d %H:%i:%S')
order by DATE_FORMAT(event_time,'%Y-%m-%%d %H:%i:%S')
uj5u.com熱心網友回復：
非常感謝您，我能在問下如下的情況下
select DATE_FORMAT(event_time,'%Y-%m-%d') as dt,
count(1) as qty
from th_event_info
where event_time between '2019-09-23 00:00:34.000' and '2019-09-25 00:00:34.000'
and event_type=1 and event_level='一級'
group by DATE_FORMAT(event_time,'%Y-%m-%d')
order by DATE_FORMAT(event_time,'%Y-%m-%d')
這樣已經初步實作了我的需求。
如果我想同時查出統計一級以及二級的數量 能一個陳述句直接實作嗎？
現在的結果是這樣，
2019-09-23 33
2019-09-24 215
能否在生成一列，而統計是的二級的數量
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/18325.html
標籤：MySQL
下一篇：索引有點不懂求有經驗人士指點