换了个方法解决了SELECT NAME,LOG.IC,MID(log_time,1,10),MAX(MID(log_time,12,8)) ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
GROUP BY MID(log_time,1,10),LOG.IC
ORDER BY log_time
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
GROUP BY MID(log_time,1,10),LOG.IC
ORDER BY log_time
SELECT NAME,LOG.IC,MID(log_time,1,10),MID(log_time,12,8) ,oprtype
FROM (SELECT NAME,LOG.IC,MID(log_time,1,10),MID(log_time,12,8) ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC) v
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
GROUP BY MID(log_time,1,10),LOG.IC
ORDER BY log_time
确实可以FROM(...)as test 这样写子查询,我自己也是百度到的,不过,这个方法,用在这里,失败了,还会报错的
确实可以FROM(...)as test 这样写子查询,我自己也是百度到的,不过,这个方法,用在这里,失败了,还会报错的请楼主贴出修改过后的SQL语句和错误提示信息。
FROM (SELECT NAME,LOG.IC,MID(log_time,1,10),MID(log_time,12,8) ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC) AS test
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
GROUP BY MID(log_time,1,10),LOG.IC
ORDER BY log_time 在添加了红色部分以后,SQL报错错误代码: 1054
Unknown column 'LOG.IC' in 'field list'原因应该是所使用的LEFT JOIN,在子查询内部,而子查询外部的LOG.IC,就无法识别了。并且,这样写以后,在第一行的选取,也会有错误,因为LOG.IC,MID(long_time,1,10)等字段,因子查询之后重新命名问题,并不真实存在了。
针对这一问题,可这样修改SELECT NAME,卡号,日期,时间 ,oprtype
FROM (SELECT NAME,LOG.IC AS 卡号,MID(log_time,1,10) AS 日期 ,MID(log_time,12,8) AS 时间 ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08') AS test
GROUP BY MID(log_time,1,10),LOG.IC
ORDER BY log_time 依然会报错错误代码: 1054
Unknown column 'log_time' in 'order clause'
FROM (SELECT NAME,LOG.IC AS 卡号,MID(log_time,1,10) AS 日期 ,MID(log_time,12,8) AS 时间 ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08') AS test
GROUP BY 日期,卡号
ORDER BY 日期没有报错了。
现在需要在子查询中添加排序的问题SELECT NAME,卡号,日期,时间 ,oprtype
FROM (SELECT NAME,LOG.IC AS 卡号,MID(log_time,1,10) AS 日期 ,MID(log_time,12,8) AS 时间 ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
ORDER BY log_time DESC ) AS test
GROUP BY 日期,卡号
ORDER BY 日期解决了组内排序
FROM (SELECT NAME,LOG.IC AS 卡号,MID(log_time,1,10) AS 日期 ,MID(log_time,12,8) AS 时间 ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
ORDER BY log_time DESC ) AS test
GROUP BY 日期,卡号
ORDER BY 日期最后一句的按“日期”排序,是否有办法变成按原始字段的“log_time”排序呢?求教!
FROM (SELECT log_time,NAME,LOG.IC AS 卡号,MID(log_time,1,10) AS 日期 ,MID(log_time,12,8) AS 时间 ,oprtype
FROM log_detail LOG LEFT JOIN user_feature USER ON LOG.IC = USER.IC
WHERE log_ip = '192.168.1.230'
AND MID(log_time,1,10) >= '2015-01-08'
AND MID(log_time,1,10) <= '2015-01-08'
ORDER BY log_time DESC ) AS test
GROUP BY 日期,卡号
ORDER BY log_time