SQL：我有一个SQL查询（MSSQLSERVER），我使用子查询列添加到结果集选择的子查询

Question

：

SELECT P.name, 
(select count(*) from cars C where C.type = 'sports') AS sportscars, 
(select count(*) from cars C where C.type = 'family') AS familycars, 
(select count(*) from cars C where C.type = 'business') AS businesscars 
FROM people P 
WHERE P.id = 1; 

查询以上仅仅是从测试设置，这是一个有点废话，但它以我想象中的例子为例。我实际上正在处理的查询跨越了许多复杂的表格，这些表格只会分散眼前的问题。

在上面的例子中，表格“people”中的每条记录还有三列：“wantsSportscar”，“wantsFamilycar”和“wantsBusinesscar”。现在我想要做的只是如果people表中的各个“want .....”字段设置为“true”，则只需执行每个附加列的子选择。换句话说，如果P.wantsSportscar对特定人员设置为true，我只想执行第一个子查询。第二和第三个子查询应该以类似的方式工作。

所以这个查询应该工作的方式是它显示了一个特定的人的名字以及他想要拥有的汽车类型可用的模型数量。值得注意的是，我的最终结果集总是只包含一个记录，即一个特定用户的记录。

重要的是，如果某人对某种类型的汽车不感兴趣，则该类型的列将不会包含在最终结果集中。一个例子，以确保这是明确的：

如果人A想要一辆跑车和一辆家庭轿车，结果将包括列“名称”，“跑车”和“家庭轿车”。

如果B人想要一辆商务车，结果将包括“名称”和“businesscar”列。

我一直在尝试使用IF，CASE和EXISTS语句的各种组合，但到目前为止我还没有能够得到一个语法正确的解决方案。有谁知道这是否可能？请注意，该查询将存储在一个存储过程中。

Answer 1

在你的情况下，有8列布局可能和做到这一点，你将需要8单独的查询（或动态构建您的查询）。

无法在单个查询中更改结果集布局。

相反，你可以按照如下设计你的查询：

SELECT P.name, 
     CASE WHEN wantssport = 1 THEN (select count(*) from cars C where C.type = 'sports') ELSE NULL END AS sportscars, 
     CASE WHEN wantsfamily = 1 THEN (select count(*) from cars C where C.type = 'family') ELSE NULL END AS familycars, 
     CASE WHEN wantsbusiness = 1 THEN (select count(*) from cars C where C.type = 'business') ELSE NULL END AS businesscars 
FROM people P 
WHERE P.id = 1 

将在适当的列中选择NULL如果一个人不想要它，并在客户端解析这些NULL的。

请注意，关系模型根据relations回答查询。

在你的情况下，关系如下：“这个人的需求对于这么多的跑车，这么多的商务车以及这么多的家用轿车都很满意。

关系模型总是以四元关系回答这个具体问题。

它不会忽略任何关系成员：相反，它只是将它们设置为NULL这就是SQL的方式来表明关系的成员没有定义，适用或有意义。

Answer 2

我大部分都是Oracle的人，但同样适用的可能性很大。除非我误解了，否则你想要的东西在这个层次上是不可能的 - 你总会有一个固定的列数。您的查询可以控制列是否为空，但由于在查询的最外面部分中指定了X列，因此您可以保证在结果集中获得X列。

正如我所说，我不熟悉MS SQL Server，但我猜会有一些执行动态SQL的方法，在这种情况下，您应该研究一下，因为它应该允许您构建更灵活的查询。

Answer 3

您可以通过首先将值选为单独的行选择到临时表中，然后在该表上执行PIVOT（将行转换为列）来完成您想要的操作。

Answer 4

重要的是，如果一个人没有 感兴趣的某一类车， ，对于这种类型的列将不 被包含在最终的结果集。一个 的例子，以确保这是明确的：

你将无法在普通的SQL中做到这一点。我建议你只是将这个列设置为NULL或ZERO。

如果您希望查询在添加新车时动态展开，那么PIVOTing可以帮助您。

Answer 5

为了使这项工作变得简单，您想学习三个基本原则。第一个是数据规范化，第二个是GROUP BY，第三个是PIVOT。

首先，数据标准化。人们的表格设计不是第一种正常形式。 “wantports”，“wantfamily”，“wantbusiness”这些专栏实际上是一个重复的小组，虽然他们看起来不像一个。如果您可以修改表格设计，您会发现创建第三个表格的好处是，可以称其为“peoplewant”，并带有两个关键列，personid和cartype。如果你愿意，我可以详细说明为什么这个设计更灵活和更强大，但我现在要跳过这个。

在GROUP BY上。这使您可以生成一个结果，该结果汇总了结果的一行中的每个组。

SELECT 
    p.name, 
    c.type, 
    c.count(*) as carcount 
FROM people p, 
    INNER JOIN peoplewant pw ON p.id = pw.personid 
    INNER JOIN cars c on pw.cartype = c.type 
WHERE 
    p.id = 1 
GROUP BY 
    p.name, 
    c.type 

这（未经测试）查询给你你想要的结果，但结果对人要各车种单独一行。

最后，PIVOT。您的DBMS中的PIVOT工具允许您将此结果转换为只有一行的表单，并且该人员想要的每个cartype都有一个单独的列。我自己并没有使用PIVOT，所以我会让其他人编辑这个响应来提供一个使用PIVOT的例子。

如果您使用相同的技术在一次扫描中检索多个人的数据，请记住，对于每个想要的类型都会显示一列，任何人都想要的列将出现在PIVOT结果中，想要一个在结果列中的汽车类型。

Answer 6

刚刚通过谷歌搜索发现这篇文章，所以我意识到我迟到了这个派对了一点，但是..确定这真的是可能做...但是，我不会建议实际上是这样做的，因为它通常被认为是一件非常糟糕的事情（tm）。

动态SQL是你的答案。

在我说如何做到这一点之前，我想说一句，动态SQL是一个非常危险的东西，如果你没有从应用程序中消毒你的输入。

所以，因此，谨慎行事：

declare @sqlToExecute nvarchar(max); 
declare @includeSportsCars bit; 
declare @includeFamilyCars bit; 
declare @includeBusinessCars bit; 

set @includeBusinessCars = 1 
set @includeFamilyCars = 1 
set @includeSportsCars = 1 

set @sqlToExecute = 'SELECT P.name ' 

if @includeSportsCars = 1 
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''sports'') AS sportscars, '; 
if @includeFamilyCars = 1 
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''family'') AS familycars, '; 
if @includeBusinessCars = 1 
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''business'') AS businesscars ' 

set @sqlToExecute = @sqlToExecute + ' FROM people P WHERE P.id = 1;'; 

exec(@sqlToExecute)