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

解释行和列相关性

database-design database

nearly_lunchtime · 技术社区 · 17 年前

这是一个简单而常见的工作场景,我希望能得到一些输入。

假设我正在为宠物秀的所有者生成一个报告,他们想知道他们的哪些客户购买了每只宠物的数量。在这个场景中,我唯一的工具是SQL和将查询输出到电子表格的工具。

作为店主,我希望报告的形式是:

Customer  Dog  Cat  Rabbit
1         2    3    0
2         0    1    1
3         1    2    0
4         0    0    1

如果有一天我决定去养金鱼,那么这份报告现在应该是这样的。

Customer  Dog  Cat  Rabbit Goldfish
1         2    3    0      0
2         0    1    1      0
3         1    2    0      0
4         0    0    1      0
5         0    0    0      1

但是,正如您可能知道的,以这种方式工作的查询将涉及某种形式的动态代码生成,而且很难做到。

最简单的查询将沿着以下几行工作: 交叉加入客户和宠物,外部加入销售,集团等。生成:

Customer Pet      Quantity
1        Dog      2
1        Cat      3
1        Rabbit   0
1        Goldfish 0
2        Dog      0
2        Cat      1
2        Rabbit   1
...etc

a)我如何向店主解释他们想要的报告“难以”生成?我不是想说它读起来难,但写起来难。

b)我试图向客户解释的概念的名称是什么(帮助我谷歌搜索)?

4 回复 | 直到 17 年前

n8wrl 17 年前

概念的名称是“交叉表”,可以通过多种方式实现。

MS Access对SQL进行了专有扩展,以实现这一点。SQLPre-2K5有一个case技巧,2K5和更高版本有pivot,但我认为您仍然需要知道列是什么。

Vinko Vrsalovic 17 年前

有些数据库确实支持某种创建交叉表的方法,但我认为大多数数据库都需要知道列提前,因此您必须修改SQL(并获取支持此类扩展的数据库)。

另一种选择是创建一个程序,该程序将对第二个“Easy”表进行后处理,以使客户机将交叉表作为输出。这可能比修改或动态生成SQL更容易,更通用。

关于解释这个问题的方法…您可以在Excel中显示需要多少步骤才能获得所需的结果:

源数据(您的第二个列表)。
从“宠物”列中选择值
将找到的每种宠物类型放在新列上
每种类型每客户端的计数值
填充值

然后说SQL只提供源数据,所以它当然要做更多的工作。

Quassnoi 17 年前

这个概念叫做 pivoting

SQL 假设您的数据是以关系固定结构。

比如,平等是一种二元关系,“顾客有这么多这种类型的宠物”是一种三元关系等等。

当您看到此结果集时:

Customer Pet      Quantity
1        Dog      2
1        Cat      3
1        Rabbit   0
1        Goldfish 0
2        Dog      0
2        Cat      1
2        Rabbit   1

它实际上是一个由所有可能的领域价值观在这一关系中。

就像,一个顾客 1 (域 customers id's 准确地说 2 (域 positive numbers 属的宠物 dog (域 pets )

在结果集中看不到这样的行:

Customer Pet      Quantity
1        Dog      3
Pete     Wife     0.67

,因为第一行是假的(客户 一 没有 3 项目 狗 但是 二 ,第二行值超出了它们的域范围。

SQL 范例意味着您的关系是在发出查询时定义的,并且返回的每一行都完全定义了关系。

SQL Server 2005+ 可以将行映射到列中(这是您想要的),但在设计查询(不运行)时应该知道列的数目。

通常,您试图构建的报表是使用知道如何转换关系的报表软件构建的。 SQL 结果集成为好看的人可读的报告。

AngerClown 17 年前

我一直称之为旋转,但这可能不是正式名称。

不管它叫什么,您几乎可以用普通的SQL完成所有这些工作。

SELECT customer, count(*), sum(CASE WHEN pet='dog' THEN 1 ELSE 0 END) as dog, sum(case WHEN pet='cat' THEN 1 ELSE 0 END) as cast FROM customers join pets

显然,缺少的是动态列。我不知道在直接SQL中是否可以这样做,但是在存储过程中,在第一次查询宠物列表之后,当然可以动态生成查询。查询内置到字符串中,然后该字符串用于创建准备好的语句。