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的记录
请赐教！
最佳答案
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";
其他回答(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
今天又改了改 应该是
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";
这样就可以了
