首页 新闻 搜索 专区 学院

mysql语句

0
悬赏园豆:10 [已解决问题] 解决于 2017-03-09 13:36

 

两张表  第一张表 mytb_cloudFile  第二张表 mytb_shareFile

第二张表中最后两条记录是一样的,我想得到最近新的一条记录,也就是最后一条记录

SELECT a.*,b.addTime AS shareTime,b.toObjType,b.deptCode,b.fromJID AS shareFromJID,b.toJID AS shareToJID,b.receiverlimit,b.aproverLimit
FROM mytb_cloudFile a
JOIN
(SELECT toObjType,deptCode,f_id,fromJID,status,approvalLevel,delFlag, GROUP_CONCAT(toJID) AS toJID,addTime,receiverlimit,aproverLimit
FROM mytb_shareFile where status = 1 and delFlag = 0 and (toJID LIKE '%facebook%') GROUP BY f_id ) AS b
ON a.id = b.f_id
where a.delFlag = 0
order by shareTime DESC

但这样 只能得到第一条记录也就是mytb_shareFile中id为510的记录 我想得到id为511的记录

请赐教!

飘飘城的主页 飘飘城 | 初学一级 | 园豆:69
提问于:2016-12-22 17:15
< >
分享
最佳答案
0

String jidLikeOne = jid.concat("%");
String jidLikeTwo = "%,".concat(jid).concat(",%");
String jidLikeThree = "%,".concat(jid);

 

String sql = "SELECT a.*,b.addTime AS shareTime,b.toObjType,b.deptCode,b.fromJID AS shareFromJID,b.toJID AS shareToJID,b.receiverlimit,b.aproverLimit "
+ "FROM mytb_cloudFile a "
+ "JOIN "
+ "(SELECT id,f_id,toJID,fromJID,toObjType,addTime,deptCode,status,approvalLevel,fromLevel,toLevel,delFlag,receiverlimit,aproverLimit FROM "
+ "(SELECT id,f_id,toJID,fromJID,toObjType,addTime,deptCode,status,approvalLevel,fromLevel,toLevel,delFlag,receiverlimit,aproverLimit from mytb_shareFile ORDER BY addTime DESC) "
+ " mytb_shareFile where status = 1 and delFlag = 0 and (toJID LIKE '" + jidLikeOne + "' or toJID like '" + jidLikeTwo + "' or toJID like '" + jidLikeThree + "') GROUP BY f_id order by addTime desc) AS b "
+ "ON a.id = b.f_id "
+ "where a.delFlag = 0 "
+ "order by shareTime desc";

飘飘城 | 初学一级 |园豆:69 | 2016-12-23 14:33
其他回答(1)
0

表mytb_sharefile 中 这句话确定能查出数据?

SELECT toObjType,deptCode,f_id,fromJID,status,approvalLevel,delFlag, GROUP_CONCAT(toJID) AS toJID,addTime,receiverlimit,aproverLimit
FROM mytb_shareFile where status = 1 and delFlag = 0 and (toJID LIKE '%facebook%') GROUP BY f_id。

怎么觉你只group by 一个字段,除了红色字段其他字段能查出来吗?

收获园豆:10
小何人家 | 园豆:329 (菜鸟二级) | 2016-12-23 11:03

 今天又改了改 应该是 

sql = "SELECT a.*,b.addTime AS shareTime,b.toObjType,b.deptCode,b.fromJID AS shareFromJID,b.toJID AS shareToJID,b.receiverlimit,b.aproverLimit "
+ "FROM mytb_cloudFile a "
+ "JOIN "
+ "(SELECT id,f_id,toJID,fromJID,toObjType,addTime,deptCode,status,approvalLevel,fromLevel,toLevel,delFlag,receiverlimit,aproverLimit FROM "
+ "(SELECT id,f_id,toJID,fromJID,toObjType,addTime,deptCode,status,approvalLevel,fromLevel,toLevel,delFlag,receiverlimit,aproverLimit from mytb_shareFile ORDER BY addTime DESC) "
+ " mytb_shareFile where status = 1 and delFlag = 0 and (toJID LIKE '" + jidLikeOne + "' or toJID like '" + jidLikeTwo + "' or toJID like '" + jidLikeThree + "') GROUP BY f_id order by addTime desc) AS b "
+ "ON a.id = b.f_id "
+ "where a.delFlag = 0 "
+ "order by shareTime desc";

这样就可以了

支持(0) 反对(0) 飘飘城 | 园豆:69 (初学一级) | 2016-12-23 14:32
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册