根据SQL查询生成“散点图”成员集合的结果

问题描述：

我有一个staff数据库表，其中包含职员，user_no和user_name列。我还有另外一个，department，包含人员可以成员的部门的表，其中dept_no和dept_name为列。根据SQL查询生成“散点图”成员集合的结果

由于员工可以是多个部门的成员，因此我有第三个staff_dept表，其中user_no列和dept_no列是其他两个表的主键。此表显示每个员工所属的部门，并且每个用户/部门交叉点都包含一行。

我想以电子表格（CSV文件，无论什么;我会很好地将结果转换成可用的形式后，我有他们）的输出与每个部门一列，并输出每个用户一行，每个路口出现X，如staff_dept中所定义。

我可以写一个SQL查询来实现这个结果吗？或者我将不得不做一些“真正的”编程（因为显然，在嵌套三个或四个for循环之前，它不是一个“真正的”程序）来收集和格式化这些数据？

哪个rdbms？甲骨文？ SQL服务器？ MySQL的？ postgre？ DB2？访问？ – mellamokb 2012-04-26 13:50:46

测试查询：http://www.sqlfiddle.com/#!3/c136d – mellamokb 2012-04-26 13:53:58

答

这可以用一个数据透视表（使用SQL Server）来完成：

SELECT user_name, [dept1name], [dept2name], [dept3name], ... 
FROM 
    (SELECT s.user_name, d.dept_name, 
    case when sd.user_no is not null then 'X' else '' end as matches 
    from staff s 
    cross join department d 
    left join staff_dept sd on s.user_no = sd.user_no and d.dept_no = sd.dept_no 
    ) AS s 
PIVOT 
(
    min(matches) 
    FOR dept_name IN ([dept1name], [dept2name], [dept3name], ...) 
) AS pvt 
order by user_name

演示：http://www.sqlfiddle.com/#!3/c136d/5

编辑：从部门在表列表中动态生成的PIVOT查询，您可以使用动态SQL，即将代码生成到变量中并使用sp_executesql辅助程序存储过程。这里有一个例子：http://www.sqlfiddle.com/#!3/c136d/14

这非常酷。有没有办法做到这一点，以便您不必将所有部门名称输入为列？一种选择列作为列的方法？ – Dan 2012-04-26 14:03:41

@Dan：是的，使用动态生成的SQL代码。我会用一个例子来更新。 – mellamokb 2012-04-26 14:03:58

谢谢。这有点凌驾于我的头上，但如果遇到类似的情况，我会知道该查找什么。是动态部分递归和coalesce（）只是为了处理第一种情况，当@ sql仍然是NULL还是完全关闭？ – Dan 2012-04-26 14:28:04

答

在SQL Server（如果你使用SQL Server），我将开始一个全外连接（包括所有人员和部门，而不仅仅是那些参与的关系），降将其转换为一个将所有部门转换为列的pivot语句，然后构建一个简短脚本来生成并动态执行该SELECT语句（因为由pivot语句创建的列必须是硬编码的，所以它们不能在运行时动态生成时间）。

Here's a sample - 这是一个不透明的声明，但概念几乎相同。

根据SQL查询生成“散点图”成员集合的结果

相关推荐