数据库查询需要帮助 [英] Help needed in Database Query

问题描述

我的数据库中有以下表格，关系是一对多。消息将发送给多个用户。并希望得到用户发送的消息的列表摘要。

 tbl_message（message_id，subject，content，category）
 tbl_log_message（log_id，msg_id， sent_by，sent_on，status）

i想要获取消息主题，以及具有消息状态的特定用户发送的消息总数。

像这样

 msg_subject被发送给50个用户。 Pending = 20，Draft = 10，Delivered = 15，failed = 5 

 log_id ... msg_id ... sent_to ... ... sent_by ... sent_on ... status 
  1  ............  5  ...... [e-mail] ... [e-mail] ... 15-10-2014 ...草案
  2  ............  5  ...... [e-mail] ... admin @ gmail.com ... 15-10-2014 ...草稿
  3  ............  5  ...... [e-mail] ... [e-mail] ... 15-10-2014 ...等待
  4  ............  5  ...... [e-mail] ... [e-mail] ... 15-10-2014 ...交付
  5  ........... 。 7  ...... [e-mail] ... [e-mail] ... 15-12-2014 ...已发送
  6  ............  7  ...... abc6 @gmail。 com ... [e-mail] ... 15-12-2014 ...已交付
  7  .......... ..  7  ...... [e-mail] ... [e-mail] ... 15-12-2014 ...待定
  8  ............  7  ...... [e-mail] ... [e-mail] ... 15-12-2014 ...等待
  9  ...... ......  7  ...... [e-mail] ... [e-mail] ... 15-12-2014 .. 。草稿
  10  ...........  7  ... ..abc10 @ gmail.com ... [e-mail] ... 15-12-2014 ...失败

到目前为止我已经写了这个查询。

  SELECT  msg_id，COUNT（log_id），sent_on  AS  Sent_ON，log_status 
  FROM  log_messages 
  WHERE  from_name = '  [e-mail]' 
  GROUP   BY  msg_id，sent_on，log_status; 

但它会为待处理，已发送的消息返回单独的行。

解决方案

使用查询累积数据时有一些规则...

查询提供的所有数据必须是GROUP BY子句的一部分或必须计算（比如COUNT，MAX ...）...

所以如果你想添加sent_on，你应该这样做：

  SELECT  message_id，COUNT（log_id），sent_on  AS 用户
  FROM  log_messages 
  WHERE  from_name = '  [e-mail]' 
  GROUP   BY  message_i d，sent_on; 

您可以使用案例陈述来达到您的要求..

例如：

 选择 count（sent_to）为 TotalUsers，
 count（ case  当 log_status = ' 待定' 然后 log_status  end ） as  TotalPending，
 count（ case  何时 log_status = ' 草稿' 然后 log_status  end ） as  TotalDraft，
 count（ case  何时 log_stat us = ' 已发送' 然后 log_status < span class =code-keyword> end ） as  TotalDelivered，
 count（ case  当 log_status = ' 失败' 然后 log_status  end ） as  TotalFailed 
 来自 log_messages 
 其中 from_name =  '  [e-mail]' 
  GROUP   BY  message_id，sent_to，sent_on，log_status; 

使用这样的东西并尝试..

i have following tables in my db, relation is one to many. a message is sent to multiple users. and want to get a list summary of messages sent by a user.

tbl_message(message_id, subject, content, category)
tbl_log_message(log_id, msg_id, sent_by, sent_on, status)

i want to fetch message subject, and total numbers of messages, sent_on by a particular user with message status.
like this

msg_subjectwas sent to 50 users. Pending = 20, Draft = 10, Delivered = 15, failed = 5

log_id ... msg_id ... sent_to ......    sent_by ...      sent_on   ... status
[e-mail][e-mail]... 15-10-2014... Draft
[e-mail][e-mail]... 15-10-2014... Draft
[e-mail][e-mail]... 15-10-2014... Pending
[e-mail][e-mail]... 15-10-2014... Delivered
[e-mail][e-mail]... 15-12-2014... Delivered
[e-mail][e-mail]... 15-12-2014... Delivered
[e-mail][e-mail]... 15-12-2014... Pending
[e-mail][e-mail]... 15-12-2014... Pending
[e-mail][e-mail]... 15-12-2014... Draft
[e-mail][e-mail]... 15-12-2014... Failed 

I have write this query so far.

SELECT msg_id, COUNT(log_id), sent_on AS Sent_ON, log_status
FROM log_messages
WHERE from_name = '[e-mail]'
GROUP BY msg_id, sent_on, log_status;

but it returns separate rows for pending, delivered, messages.

解决方案

There are certain rules when you accumulate data using query...
All data that presented by the query must be part of the GROUP BY clause or must be computed (like COUNT, MAX...)...
So if you want to add sent_on, you should do something like this:

SELECT message_id, COUNT(log_id), sent_on AS Users
FROM log_messages
WHERE from_name = '[e-mail]'
GROUP BY message_id, sent_on;

You can use case statements to achieve your requirement..
For example:

select count(sent_to) as TotalUsers,
count(case when log_status='Pending' then log_status end) as TotalPending,
count(case when log_status='Draft' then log_status end) as TotalDraft,
count(case when log_status='Delivered' then log_status end) as TotalDelivered,
count(case when log_status='Failed' then log_status end) as TotalFailed
from log_messages
where from_name = '[e-mail]'
GROUP BY message_id,sent_to, sent_on,log_status;

use some thing like this and try..

这篇关于数据库查询需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！