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

如何根据技能和出勤情况查询总人数?

sqlite c#

0

Jesson · 技术社区 · 7 年前

在我的Sqlite数据库中,我有两个表:

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   |
+-----------+-----+-----+-----+-----+-----+-----+-----+

1 回复 | 直到 7 年前

1

Siavash Rostami Raju K 7 年前

我测试的结果会是你想要的。可能不是最优雅和最有表现力的方式,但我只是匆匆忙忙地做了这件事。

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

这是在SQLServerDB上完成的,所以您可能会看到一些语法描述。不太可能,但是如果你只是使用了sqlite对应的。

ps2。在sqlserver中,您可以使用PIVOT和window函数来实现这一点,但是您可以使用sqlite内置的功能来完成类似的工作,以避免脚本中的重复。