Sql 语句将行减半
这是我当前的 sql 查询，
SELECT
w.city, t.tmc, t.date, t.time, w.event,
ROUND(AVG(w.Pave), 0) surface_temperature,
ROUND(AVG(w.Air), 0) air_temperature,
ROUND(AVG(a.material_rate),0) material_rate,
w.intensity, t.avg
FROM winter2010.traffictest t
LEFT OUTER JOIN winter2010.application a ON a.DATE = t.DATE AND a.tmc = t.tmc AND a.tmc = t.tmc AND Left(a.time,2) = Left(t.time, 2)
LEFT OUTER JOIN winter2010.weather w ON t.DATE = w.DATE AND t.tmc = w.tmc AND Left(t.time, 2) = Left(w.time, 2)
GROUP BY tmc, t.time
INTO OUTFILE 'c:/sass2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES
TERMINATED BY '\n';
我正在从以下 sql 语句创建此 csv 文件，它工作得很好，除了一件事，它只占用 1/2 行！
TrafficTest一共有2661行 当我将数据导出到 csv 时，它只输入 1331。
我更改了流量测试的大小，这也是正确的，除非我只有 3 行，它实际上拉出了所有三行。
任何帮助解决这个问题的帮助将不胜感激。
Here is my current sql query
SELECT
w.city, t.tmc, t.date, t.time, w.event,
ROUND(AVG(w.Pave), 0) surface_temperature,
ROUND(AVG(w.Air), 0) air_temperature,
ROUND(AVG(a.material_rate),0) material_rate,
w.intensity, t.avg
FROM winter2010.traffictest t
LEFT OUTER JOIN winter2010.application a ON a.DATE = t.DATE AND a.tmc = t.tmc AND a.tmc = t.tmc AND Left(a.time,2) = Left(t.time, 2)
LEFT OUTER JOIN winter2010.weather w ON t.DATE = w.DATE AND t.tmc = w.tmc AND Left(t.time, 2) = Left(w.time, 2)
GROUP BY tmc, t.time
INTO OUTFILE 'c:/sass2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES
TERMINATED BY '\n';
I'm creating this csv file from the following sql statement and it works great except for one thing, its only taking 1/2 the rows!
There are a total of 2661 rows in traffictest
And when I outfile the data into the csv its only putting 1331.
I've changed the size of traffictest and this holds true except when I had only 3 rows it actually pulled all three rows.
Any help figuring this out would be much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当您使用 GROUP BY 时，MySql 每组仅返回一行，您有具有相似 tmc-time 的行，并且 MySql 对于所有此类行仅返回一行，这就是行数减少的原因。
When you use
GROUP BYMySql returns only one row per group, You have rows with similar tmc-time and MySql is returning only one row for all such rows that's the reason for decrease in row number.