mysql查询,具有多个子查询,且分组条件不同

yshpjwxd  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(277)

嗨,我是一个php开发人员在mysql中的薄弱中复杂的查询使我的头炒。下表是 vulnerability .

+-----------------------------------------------------------------------------------------------+ 
| id | webisite_id | low_count| high_count | medium_count | date_time           |   vul_date    |
+-----------------------------------------------------------------------------------------------+
| 20 | 6           | 1        | 1          | 1            | 2018-07-04 09:14:04 | 2018-02-01    |
| 19 | 6           | 30       | 30         | 30           | 2018-07-04 09:13:38 | 2018-01-30    |   
| 18 | 6           | 1        | 1          | 1            | 2018-07-04 09:13:16 | 2018-01-01    |
+-----------------------------------------------------------------------------------------------+

此表表示数据库中每个网站的低、中、高漏洞计数。我们可以为每个网站输入尽可能多的条目。但网站唯一相关的条目是每个月的最新条目(基于 vul_date ).
在这里我需要帮助我要查询取总数低,高,中的每个网站的每个月的过去1年,例如如果->网站与 id 1在6月份有1个、2个、3个低、高、中等数量的漏洞,而->在6月份有 id 2分别为7、8、9,结果应为6月8日、10日、12日。而像上一年每个月的成绩都应该得到。如果没有输入,则应为0。
请注意,条目应为的最大值 vul_date 如果一个网站上有多个条目 vul_date 获取最新信息 date_time 进入。
我尽量把问题写得简单些。希望问题被理解。请帮帮我
提前谢谢。

hm2xizp9

hm2xizp91#

DROP TABLE IF EXISTS T;
CREATE TABLE T(id INT, website_id INT, low_count INT, high_count INT, medium_count INT, date_time DATETIME, vul_date DATE);
INSERT INTO T VALUES
( 20 , 6 , 1 , 1 , 1,  '2018-07-04 09:14:04' , '2018-02-01'), 
( 19 , 6 , 30, 30, 30, '2018-07-04 09:13:38' , '2018-01-30'), 
( 18 , 6 , 2 , 2 ,2 ,  '2018-07-04 09:13:16' , '2018-01-01'),
( 17 , 6 , 2 , 2 ,2 ,  '2018-07-04 09:12:01' , '2018-01-01'),
( 90 , 1,1,2,3,'2017-07-05 01:00:00',' 2017-07-06'),
( 90 , 2,8,9,10,'2017-07-05 01:00:00',' 2017-07-06');

select coalesce(c.yyyymm,d.yyyymm) yyyymm,
        coalesce(c.lo,0) lo,
        coalesce(c.hi,0) hi,
        coalesce(c.med,0) med 
from
(

SELECT concat(year(a.vul_date),'-',month(a.vul_date)) yyyymm,
        SUM(LOW_COUNT) lo,SUM(HIGH_COUNT) hi,sum(medium_count) med
from
(
select  website_id,date_time,vul_date
from t
where date_time = (select max(date_time) from t t1 where t1.website_id = t.website_id and t1.vul_date = t.vul_date)
) a
join
(select website_id, date_time,vul_date,
          LOW_COUNT,HIGH_COUNT,medium_count
from t) b
on b.website_id = a.website_id and b.date_time = a.date_time
group by concat(year(a.vul_date),'-',month(a.vul_date))
) c
right join 
(select distinct concat(year(dte),'-',month(dte)) yyyymm from dates d
where dte between date_sub(now(), interval 1 year) and now() 
) d on d.yyyymm = c.yyyymm
;

子查询a获取包含最新数据时间的vul\u日期,然后使用与日期/日历表的右联接将其自联接、聚合,然后填充缺少的日期。如果你没有一个日期/日历,这将是有益的这类运动坚果有替代品,你可以找到在这样做。
结果

+---------+------+------+------+
| yyyymm  | lo   | hi   | med  |
+---------+------+------+------+
| 2017-7  |    9 |   11 |   13 |
| 2017-8  |    0 |    0 |    0 |
| 2017-9  |    0 |    0 |    0 |
| 2017-10 |    0 |    0 |    0 |
| 2017-11 |    0 |    0 |    0 |
| 2017-12 |    0 |    0 |    0 |
| 2018-1  |   32 |   32 |   32 |
| 2018-2  |    1 |    1 |    1 |
| 2018-3  |    0 |    0 |    0 |
| 2018-4  |    0 |    0 |    0 |
| 2018-5  |    0 |    0 |    0 |
| 2018-6  |    0 |    0 |    0 |
| 2018-7  |    0 |    0 |    0 |
+---------+------+------+------+
13 rows in set (0.04 sec)
py49o6xq

py49o6xq2#

我认为下面的查询将为您工作。

SELECT 
    SUM(low_count),
    SUM(medium_count),
    SUM(high_count),
    MONTH(vul_date)
FROM
    (SELECT 
        low_count, medium_count, high_count, vul_date, date_time
    FROM
        test
    WHERE
        (website_id , vul_date) IN (SELECT website_id, MAX(vul_date)
            FROM test GROUP BY website_id , MONTH(vul_date))) t
WHERE
    date_time IN (SELECT MAX(date_time) FROM test GROUP BY website_id , vul_date)
GROUP BY MONTH(vul_date);

它做的是,首先找到每个网站id的最新进入月份,这是你的最大vul\ u日期。

SELECT website_id, MAX(vul_date)
            FROM test GROUP BY website_id , MONTH(vul_date)

如果vul\u日期有多个条目,它将使用date\u time从中选择最大值。最后,它将所有的网站日期按月份分组后求和。
您可以将上述查询更改为在没有任何网站条目的月份获得0值。

相关问题