代码之家 › 专栏 › 技术社区 › enkiki

基于日期(字段总数)检索数据的MySQL查询

mysql

enkiki · 技术社区 · 7 年前

我是MySQL新手,所以请耐心点:)

我有一个表,我想根据日期检索数据,并进行汇总

这是我的桌子

client    datescanned  problem   title
 abc      2019-02-02    12345a         High      xyz
 abc      2019-02-02    12345b         High      xyz 
 abc      2019-02-02    12345c         High      xyz
 abc      2019-02-02    12345d         Medium    xyz
 abc      2019-02-09    12345e         High      xyz 
 abc      2019-02-09    12345f         High      xyz
 abc      2019-02-09    12345g         Low       xyz
 abc      2019-02-09    12345h         Low       xyz
 abc      2019-02-09    12345j         Low       xyz
 abc      2019-02-16    12345x         High      xyz
 abc      2019-02-16    12345s         High      xyz
 abc      2019-02-16    12345w         High      xyz
 abc      2019-02-16    12345bs        Medium    xyz

我想要的结果是

 client   datescanned  problem         High   Medium   Low 
 abc      2019-02-02    12345x          3       1       0
 abc      2019-02-09    12345s          2       0       3
 abc      2019-02-16    12345w          3       1       0

这是我的密码

select client,datescanned, problem, severity, 

         count(case when severity = 'High' then 1 end) as High,
         count(case when severity = 'Medium' then 1 end) as Medium,
         count(case when severity = 'Low' then 1 end) as Low

 from ssstest where client = "myuser"
 group by client,datescanned, problem

这将获得所有的数字正确,但我想“总结”高,中,低每个日期。

现在我明白了。。

client    datescanned  problem   severity      High  Medium  Low
 abc      2019-02-02    12345a      High        1      0      0
 abc      2019-02-02    12345b      High        1      0      0
 abc      2019-02-02    12345c      High        1      0      0 
 abc      2019-02-02    12345d      Medium      0      1      0 
 abc      2019-02-09    12345e      High        1      0      0 
 abc      2019-02-09    12345f      High        1      0      0 
 abc      2019-02-09    12345g      Low         0      0      1
 abc      2019-02-09    12345h      Low         0      0      1
 abc      2019-02-09    12345j      Low         0      0      1
 abc      2019-02-16    12345x      High        1      0      0
 abc      2019-02-16    12345s      High        1      0      0
 abc      2019-02-16    12345w      High        1      0      0
 abc      2019-02-16    12345bs     Medium      0      1      0

所以基本上,我只想把同一天的高、中、低数据加起来。

客户端数据扫描问题高-中-低
abc 2019-02-02 12345x 3 1 0
abc 2019-02-09 12345s 2 0 3
abc 2019-02-16 12345w 3 1 0

再次感谢你的帮助!

1 回复 | 直到 7 年前

Fahmi 7 年前

你可以尝试使用 conditional aggregation

select client,dateadded, computername, severity, 
       count(case when severity='High' then 1 end) as High,
       count(case when severity='Medium' then 1 end) as Medium,
       count(case when severity='Low' then 1 end) as Low,
from mytable where client =%CURRENT_USER_LOGIN%
group by client,dateadded, computername

Darek Adamkiewicz 7 年前

更新(1):

在MySQL/MariaDB查询中,以下内容没有多大意义:

=%CURRENT_USER_LOGIN%

如果要搜索的子字符串为 CURRENT_USER_LOGIN 然后使用 like '%CURRENT_USER_LOGIN%'

select client,dateadded, computername, severity, 
       count(case when severity='High' then 1 end) as High,
       count(case when severity='Medium' then 1 end) as Medium,
-- comma mark ',' before 'from' !!! produces error
       count(case when severity='Low' then 1 end) as Low,
from mytable where client =%CURRENT_USER_LOGIN%
group by client,dateadded, computername