表结构以及记录:
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+
| chanceitemId | chanceId | contactTime | linkmanId | contactKind | cont
actMode | userId | content | nextTime | chance | volume | saleTime | phase |
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+
| 66 | 26 | 2010-12-23 17:23:00 | 0 | 509 |
0 | 0 | a2 | NULL | 0 | NULL | NULL | 0 |
| 67 | 26 | 2010-12-07 17:23:00 | 0 | 509 |
0 | 1 | a1 | NULL | 0 | NULL | NULL | 0 |
| 73 | 27 | 2010-12-28 17:24:00 | 0 | 509 |
0 | 0 | b1 | NULL | 30 | 123 | NULL | 0 |
| 74 | 27 | 2010-12-29 17:24:00 | 0 | 509 |
0 | 0 | b2 | NULL | 0 | 1234 | NULL | 0 |
| 75 | 27 | 2010-12-30 17:25:00 | 0 | 509 |
0 | 3 | b3 | NULL | 0 | NULL | NULL | 0 |
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+目的,查该表中,各个chanceId对应的最近一次contactTime的记录以及该chanceId对应的chanceitem记录数。
使用了以下的sql:
select chanceitemId,count(chanceitemId) as cnt,chanceId,contactTime,content,nextTime,chance,volume, saleTime from t_cust_chanceitem group by chanceId having contactTime in (select Max(contactTime) from t_cust_chanceitem GROUP by CHANCEID);但是得不到chanceId=27的记录值,请问该如何写这句sql?
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+
| chanceitemId | chanceId | contactTime | linkmanId | contactKind | cont
actMode | userId | content | nextTime | chance | volume | saleTime | phase |
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+
| 66 | 26 | 2010-12-23 17:23:00 | 0 | 509 |
0 | 0 | a2 | NULL | 0 | NULL | NULL | 0 |
| 67 | 26 | 2010-12-07 17:23:00 | 0 | 509 |
0 | 1 | a1 | NULL | 0 | NULL | NULL | 0 |
| 73 | 27 | 2010-12-28 17:24:00 | 0 | 509 |
0 | 0 | b1 | NULL | 30 | 123 | NULL | 0 |
| 74 | 27 | 2010-12-29 17:24:00 | 0 | 509 |
0 | 0 | b2 | NULL | 0 | 1234 | NULL | 0 |
| 75 | 27 | 2010-12-30 17:25:00 | 0 | 509 |
0 | 3 | b3 | NULL | 0 | NULL | NULL | 0 |
+--------------+----------+---------------------+-----------+-------------+-----
--------+--------+---------+----------+--------+--------+----------+-------+目的,查该表中,各个chanceId对应的最近一次contactTime的记录以及该chanceId对应的chanceitem记录数。
使用了以下的sql:
select chanceitemId,count(chanceitemId) as cnt,chanceId,contactTime,content,nextTime,chance,volume, saleTime from t_cust_chanceitem group by chanceId having contactTime in (select Max(contactTime) from t_cust_chanceitem GROUP by CHANCEID);但是得不到chanceId=27的记录值,请问该如何写这句sql?
解决方案 »
- 送分贴子,要的进来抢,求一条简单的SQL语句,希望指点!
- 一个帐号可以针对不同的表设定权限不?比如帐户A对T1表可读写,对T2表只读?
- 关于mysql server版本
- mysql 密码为空 导入sql脚本问有什么方法比避免回车
- 求助~一条SqlServer2000的触发器语句转换为mysql5.0的触发器语句
- html/jsp/asp文件内容如何录入数据库?
- 分不高,请教个问题,请大家帮忙!MYSQL的恢复
- 啊啊啊,到底该怎么解决呢?
- 求高手帮我分析下mysql错误日志
- mysql 数据库备份问题
- 求助一个SQL查询问题
- 请教linux下c/c++程序读取pgsql数据库中的utf8字段问题!
from (
select *
from t_cust_chanceitem
Order By contactTime DESC
) t
group by chanceId
select chanceitemId,count(chanceitemId) as cnt,chanceId,contactTime,content,nextTime,chance,volume, saleTime from t_cust_chanceitem group by chanceId
的结果是什么?
然后再执行 having contactTime in (select Max(contactTime) from t_cust_chanceitem GROUP by CHANCEID);判断。