我正试图从该表中获取max(日期)、count(id)和*look_name(最大(日期)*:
id | member_id | look_week | look_name | is_pinned | date
1 | 1 | 3 | the improviser | yes | 2013-11-19 21:57:04
2 | 1 | 2 | destined for stardom | yes | 2013-11-19 21:56:00
2 | 1 | 1 | fashinably corporate | no | 2013-11-19 21:54:00
这是我的疑问:-
$sql="SELECT COUNT(id) as total_pins,MAX(pinned_date) as last_activity_date FROM pin_info WHERE member_id='1' AND is_pinned='yes'";
我得到这个数组作为输出。
//[total_pins] => 2
//[last_activity_date] => 2013-11-19 21:57:04
//[lookname_for_last_date] => i am stuck at this?
如何操作此查询,以便在此数组中获取look_name for max(date)?
步骤1:首先,您必须选择MAX(pinned_date)。我假设这是针对一个特定成员的,并且is_pinned='yes'
步骤2:然后,您必须选择look_name,其中pinned_date等于上面找到的最大日期。您可以通过在步骤2 中进行步骤1和内部查询来做到这一点
步骤3:最后,步骤2中的查询作为主查询中的第三列
SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,
(select look_name
from pin_info B
where A.member_id=B.member_id and A.is_pinned=B.is_pinned
and pinned_date in (
select max(pinned_date)
from pin_info C
where B.member_id=C.member_id and B.is_pinned=C.is_pinned
) AS lookname_for_last_date
),
(
select max(pinned_date)
from pin_info C
where A.member_id=C.member_id and A.is_pinned=C.is_pinned
) AS CHK_LAST_DATE
FROM pin_info A
WHERE member_id='1'
AND is_pinned='yes'
要对此进行检查或调整,请参阅此处的SQL Fiddle。
大部分都是相似的。。这给了我确切的结果。
$sub_query="SELECT MAX(pinned_date) FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";
$sql = "SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,(SELECT look_name FROM pin_info WHERE member_id='$member_id' AND pinned_date=($sub_query)) as last_pinned_look FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";
我会选择一个子选项:
SELECT COUNT(id) as total_pins, MAX(pinned_date) as last_activity_date,
(SELECT look_name FROM pin_info WHERE pinned_date = (SELECT MAX(pinned_date) FROM look_info)) as lookNameForMaxDate
FROM pin_info WHERE member_id='%s' AND is_pinned='yes'