MS访问SQL查询 - 可能很简单，但我生锈 [英] MS Access SQL Query - Probably simple, but I'm rusty

问题描述

我有一个表单，人们可以使用它发送电子邮件给我们的客户。用户具有在两个固定消息（消息1或消息2）之间进行选择的选项。在幕后，每次他们点击发送按钮，它会登录到一个记录表（只要它通过错误处理程序）。

I have a form that people can use to send emails to our clients. The user has an option to select between two canned messages (Message "1" or Message "2"). Behind the scenes, every time they hit the "SEND" button it logs into a "RECORDS" table (so long as it makes it through the error handlers).

假装RECORDS表有2列：

Let's pretend the RECORDS table has 2 columns:

CUST_ID, EMAIL_NUM
0000, 1
0000, 2
0000, 1
0000, 1
0001, 2
0002, 1
0002, 1
0003, 2
0003, 2
0003, 2

我需要一个查询来计算每个CUST_ID的1和2。因此结果集应该如下所示：

I need a query that counts the ones and twos for each CUST_ID. So the result set should look something like this:

CUST_ID, EMAIL_1_COUNT, EMAIL_2_COUNT
0000, 3, 1
0001, 0, 1
0002, 2, 0
0003, 0, 3

$ b b

我使用count，group bys，havings，while，union，嵌套的选择，但是像我说的，我可能过于复杂的东西比较容易。

I've used count, group bys, havings, while, union, nested selects, but like I said, I'm probably over complicating something that is relatively easy.

推荐答案

select
  CUST_ID,
  sum(iif(EMAIL_NUM = 1, 1, 0)) as EMAIL_1_COUNT,
  sum(iif(EMAIL_NUM = 2, 1, 0)) as EMAIL_2_COUNT
from
  RECORDS
group by
  CUST_ID

这篇关于MS访问SQL查询 - 可能很简单，但我生锈的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！