子查询返回的值超过1 [英] Subquery returned more than 1 value

问题描述

Using top1 and distinct as variables to make it unique.

我尝试过的：



使用top1和distinct作为变量使其唯一。

What I have tried:

Using top1 and distinct as variables to make it unique.

推荐答案

Noone能够解决您的问题，原因如下：例如：我们无法访问您的数据，您的SQL查询是不可读的，因此很丑陋！对不起...



在我看来，你使用了太多的子查询（ SELECT 语句）！请参阅：

Noone is able to resolve your issue, due to several reasons, such as: we haven't access to your data, your sql query is unreadable and therefore is ugly! Sorry...

In my opinion, you use too many subqueries (SELECT statements)! See:

SELECT     last_name
                            FROM          dbo.l_person
                            WHERE      (person_key = cw.requested_by_person_key)) AS [Referring physician last name],
                          (SELECT     RTRIM(address_line1 + ' ' + ISNULL(address_line2, '')) AS Expr1
                            FROM          dbo.l_person_address
                            WHERE      (person_address_key = cw.requested_by_address_key)) AS [Referring address line 1 + 2  (location seen for selected accession)],
                          (SELECT     RTRIM(city + ', ' + ISNULL(state, '') + ' ' + ISNULL(zipcode, '')) AS Expr1
                            FROM          dbo.l_person_address AS l_person_address_3
                            WHERE      (person_address_key = cw.requested_by_address_key)) AS [Referrring city, state, zip],
                          (SELECT     phone_number
                            FROM          dbo.l_person_address AS l_person_address_2
                            WHERE      (person_address_key = cw.requested_by_address_key)) AS [Referring phone], ISNULL
                          ((SELECT     fax_number
                              FROM         dbo.l_person_address AS l_person_address_1
                              WHERE     (person_address_key = cw.requested_by_address_key)), '') AS [Referring fax], ISNULL
                          ((SELECT     dbo.c_GetNameFromUserID(performed_by_user_id) AS Expr1
                              FROM         dbo.c_study
                              WHERE     (study_key = cw.study_key)), '') AS [Technologist full name (first name, last name, suffix)], pr.description AS [Practice name], 
                      s.description AS [Location name], RTRIM(ISNULL(s.address_line1, '') + ' ' + ISNULL(s.address_line2, '')) AS [Location address line 1 + 2], ISNULL(s.city, '') 
                      + ', ' + ISNULL(s.state, '') + ' ' + ISNULL(s.zipcode, '') AS [Location address city, state, zip], s.main_phone_number AS LocationMainPhone, 
                      s.main_fax_number AS LocationMainfax, CASE WHEN
                          (SELECT     cf.contents
                            FROM          l_config_file cf
                            WHERE      cf.file_name = s.logo_file_name) IS NULL THEN
                          (SELECT     cf.contents
                            FROM          l_config_file cf, l_practice pr
                            WHERE      cf.file_name = pr.logo_file_name AND pr.practice_code = s.practice_code) ELSE
                          (SELECT

你看，有多少 SELECT 的陈述？我甚至无法统计它们！



注意：使用 WHERE 声明很老（过时）练习！你必须使用 JOINs [ ^ ]！



我强烈建议您重新考虑您的查询并从头开始重新编写 - >一步一步，通过连接从另一个表添加其他信息。



有关详细信息，请参阅：

联接类型 [ ^ ]

SQL连接的可视化表示 [ ^ ]

Do you see, how many SELECT statements is there? I can't even count them!

Note: joing tables by using WHERE statement is very old (obsolete) practice! You have to use JOINs[^]!

I'd strongly recommend to re-think your query and re-write it from scratch -> step-by-step, by adding additional information from another table through joins.

For further details, please see:
Types of Joins[^]
Visual Representation of SQL Joins[^]

这篇关于子查询返回的值超过1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！