如何在表上选择和计数发生一些值 [英] How to select on table and count occurrences some values

查看：154
本文介绍了如何在表上选择和计数发生一些值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我要求您提供帮助，因为我不太了解SQL。



我需要计算从表列中出现的一些值，以实现统计表如下图所示：



需要的结果：





评论：



我的结果表需要有前两列（contry和site）来自第一个表Violations和接下来的5列，它们将包含id的每个可能值中Violations中出现的number_id（count）从状态表。



说明：



所以，我有两个表：违例和状态。请查看我的 sqlfiddle



违反：




  • id long，

  • country varchar

  • site varchar（20），

  • status_id long，< - 这是状态表中的状态ID。

  • ...其他列在此情况下不重要



状态：




  • id long，

  • status long
    列status具有映射到字符串值：可疑违规（1），已确认违规（2），已确认违规（3），未确定（4）



（或仅基于一个表Violations）是具有应包含列的表：




  • 从违规表中：Country和已确认的违规，未确定，总计（其中此列为计数器）的网站和网站

  • 在Violation表中出现）。



当前状态和新要求：



到bluefeet），几乎完美... 

 选择v.country，
 v.site，
 SUM（case s.id = 1 then 1 else 0 end）Total_SuspectedViolations，
 SUM（当s.id = 2然后1 else 0 end时的情况）Total_ConfirmedViolations，
 SUM（当s。 id = 3 then 1 else 0 end）Total_ConfirmedNoViolations，
 SUM（当s.id = 4然后1 else 0 end时的情况）Total_NotDetermined，
 COUNT（*）总计
违反v 
 inner join status s 
 on v.status_id = s.id 
 group by v.country，v.site

或不带JOIN：

 选择v.country，
 v.site ，
 SUM（当v.status_id = 1然后1 else 0 end时的情况）Total_SuspectedViolations，
 SUM（当v.status_id = 2然后1 else 0 end时的情况）Total_ConfirmedViolations，
 SUM当v.status_id = 3 then 1 else 0 end）Total_ConfirmedNoViolations，
 SUM（当v.status_id = 4然后1 else 0 end时的情况）Total_NotDetermined，
 COUNT（*）总计
 v 
 group by v.country，v.site

...但不包括3个问题，你可以看到在图片应该是。我是指：




  • -​​ 全部 - ，其中应计算所有国家/地区的出现次数


  • -​​ 全部 - （针对每个国家/地区） - 应计算一个国家/地区内的发生次数



其他说明：




  • -Unknown- 含义：



未知应计算国家/地区的出现次数，例如不存在于DB国家/错误的名称/ id，这就是为什么在这里被视为未知（我忘了提到在数据库中有表国家 。
同样的网站，未知的网站意味着有人在Violations.status_id不是从范围（1-4）中输入错误的值，因为这些只是可接受的值现有在状态表中。




  • 我们可以假设表国家/地区如下：


$ b b

国家：




  • id long，

  • name varchar（30） >


请帮我写正确的sql查询，其中包括这3个条件，因为我有一个大问题。

解决方案

全部 案例可以使用 / code>语句（有关结果，请参见 sqlFiddle ）：

 （SELECT v.country，
 v.site，
 SUM（CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations，
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）Total，
 0'isAll'
 FROM violation v 
 GROUP BY v.country，v.site）
 union（
 SELECT v.country，
' -  All  - '，
 SUM（CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations，
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）Total，
 1'isAll'
 FROM violation v 
 GROUP BY v。国家）
 UNION（
 SELECT' -  All  - '，
' -  All  - '，
 SUM（CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations， 
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）Total，
 1'isAll'
 FROM violation v）
 ORDER按国家，isAll DESC， site

但是，这种查询的性能可能不是很好，所以我不是

$ b
$ b

http://www.sqlfiddle.com/# ！2 / abfb7 / 21 

 （SELECT IF（c.name IS NULL，' -  Unknow  - c.name）作为名称，
 v.site，
 SUM（CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations，
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）总计，
 0'isAll'
来自违规v LEFT JOIN country c ON c.name = v.country 
 GROUP BY c.name，v.site） 
 union（
 SELECT IF（c.name IS NULL，' -  Unknow  - '，c.name）as name，
' -  All  - '，
 SUM v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations，
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）Total，
 1'isAll'
 FROM violation v LEFT JOIN country c ON c.name = v.country 
 GROUP BY c.name）
 UNION（
 SELECT' -  All  - '，
' -  All  - '， 
 SUM（CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END）Total_SuspectedViolations，
 SUM（CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END）Total_ConfirmedViolations，
 SUM（CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END）Total_ConfirmedNoViolations，
 SUM（CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END）Total_NotDetermined，
 COUNT（*）Total，
 1' isAll'
 FROM violations v LEFT JOIN country c ON c.name = v.country）
 ORDER BY name，isAll DESC，site


I am asking you for a help because I do not know SQL very well.

I need to count occurrences some values from table column to achieve effect like statistics table which will look like at the picture below:

Needed result:

Comment:

My Result Table needs to have first two columns (contry and site) comes from first table "Violations" and next 5 columns which will contain numbers (count) of occurrences status_id in "Violations" in each of possible values of id from Status table.

Explanation:

So, I have existing two tables: Violations and Status. Please look at my sqlfiddle

Violations:

  • id long,
  • country varchar(20),
  • site varchar(20),
  • status_id long, <-- this is the id of status in Status table.
  • ... other columns not important in this case

Status:

  • id long,
  • status long Column "status" have values (1-4) which are mapped to string values: Suspected Violation (1), Confirmed Violation (2), Confirmed No Violation (3), Not Determined (4)

In result of my join (or based only on one table Violations) is to have table which should contain columns:

  • from Violations table: "Country" and "Site"
  • from Status table: "Suspected Violation", "Confirmed Violation", "Confirmed No Violation", "Not Determined", "Total" (where this columns are counters of occurrences in Violation table).

Current Status and new Requirements:

First try is done (thanks to bluefeet) below and is almost perfect... 

select v.country,
v.site,
    SUM(case when s.id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when s.id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when s.id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when s.id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
inner join status s
    on v.status_id = s.id
group by v.country, v.site

or without JOIN:

select v.country,
v.site,
    SUM(case when v.status_id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when v.status_id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when v.status_id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when v.status_id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
group by v.country, v.site

...but is not including 3 issues as you can see in the picture which should be. I mean:

  • "- All -" which should count occurrences for all countries
  • "- Unknown -" which should count occurrences for some not recognized countries
  • "- All -" (regarding to each country) - which should count occurrences within one country

Additional Explanation:

  • -Unknown- meaning:

Unknown should count occurrences for countries which for example do not exists in DB Country table or have a wrong name/id and that's why is treated here as Unknown (I forgot mention that there is table Country in DB). The same for sites, Unknown for sites means that someone put wrong value in Violations.status_id not from range (1-4) because these are only acceptable values existing in Status table.

  • We can assume that table Country looks like:

Country:

  • id long,
  • name varchar(30)

Please help me to write correct sql query which would include these 3 conditions, because I have a big problem to do that.

解决方案

The All case can be easily done using UNION statement (see sqlFiddle for results):

(SELECT v.country,
    v.site,
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
    0  'isAll'
FROM violations v
GROUP BY v.country, v.site)
union(
SELECT v.country,
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v
GROUP BY v.country)
UNION (
SELECT '- All -',
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v)
ORDER BY country, isAll DESC, site

However, the performance may not be really great with that kind of query, so I'm not saying it's the best possible solution - but it works.

Version with 'Unknow'

http://www.sqlfiddle.com/#!2/abfb7/21

(SELECT IF(c.name IS NULL, '- Unknow -', c.name) as name,
    v.site,
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
    0  'isAll'
FROM violations v LEFT JOIN country c ON c.name = v.country
GROUP BY c.name, v.site)
union(
SELECT IF(c.name IS NULL, '- Unknow -', c.name) as name,
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v LEFT JOIN country c ON c.name = v.country
GROUP BY c.name)
UNION (
SELECT '- All -',
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v LEFT JOIN country c ON c.name = v.country)
ORDER BY name, isAll DESC, site

这篇关于如何在表上选择和计数发生一些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆