SELECT gq_sendxx.FromMid,gq_sendxx.ToMid,gq_sendxx.Content,gq_sendxx.DateTime,gq_member.Picture_small,gq_member.NickName,gq_member.Id FROM(SELECT * FROM gq_sendxx ORDER BY DateTime DESC)AS s JOIN gq_member ON gq_sendxx.FromMid = gq_member.Id and gq_sendxx.FromMid <> '157' or gq_sendxx.ToMid = gq_member.Id and gq_sendxx.ToMid <> '157' WHERE gq_sendxx.`FromMid`='157' and gq_sendxx.`Fromhide` = '0' or gq_sendxx.`ToMid`='157' and gq_sendxx.`Tohide` = '0' GROUP BY gq_sendxx.ToMid,gq_sendxx.FromMid ORDER BY gq_sendxx.`DateTime` desc上边语句哪里错了?要是不加 FROM(SELECT * FROM gq_sendxx ORDER BY DateTime DESC)AS s 就可以查找出来 求解
SELECT gq_sendxx.FromMid,gq_sendxx.ToMid,
gq_sendxx.Content,gq_sendxx.DateTime,
gq_member.Picture_small,gq_member.NickName,gq_member.Id
(SELECT * FROM gq_sendxx ORDER BY DATETIME DESC)AS s
FROM gq_sendxx
JOIN gq_member ON gq_sendxx.FromMid = gq_member.Id
AND gq_sendxx.FromMid <> '157'
OR gq_sendxx.ToMid = gq_member.Id
AND gq_sendxx.ToMid <> '157'
WHERE gq_sendxx.`FromMid`='157'
AND gq_sendxx.`Fromhide` = '0'
OR gq_sendxx.`ToMid`='157'
AND gq_sendxx.`Tohide` = '0'
GROUP BY gq_sendxx.ToMid,gq_sendxx.FromMid
ORDER BY gq_sendxx.`DateTime` desc
SELECT
s.FromMid,
s.ToMid,
s.Content,
s.DateTime,
gq_member.Picture_small,
gq_member.NickName,
gq_member.Id
FROM(
SELECT * FROM gq_sendxx ORDER BY DateTime DESC
)AS s
JOIN gq_member
ON s.FromMid = gq_member.Id and ( s.FromMid <> '157' or s.ToMid = gq_member.Id ) and s.ToMid <> '157'
WHERE s.FromMid = '157'
and ( s.Fromhide = '0' or s.ToMid ='157' )
and s.`Tohide` = '0'
GROUP BY s.ToMid, s.FromMid ORDER BY s.DateTime desc;
应该注意的几个问题:
1. 尽量避免使用保留字作为字段名.
2. 当为表定义别名后,使用别名作为前缀.
3. 当多表连接时尽量明确连接类型,如inner/left/right/out.
4. on后面的条件尽量简单,把逻辑判断尽量放到where之后.