查询以使用Group by子句从多个表中获取数据 [英] Query to get data from multiple tables with Group by clause

问题描述

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

log_id ... msg_id ... sent_to ...... sent_by ... sent_on ...状态

1 ....... ..... 5 ...... [e-mail] ... [e-mail] ... 15-10-2014 ......草案

2 .... ........ 5 ...... [e-mail] ... [e-mail] ... 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 ...... [e-mail] ... [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 ...失败



我希望通过msg id获取所有电子邮件的状态msg主题，内容也是。

i已经写了这个查询，

 选择 COUNT（logmsg.log_id） AS  TotalUsers，logmsg.log_status，msg.message_subject，msg.message_content 
  FROM  log_messages logmsg，messages msg 
  WHERE  logmsg.message_id =  41  和 msg.message_id =  41  
  GROUP  < span class =code-keyword> BY  logmsg.log_status，logmsg.log_status，msg.message_subject，msg.message_content; 

但我也希望得到电子邮件列表。

解决案

使用加入获取你的清单。



你可以参考这里加入：



http://www.w3schools.com/sql/sql_join_inner.asp [ ^ ]

检查这个

 选择 COUNT（logmsg.log_id） AS  TotalUsers，logmsg.log_status，msg.message_subject，msg.message_content 
 < span class =code-keyword> FROM  log_messages logmsg 
  INNER   JOIN 消息msg  On  logmsg.message_id = msg.message_id 
  GROUP   BY  logmsg.log_status，logmsg.log_status，msg.message_subject，msg.mess age_content; 

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

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 want to get all emails against their status by msg id and msg subject, content also.
i have write this query,

Select COUNT(logmsg.log_id) AS TotalUsers, logmsg.log_status, msg.message_subject, msg.message_content
FROM log_messages logmsg, messages msg
WHERE logmsg.message_id = 41 and msg.message_id = 41
GROUP BY logmsg.log_status,logmsg.log_status, msg.message_subject, msg.message_content;

but i want to get list of emails also.

解决方案

Use Joins to get your list.

You may refer about join here:

http://www.w3schools.com/sql/sql_join_inner.asp[^]

check this

Select COUNT(logmsg.log_id) AS TotalUsers, logmsg.log_status, msg.message_subject, msg.message_content
FROM log_messages logmsg 
INNER JOIN messages msg On logmsg.message_id = msg.message_id
GROUP BY logmsg.log_status,logmsg.log_status, msg.message_subject, msg.message_content;

这篇关于查询以使用Group by子句从多个表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！