首页 > 试题广场 >

有下列数据库中表tb_event,表中字段定义如下:

[单选题]

有下列数据库中表tb_event,表中字段定义如下:

CREATE TABLE tb_event

(

id bigserial NOT NULL,

smtp_rcpt character varying(2048),

count_mlink integer,

time_date timestamp without time zone NOT NULL

)

数据库中的数据如下所示:

id

smtp_rcpt

count_mlink

time_date

1

test_user2@trendmicro.com.cn

9

2017-07-09 21:15:08

2

test_user1@trendmicro.com.cn

3

2017-07-11 21:15:10

3

test_user1@trendmicro.com.cn

4

2017-07-11 21:15:20

4

test_user3@trendmicro.com.cn

2

2017-07-15 21:15:25

5

test_user4@trendmicro.com.cn

0

2017-07-15 21:15:25

6

test_user2@trendmicro.com.cn

1

2017-07-16 21:15:30

7

test_user2@trendmicro.com.cn

3

2017-07-17 07:33:31

8

test_user4@trendmicro.com.cn

3

2017-07-17 08:13:23

9

test_user1@trendmicro.com.cn

6

2017-07-17 08:13:33

10

test_user2@trendmicro.com.cn

0

2017-07-17 08:13:44

问题:执行下列SQL之后的结果是以下哪个?

 

SELECT smtp_rcpt, SUM(count_mlink) FROM tb_event

WHERE time_date > '2017-7-15 00:00:00'

GROUP BY smtp_rcpt

HAVING SUM(count_mlink) > (select avg(count_mlink) from tb_event)

  • smtp_rcpt                | sum<br>-------------------------------------------+-----<br>test_user2@trendmicro.com.cn |   4<br>test_user1@trendmicro.com.cn |   6
  • smtp_rcpt         | sum<br>--------------------------------------------+-----<br>test_user1@trendmicro.com.cn |  13<br>test_user2@trendmicro.com.cn |  13
  • smtp_rcpt        | sum<br>-------------------------------------------+-----<br>test_user1@trendmicro.com.cn |   6
  • smtp_rcpt        | sum<br>-------------------------------------------+-----<br>test_user2@trendmicro.com.cn |   13<br>test_user1@trendmicro.com.cn |   13<br>test_user3@trendmicro.com.cn |   2<br>test_user4@trendmicro.com.cn |   3
SELECT
AVG(count_mlink)
FROM tb_event,子查询输出结果为count_mlink的均值,3.1。
SELECT,FROM主语句是从tb_event抽取smtp_rcpt和SUM(count_mlink),WHERE约束条件为time_date > '2017-7-15 00:00:00',以smtp_rcpt分组后添加HAVING约束条件SUM(count_mlink)>3.1。
---------------------------------------------------------------------------------------------------------------------------
即先筛选出其中tb_event表中大于2017-7-15 00:00:00的数据,再smtp_rcpt分组确定四个网站筛选出的数据的count_mlink总数最后选出其中count_mlink总数大于3.1的数据的smtp_rcpt和count_mlink总数。结果为A
编辑于 2020-07-06 10:26:46 回复(0)