20.7　利用SQL语句编制每天刷卡汇总数据透视表

图20-48所示展示了某实验室在2012年3月份每天进出实验室刷卡记录数据列表，该数据列表保存在D盘根目录下的“2012年3月实验室出入刷卡记录.xlsx”文件中。

img458a

图20-48　刷卡记录数据列表

示例20.7　编制每天刷卡汇总数据透视表

如果希望对图20-48所示的数据列表，查询每天实验室人员的刷卡情况，请参照以下步骤。

步　骤1img01

新建一个Excel工作簿，将其命名为“编制每天刷卡汇总数据透视表.xlsx”，打开该工作簿，将Sheet1工作表改名为“出入汇总”，然后删除其余的工作表。

步　骤2img01

打开D盘根目录下的目标文件“2012年3月实验室出入刷卡记录.xlsx”，弹出【选择表格】对话框，如图20-49所示。

img458b

图20-49　选择表格

步　骤3img01

保持【选择表格】对话框的默认选择，单击【确定】按钮，在弹出的【导入数据】对话框中选择【数据透视表】单选钮，【数据的放置位置】选择【现有工作表】单选钮，单击“出入汇总”工作表中的A1单元格，再单击【属性】按钮打开【连接属性】对话框，单击【定义】选项卡，如图20-50所示。

img459a

图20-50　打开【连接属性】

步　骤4img01

清空【命名文本】文本框中的内容，输入以下SQL语句：

SELECT A．工号，A．姓名，A．日期，A．刷卡时间，COUNT（B．刷卡时间）AS打卡次序FROM[刷卡记录$]A INNER JOIN[刷卡记录$]B

ON A．工号=B．工号AND A．日期=B．日期AND A．刷卡时间>=B．刷卡时间

GROUP BY A．工号，A．姓名，A．日期，A．刷卡时间

单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白的数据透视表，如图20-51所示。

img459b

图20-51　创建空白的数据透视表

思路解析：以工号、日期和刷卡时间作为关联条件，通过对同一天、同一工号下的不同刷卡时间进行比较，利用聚合函数来统计符合条件的刷卡记录对比次数，从而获得同一天、同一工号不同刷卡记录对应的打卡次序，实现每天刷卡汇总查询。

步　骤5img01

在【数据透视表字段列表】中，将工号、姓名和日期字段移动至【行标签】区域内，将“打卡次序”字段移动至【列标签】区域内，将“刷卡时间”字段移动至【∑数值】区域内，并更改“刷卡时间”字段的值汇总方式为“求和”，设置“数字格式”为时间格式，最后对数据透视表进一步美化，最终完成的数据透视表如图20-52所示。

img460a

图20-52　最终完成的数据透视表

本例利用SQL连接语句结合聚合函数统计符合条件的数据记录，日常工作中有着非常广泛的应用，例如生成排名等，但使用JOIN连接，需要注意关联条件的设置，条件设置不当，容易产生笛卡尔积，导致数据虚增。