这个查询非常接近,但是我的平庸的sql技能让我无法包装…

$messages = array();
$unread_messages_total = 0;

$messages_query = "
SELECT m.*,COUNT(r.id) AS num_replies,MAX(r.datetime) AS reply_datetime,(m.archived NOT LIKE '%,".$cms_user['id'].",%') AS message_archive,(m.viewed LIKE '%,%') AS message_viewed,SUM(r.viewed NOT LIKE '%,%') AS unread_replies,CASE
        WHEN MAX(r.datetime) >= m.datetime THEN MAX(r.datetime)
        ELSE m.datetime
        END AS last_datetime
FROM directus_messages AS m
LEFT JOIN directus_messages as r ON m.id = r.reply
WHERE m.active = '1'  
AND (m.to LIKE '%,%' OR m.to = 'all' OR m.from = '".$cms_user['id']."') 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY last_datetime DESC";

foreach($dbh->query($messages_query) as $row_messages){
    $messages[] = $row_messages;
    $unread_messages_total += (strpos($row_messages['archived'],','.$cms_user['id'].',') === false && ( (strpos($row_messages['viewed'],') === false && $row_messages['unread_replies'] == NULL) || ($row_messages['unread_replies']>0 && $row_messages['unread_replies'] != NULL) ) )? 1 : 0;
}

提前感谢您提供的任何帮助！

编辑：(数据库)

CREATE TABLE `cms_messages` (
  `id` int(10) NOT NULL auto_increment,`active` tinyint(1) NOT NULL default '1',`subject` varchar(255) NOT NULL default '',`message` text NOT NULL,`datetime` datetime NOT NULL default '0000-00-00 00:00:00',`reply` int(10) NOT NULL default '0',`from` int(10) NOT NULL default '0',`to` varchar(255) NOT NULL default '',`viewed` varchar(255) NOT NULL default ',`archived` varchar(255) NOT NULL default ',PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

编辑2：(要求)

>返回特定user_id的所有父消息：$cms_user [‘id’]
>返回该父消息的回复数：num_replies
>返回该父消息的未读回复数：unread_replies
>返回父消息的日期或最近的回复：last_datetime
>返回消息是否在归档文件中：message_archive
>返回消息是否已被查看：message_viewed
>以DESC datetime顺序返回所有消息
>返回最新消息,从父或回复如果有一些(如gmail)

select
    root_message.id,root_message.active,root_message.subject,case
        when max_reply_id.max_id is null then 
            root_message.message
        else
            reply_message.message
    end as message,root_message.datetime,root_message.reply,root_message.from,root_message.to,root_message.viewed,root_message.archived
from
    -- basic data
    cms_messages as root_message
    -- ID of last reply for every root message
    left join (
        select 
            max(id) as max_id,reply as parent_id 
        from 
            cms_messages
        where
            reply <> 0 
        group by 
            reply
    ) as max_reply_id on max_reply_id.parent_id = root_message.id                                              
    left join cms_messages as reply_message on reply_message.id = max_reply_id.max_id
where
    root_message.reply = 0

它使用子查询max_reply_id作为数据源来选择最新答案的ID.如果存在(即,如果有答案),则使用reply_message.message.如果不存在(根消息未找到答案),则使用root_message.message.

你也应该考虑表的结构.例如,如果回应包含NULL(如果是父消息)或现有消息的ID,则会更有意义.目前,您将其设置为0(不存在的消息的ID),这是错误的.查看和归档的类型也很奇怪.

编辑：你也应该避免使用having子句.在可能的地方使用.

这是一个满足您的要求的新查询.如果它有任何问题(即如果它返回错误的数据),让我知道.

像第一个查询一样,它：

>使用子查询reply_summary来累积关于回复的数据(最后回复的ID,回复的数量和未读回复的数量);
>将此子查询加入到基表中;
>根据reply_summary.max_reply_id将cms_messages作为reply_message连接到子查询,以获取有关最后一个回复(消息,datetime)的数据.

我已经简化了你如何确定last_datetime的方式 – 它现在需要上次回复的时间(如果有任何回复)或原始帖子的时间(当没有发现回复时).

我没有过滤回复和来自字段.如果有必要,应该更新reply_summary子查询的where子句.

select
    parent_message.id,parent_message.subject,parent_message.message,parent_message.from,parent_message.to,coalesce(reply_summary.num_replies,0) as num_replies,last_reply_message.datetime as reply_datetime,(parent_message.archived NOT LIKE '%,{$cms_user['id']},(parent_message.viewed   LIKE     '%,reply_summary.unread_replies,coalesce(last_reply_message.message,parent_message.message) as last_message,coalesce(last_reply_message.datetime,parent_message.datetime) as last_datetime
from
    cms_messages as parent_message
    left join (
        select
            reply as parent_id,max(id) as last_reply_id,count(*) as num_replies,sum(viewed not like '%,%') as unread_replies
        from
            cms_messages
        where
            reply <> 0 and
            active = 1
        group by
            reply
    ) as reply_summary on reply_summary.parent_id = parent_message.id
    left join cms_messages as last_reply_message on last_reply_message.id = reply_summary.last_reply_id
where
    parent_message.reply = 0 and
    parent_message.active = 1 and
    (parent_message.to like '%,%' or parent_message.to = 'all' or parent_message.from = '{$cms_user['id']}')
order by
    last_datetime desc;

解决方法

protected void CheckBoxList1_SelectedindexChanged(object sender,EventArgs e)
{
    int lastSelectedindex = 0;
    string lastSelectedValue = string.Empty;

    foreach (ListItem listitem in CheckBoxList1.Items)
    {
        if (listitem.Selected)
        {
            int thisIndex = CheckBoxList1.Items.IndexOf(listitem);

            if (lastSelectedindex < thisIndex)
            {
                lastSelectedindex = thisIndex;
                lastSelectedValue = listitem.Value;
            }
        }
    }
}

protected void CheckBoxList1_SelectedindexChanged(object sender,EventArgs e)
{
    string value = string.Empty;

    string result = Request.Form["__EVENTTARGET"];

    string[] checkedBox = result.Split('$'); ;

    int index = int.Parse(checkedBox[checkedBox.Length - 1]);

    if (CheckBoxList1.Items[index].Selected)
    {
        value = CheckBoxList1.Items[index].Value;
    }
    else
    {

    }
}

class Program{
   public static void Main(){
      Thread thr;
      thr = Thread.CurrentThread;
      thr.Name = "Main thread";
      Console.WriteLine("Name of current running " + "thread: {0}", Thread.CurrentThread.Name);
      Console.WriteLine("Id of current running " + "thread: {0}", Thread.CurrentThread.ManagedThreadId);
      Console.ReadLine();
   }
}

Name of current running thread: Main thread
Id of current running thread: 1

解决方法

MyBean bean = (MyBean) new InitialContext().lookup( name );