时间戳

首页 > 解决方案 > 如何根据技能和出勤率查询基地总人数？

c# - 如何根据技能和出勤率查询基地总人数？

问题描述

如何根据他们的技能参考出勤率来汇总可用用户群？

在我的 Sqlite 数据库中，我有 2 个表：

TABLE: Skill Available
+----------+--------+---------+---------+---------+
| Username | Skill_1| Skill_2 | Skill_3 | Skill_4 |
+----------+--------+---------+---------+---------+
| Mark     | 1      | 1       | 1       | 1       |
+----------+--------+---------+---------+---------+
| Jordan   | 1      | 0       | 1       | 0       | 
+----------+--------+---------+---------+---------+
| John     | 1      | 1       | 0       | 0       | 
+----------+--------+---------+---------+---------+
| Edward   | 1      | 1       | 0       | 0       | 
+----------+--------+---------+---------+---------+
Note: Zero represents users that doesn't have that skill. (1/0 = true/false)

TABLE: Attendance 
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
| Username | Site  | Shift | SUN | MON | TUE | WED | THU | FRI | SAT |
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
| Mark     | Bldg1 | Night | 1   | 1   | 1   | 1   | 1   | 0   | 0   |
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
| Jordan   | Bldg1 | Night | 1   | 1   | 0   | 0   | 1   | 1   | 1   |
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
| John     | Bldg2 | Day   | 1   | 1   | 1   | 0   | 0   | 1   | 1   |
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
| Edward   | Bldg1 | Night | 1   | 0   | 0   | 1   | 1   | 1   | 1   |
+----------+-------+-------+-----+-----+-----+-----+-----+-----+-----+
Note: Zero represents restday. (1/0 = true/false)

通过使用上面的两个表，我如何在查询中实现这个结果？

+-----------+-----+-----+-----+-----+-----+-----+-----+
| SkillList | SUN | MON | TUE | WED | THU | FRI | SAT |
+-----------+-----+-----+-----+-----+-----+-----+-----+
| Skill_1   | 4   | 3   | 2   | 2   | 3   | 3   | 3   |
+-----------+-----+-----+-----+-----+-----+-----+-----+
| Skill_2   | 3   | 3   | 1   | 1   | 2   | 2   | 2   |
+-----------+-----+-----+-----+-----+-----+-----+-----+
| Skill_3   | 2   | 2   | 1   | 0   | 2   | 1   | 1   |
+-----------+-----+-----+-----+-----+-----+-----+-----+
| Skill_4   | 1   | 1   | 0   | 0   | 1   | 0   | 0   |
+-----------+-----+-----+-----+-----+-----+-----+-----+

标签： c#sqlite

解决方案

我测试的这将产生您正在寻找的结果。可能不是最优雅和最高效的方式，但我只是匆匆忙忙地这样做了。
希望无论如何都会有所帮助:) 

select 
    'Skill_1',
    (Select COUNT(*) from Attendance att where att.SUN = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) SUN,
    (Select COUNT(*) from Attendance att where att.MON = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) MON,
    (Select COUNT(*) from Attendance att where att.TUE = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) TUE,
    (Select COUNT(*) from Attendance att where att.WED = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) WED,
    (Select COUNT(*) from Attendance att where att.THU = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) THU,
    (Select COUNT(*) from Attendance att where att.FRI = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) FRI,
    (Select COUNT(*) from Attendance att where att.SAT = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_1 = 1) > 0) SAT
union 

select
    'Skill_2',
    (Select COUNT(*) from Attendance att where att.SUN = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) SUN,
    (Select COUNT(*) from Attendance att where att.MON = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) MON,
    (Select COUNT(*) from Attendance att where att.TUE = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) TUE,
    (Select COUNT(*) from Attendance att where att.WED = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) WED,
    (Select COUNT(*) from Attendance att where att.THU = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) THU,
    (Select COUNT(*) from Attendance att where att.FRI = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) FRI,
    (Select COUNT(*) from Attendance att where att.SAT = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_2 = 1) > 0) SAT

union 

select
    'Skill_3',
    (Select COUNT(*) from Attendance att where att.SUN = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) SUN,
    (Select COUNT(*) from Attendance att where att.MON = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) MON,
    (Select COUNT(*) from Attendance att where att.TUE = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) TUE,
    (Select COUNT(*) from Attendance att where att.WED = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) WED,
    (Select COUNT(*) from Attendance att where att.THU = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) THU,
    (Select COUNT(*) from Attendance att where att.FRI = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) FRI,
    (Select COUNT(*) from Attendance att where att.SAT = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_3 = 1) > 0) SAT

union 

select
    'Skill_4',
    (Select COUNT(*) from Attendance att where att.SUN = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) SUN,
    (Select COUNT(*) from Attendance att where att.MON = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) MON,
    (Select COUNT(*) from Attendance att where att.TUE = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) TUE,
    (Select COUNT(*) from Attendance att where att.WED = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) WED,
    (Select COUNT(*) from Attendance att where att.THU = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) THU,
    (Select COUNT(*) from Attendance att where att.FRI = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) FRI,
    (Select COUNT(*) from Attendance att where att.SAT = 1 and 
        (select COUNT(*) from SkillsAvailable where Username = att.Username and Skill_4 = 1) > 0) SAT

附言。这是在 sql server db 上完成的，因此您可能会看到一些语法差异。极不可能，但如果您确实使用了 sqlite 对应项。

ps2。在 sql server 中，您可以使用 PIVOT 和窗口函数来实现此目的，但您可以使用 sqlite 内置功能来执行类似的操作，以避免脚本中的重复。

推荐阅读