SQL SELECT顶行和聚合计数

I can't wrap my head around this. I have a client-staff communication system. On the admin side, I'm trying to retrieve a list of users, which contains total number of messages, total new messages, last message date, last message for that user.

我无法绕过这个。我有一个客户 - 员工沟通系统。在管理员方面,我正在尝试检索用户列表,其中包含消息总数,新消息总数,最后消息日期,该用户的最后消息。

A bit of logic:

一点逻辑:

sender or receiver value of 0, means staff sent or received it.

发送方或接收方的值为0,表示员工发送或接收它。

sender or receiver value of X, means user with ID value of X received or sent it.

X的发送者或接收者值表示接收或发送了ID值为X的用户。

Sample Data:

ID  |date                   |new    |sender |receiver   |message
----+-----------------------+-------+-------+-----------+------------
1   |2013-12-22 08:19:12    |1      |2      |0          |Hello
2   |2013-12-22 08:23:19    |0      |0      |2          |Hello There
3   |2013-12-22 08:23:19    |0      |0      |4          |Hello There
4   |2013-12-22 10:09:44    |1      |2      |0          |The latest message

Desired Output:

user ID |total_messages |total_new  |latest_date            |latest_messsage
------------------------------------------------------------------------------
2       |3              |2          |2013-12-22 10:09:44    |The latest message
4       |1              |0          |2013-12-22 08:23:19    |Hello There

I'm using SQL Server 2008

我正在使用SQL Server 2008

3 个解决方案

#1

Split the logic of selecting the first row and the aggregate rows in two queries and UNION the results.

拆分在两个查询中选择第一行和聚合行的逻辑,并对结果进行UNION。

SELECT TOP 1 --columns-- 
FROM yourTable

UNION ALL

SELECT --columns-- 
FROM yourTable
GROUP BY --columns--

(without more details about the table structure or what is the desired output, this is the best answer I can come up with)

(没有关于表结构的更多细节或所需的输出,这是我能想到的最佳答案)

#2

I don't know what is your database tables. but I can quess you have a message table and a user table.

我不知道你的数据库表是什么。但我可以告诉你有一个消息表和一个用户表。

SELECT 
    [userId], 
    (SELECT 
         count(*)
     FROM messages
     WHERE u.userId = messages.UserId) AS totalMessages, 
    (SELECT
         TOP 1 [TimeStamp]
     FROM messages
     WHERE messages.UserId = u.UserId
     ORDER BY messages.MessageId DESC), 
    (SELECT
         TOP 1 [Message]
     FROM messages
     WHERE messages.UserId = u.UserId
     ORDER BY messages.MessageId DESC)
FROM users u

You can compute sender and receiver columns like this.

您可以像这样计算发送方和接收方列。

#3

Since it doesn't seem to matter whether a user sent or received for your totals, one way you could do this is by listing message data by user (sender or receiver) in a single column (I used a CTE), then using that set for aggregation and JOIN it to itself to get latest_message

由于用户是否为您的总计发送或接收似乎无关紧要,因此您可以通过在单个列(我使用CTE)中列出用户(发件人或接收者)的消息数据,然后使用该方法设置聚合并将其加入自身以获取latest_message

WITH Users_CTE AS
(
  SELECT sender AS user_id, ID, new, date, message
  FROM Messages
  WHERE sender <> 0
  UNION
  SELECT receiver AS user_id, ID, new, date, message
  FROM Messages
  WHERE receiver <> 0
)

SELECT m.user_id, m.total_messages, m.total_new, m.latest_date, u.message AS latest_message
FROM (SELECT user_id
        , COUNT(*) AS total_messages
        , SUM(new) AS total_new
        , MAX(date) AS latest_date
      FROM Users_CTE
      GROUP BY user_id) AS m
  INNER JOIN Users_CTE AS u ON u.user_id = m.user_id
    AND u.date = m.latest_date

sqlfiddle

#1