的SQL Server ...子查询返回多个

问题描述：

id  record_name  record_value 
------------------------------------- 
1001 price1   12 
1001 price2   1 
1001 price3   8 
1201 price1   18 
1201 price2   2 
1201 price3   6 
1601 price1   12 
1601 price2   8 
1601 price3   8

输出的SQL Server ...子查询返回多个

id  price1  value  price2  value  price3  value 
-------------------------------------------------------------------------- 
1001 price1  12   price2  1   price3  8 
1201 price1  18   price2  2   price3  6

我收到错误

子查询返回多个值。当子查询遵循=，！=，<，< =，>，> =或当子查询用作表达式时，这是不允许的。

我用这个查询：

select distinct 
    a.id, 'Price1', 
    (select record_value 
    from table_name 
    where id = a.id and record_name = 'price1') as 'value1', 
    'Price2', 
    (select record_value 
    from table_name 
    where id = a.id and record_name = 'price2') as 'value2', 
    'Price3', 
    (select record_value 
    from table_name 
    where id = a.id and record_name = 'price3') as 'value3' 
from 
    table_name a

请尽量解决这个问题！

那么，哪个DBMS？ Postgres的？ SQL Server？ SQLite的？ – 2014-11-25 07:30:01

好问题。哪一个 – Jaques 2014-11-25 07:31:30

随着你在这里发布的数据..有没有像这样的错误..它的工作很好.. – Deepshikha 2014-11-25 07:36:56

答

这种方法有点不同。但是，你可以这样做：

SELECT 
    table_name.id, 
    'price1' AS price1, 
    SUM(CASE WHEN record_name='price1' THEN record_value ELSE 0 END) AS value1, 
    'price2' AS price2, 
    SUM(CASE WHEN record_name='price2' THEN record_value ELSE 0 END) AS value2, 
    'price3' AS price2, 
    SUM(CASE WHEN record_name='price3' THEN record_value ELSE 0 END) AS value3 
FROM 
    table_name 
GROUP BY 
    table_name.id

更新

要回复记者的置评。是的，它会工作。如果我们看一下这样一个简单的测试：

DECLARE @tbl TABLE(ID INT, test VARCHAR(100)) 

INSERT INTO @tbl 
VALUES 
(1,'foo'), 
(1,'foo'), 
(1,'bar'), 
(1,'bar')

此查询将有一个静态值作为price1。

SELECT 
    tbl.ID, 
    'price1' as price1 
FROM 
    @tbl AS tbl 
GROUP BY 
    tbl.ID

更新2

然后，如果你不想SUM值。然后，您可以改用MAX。像这样：

SELECT 
    table_name.id, 
    'price1' AS price1, 
    MAX(CASE WHEN record_name='price1' THEN record_value ELSE 0 END) AS value1, 
    'price2' AS price2, 
    MAX(CASE WHEN record_name='price2' THEN record_value ELSE 0 END) AS value2, 
    'price3' AS price2, 
    MAX(CASE WHEN record_name='price3' THEN record_value ELSE 0 END) AS value3 
FROM 
    table_name 
GROUP BY 
    table_name.id

由于price1，price2，..etc包含在Group by中，此查询是否有效？ – 2014-11-25 07:37:20

@PareshJ：更新了答案 – Arion 2014-11-25 07:42:28

Arion，很好的例子。我一直怀疑静态列值是否需要分组。现在，它很清楚。 – 2014-11-25 07:46:08

答

我刚刚修改了查询，在查询中加入了'top 1'以避免'Subquery返回多个值'的错误。检查并更新您的状态。

select distinct 
    a.id, 'Price1', 
    (select top 1 record_value 
    from table_name 
    where id = a.id and record_name = 'price1') as 'value1', 
    'Price2', 
    (select top 1 record_value 
    from table_name 
    where id = a.id and record_name = 'price2') as 'value2', 
    'Price3', 
    (select top 1 record_value 
    from table_name 
    where id = a.id and record_name = 'price3') as 'value3' 
from 
    table_name a

我需要所有记录，包括重复.....顶1不会为我工作:( – Kumar 2014-11-25 13:23:55

答

你也可以尝试在子查询Distinct。它不是最好的解决方案，但它的工作。

select distinct 
a.id, 'Price1', 
(select distinct record_value 
from table_name 
where id = a.id and record_name = 'price1') as 'value1', 
'Price2', 
(select distinct record_value 
from table_name 
where id = a.id and record_name = 'price2') as 'value2', 
'Price3', 
(select distinct record_value 
from table_name 
where id = a.id and record_name = 'price3') as 'value3' 
from 
table_name a

的SQL Server ...子查询返回多个

相关推荐