子查询从查找表返回1个以上的值

Question

我的查询中出现了这个错误。我有一个查询表，存储性别、婚姻状况和客户类型的值。但我想在asp中显示所有在1网格视图。这是工作，如果只有一条记录在数据库中，但当我保存另一个记录的错误appears.Can有人建议修复？

下面是我的问题：

SELECT
  customer.customer_id
  ,customer.first_name
 ,customer.last_name
 ,customer.birth_date
 ,customer.phone
 ,customer.email
 ,customer.block
 ,customer.lot
 ,customer.status
 ,customer.is_deleted
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.gender = lookup_table.lookup_id) AS Gender
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.marital_status = lookup_table.lookup_id) AS MaritalStatus
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.village = lookup_table.lookup_id) AS Village
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.customer_type = lookup_table.lookup_id) AS CustomerType
FROM dbo.customer

提前感谢！

Answer 1

出现这个错误是有道理的。有了一条记录，一切都很好，但是你的子查询有更多的值和更多的记录，ngine不知道该选择什么。因为你根本没有过滤，所以你想要所有的客户都有他们的特征(性别，村庄...)我认为你应该这样做：

SELECT
  customer.customer_id
  ,customer.first_name
 ,customer.last_name
 ,customer.birth_date
 ,customer.phone
 ,customer.email
 ,customer.block
 ,customer.lot
 ,customer.status
 ,customer.is_deleted
 ,Gender
 ,marital_status
 ,village
 ,customer_type

FROM dbo.customer a
inner join
 (SELECT 
 lookup_table.value as Gender, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS Gender on  a.gender = Gender.lookup_id

inner join
 (SELECT 
 lookup_table.value as marital_status, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS marital_status on  a.marital_status = marital_status.lookup_id

inner join
 (SELECT 
 lookup_table.value as village, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS village on  a.village = village.lookup_id

inner join
 (SELECT 
 lookup_table.value as customer_type, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS customer_type on  a.customer_type = customer_type.lookup_id

Answer 2

正如@zip提到的，子查询返回多行并产生问题，您可以尝试这样做并应用筛选器来消除重复项

SELECT
  customer.customer_id
 ,customer.first_name
 ,customer.last_name
 ,customer.birth_date
 ,customer.phone
 ,customer.email
 ,customer.block
 ,customer.lot
 ,customer.status
 ,customer.is_deleted
 ,gender.value AS Gender
 ,MaritalStatus.value AS MaritalStatus
 ,Village.value AS Village
 ,CustomerType.value AS CustomerType
FROM dbo.customer
LEFT OUTER JOIN dbo.lookup_table gender          ON customer.gender = gender.lookup_id
LEFT OUTER JOIN dbo.lookup_table MaritalStatus   ON customer.marital_status = MaritalStatus.lookup_id
LEFT OUTER JOIN dbo.lookup_table Village         ON customer.village = Village.lookup_id
LEFT OUTER JOIN dbo.lookup_table CustomerType    ON customer.customer_type = CustomerType.lookup_id

Answer 3

我听从了zip的回答。他做了一个客户表作为'a‘。将customer替换为'a‘，并删除了性别、marital_status、乡村、customer_type。

下面是查询：

SELECT
  a.customer_id
  ,a.first_name
 ,a.last_name
 ,a.birth_date
 ,a.phone
 ,a.email
 ,a.block
 ,a.lot
 ,a.status
 ,a.is_deleted

FROM dbo.customer a
inner join
 (SELECT 
 lookup_table.value as Gender, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS Gender on  a.gender = Gender.lookup_id

inner join
 (SELECT 
 lookup_table.value as marital_status, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS marital_status on  a.marital_status = marital_status.lookup_id

inner join
 (SELECT 
 lookup_table.value as village, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS village on  a.village = village.lookup_id

inner join
 (SELECT 
 lookup_table.value as customer_type, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS customer_type on  a.customer_type = customer_type.lookup_id