请教MySQL中产品排序的问题
比如:我有一个产品表,然后产品表里面有用户的产品更新时间(lastChangedByUser)和管理员的产品更新时间(lastUpdatedByAdmin)等两个字段,然后获取产品数据的时候,想按照哪个时间最新就按照哪个时间排序。应该如何在mysql中处理?谢谢
现在的mysql语句如下:
SELECT p.productId,p.productTitle,s.supplierName AS sName FROM products AS p,suppliers AS s WHERE p.supplierId=s.supplierId AND s.language='en' ORDER BY p.lastChangedByUser DESC,p.lastUpdatedByAdmin DESC LIMIT 0,12
up
down
试试这个?
SELECT p.productId, p.productTitle, s.supplierName AS sName, if(p.lastChangedByUser > p.lastUpdatedByAdmin, p.lastChangedByUser, p.lastUpdatedByAdmin) AS lastModified FROM products AS p, suppliers AS s WHERE p.supplierId = s.supplierId AND s.language = 'en' ORDER BY lastModified DESC LIMIT 0, 12
该问题已经被删除,只有管理员和作者可以修改后重新发布!
up
down
这是在drupal中?
好吧,不管是不是了,给你个思路就是,根据用户行为去组织查询排序条件
比如:
<?php $sql = $where = $order = ''; $sql = 'SELECT p.productId,p.productTitle,s.supplierName AS sName FROM products AS p,suppliers AS s'; $where = "WHERE p.supplierId=s.supplierId AND s.language='en'"; // 这里我偷懒了,因为sql语句中有单引号,我就偷懒用双引号作为字符串起止了 $orderby = ''; // 这里可以根据用户行为获取一个标识 if ($orderby == 'user') { $order = 'ORDER BY p.lastChangedByUser DESC,p.lastUpdatedByAdmin DESC LIMIT 0,12'; // 先按user,再按admin } else { $order = 'ORDER BY p.lastUpdatedByAdmin DESC,p.lastChangedByUser DESC LIMIT 0,12'; // 先按admin,再按user } $sql = $sql . $where . $order; ... db_query($sql); ... //你的剩下的逻辑过程 ?>
该问题已经被删除,只有管理员和作者可以修改后重新发布!
up
down
kent
向我提问我才会告诉你哦~
0 票
套用@陈木辉的,如下:
SELECT p.productId, p.productTitle, s.supplierName AS sName FROM products AS p, suppliers AS s WHERE p.supplierId = s.supplierId AND s.language = 'en' ORDER BY if(p.lastChangedByUser > p.lastUpdatedByAdmin, p.lastChangedByUser, p.lastUpdatedByAdmin) DESC LIMIT 0, 12
该问题已经被删除,只有管理员和作者可以修改后重新发布!
请先 登录之后,再提交问题答案!