如何在cfoutput中显示不同的值 [英] How to display Distinct values in cfoutput

问题描述

我有一个查询，该查询从数据库返回结果。它返回重复的信息。我想防止显示重复数据。

I have a query that returns results from the database. It returns duplicate information. I would like to prevent showing the duplicate data.

重复数据的意思是准入日期，服务日期和出院日期列重复相同的数据（在这种情况下为日期）。因此，如果日期是10/05/2019，则对于``允许日期''，它将重复日期两次或更多次。这是由于处理日期列所致，在该列中，提交查询的过程是在不同的日期进行的，并导致其他日期重复两次。

What I mean by duplicate data is that the Admit Date, Service Date, and Discharge Date columns repeats the same data (in this case dates). So if the date is 10/05/2019, for Admit Date, it will repeat the date 2 or more times. This is due to the Process Date column where the process of submitting the inquiry were done in different dates and causes the other dates to repeat twice.

我最初尝试使用 SELECT DISTINCT Column1，Column2，... FROM Data 在我的查询中，但出错了。以下是我遇到的错误：

I originally tried using SELECT DISTINCT Column1, Column2, ... FROM Data in my query but it errors outs. The following is the error I am getting:

错误执行数据库查询。 [Macromedia] [SQLServer JDBC
驱动程序] [SQLServer]由于文本数据类型不可比较，因此无法将其选择为DISTINCT
。

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The text data type cannot be selected as DISTINCT because it is not comparable.

我在做什么错？任何帮助将不胜感激

What am I doing wrong? Any help would be appreciated

这是我最近的尝试：

<cfquery name="name" datasource="source">
        SELECT First_Name, 
               Last_Name, 
               DOB, Sex, 
               Service, 
               Service_Desc, 
               Distinct(service_dt) AS Service_Dt, 
               Distinct(admit_dt) AS Admit_Dt, 
               Phone, 
               Address1, 
               Address2, 
               City, 
               State, 
               Zip, 
               Account, 
               Hosp_Name,
               MR_Number, 
               Insurance, 
               Plan_Name, 
               Policy_No, 
               Group_No, 
               Reason_For_Visit, 
               Distinct(process_dt) AS Process_Dt,
               Distinct(discharge_dt) AS Discharge_Dt,
               [Pt Class] as PtClass
        FROM DATA
        WHERE 
            First_Name = <cfqueryparam value="#FName#" cfsqltype="cf_sql_varchar">
            AND
            Last_Name = <cfqueryparam value="#LName#" cfsqltype="cf_sql_varchar">
            AND
            DOB = <cfqueryparam value="#BirthDt#" cfsqltype="cf_sql_varchar">
            AND
            Hosp_Name like <cfqueryparam value="#Hosp#" cfsqltype="cf_sql_varchar">
    </cfquery>

推荐答案

首先，这纯粹是 SQL 问题，根本不是 ColdFusion 问题。

First things first, this is purely a SQL issue, not at all a ColdFusion issue.

除了不同的日期列之外，您的数据对于所有记录都是一致的吗？假设有四个记录，其中 user_id = 1 ：

Your data is consistent for all records, with the exception of the different date columns? Let's say there are four records for user_id = 1:

| user_id | admit_date | service_date | process_date | discharge_date |
| ------- | ---------- | ------------ | ------------ | -------------- |
| 1       | 2018-01-01 | 2018-01-01   | 2018-01-02   | 2018-01-05     |
| 1       | 2018-01-01 | 2018-01-02   | 2018-01-02   | 2018-01-05     |
| 1       | 2018-01-01 | 2018-01-03   | 2018-01-03   | 2018-01-05     |
| 1       | 2018-01-01 | 2018-01-04   | 2018-01-04   | 2018-01-05     |

这是一个人的数据吗？

Is this what your data looks like for a single person?

我想象这个数据只是汇总的数据转储，所以 admit_date 对于所有4个都是相同的记录，以及 discharge_date 。每个记录的其他两个日期都不同。因为该用户的名字，姓氏，出生日期相同，并且在同一家医院，所以它与您的查询条件匹配。

I'm imagining this data is just an aggregated data dump, so the admit_date is the same for all 4 records, as is the discharge_date. The other two dates are different for each record. Because this one user has the same first name, last name, dob and was at the same hospital, it matches your query criteria.

如果这是好的数据，则选择一个不同的 admit_date 会给我4条记录，选择一个不同的放电日期。

If this is good data, then selecting a distinct admit_date will give me 4 records, as will selecting a distinct discharge_date.

• 您是否要查找此用户或所有用户的最新记录？


• 您要查找第一个吗？


• 您实际上是想从这些数据中提取什么？

如果要显示按单个日期分组的数据输出，这很容易：

If you wanted to show an output of data, grouped by a single date, that's pretty easy:

<cfoutput query="qData" group="user_id">
    <!--- Output User Info here. --->
    <cfoutput group="admit_date">
        <!--- 
            This will loop over all records of the same 
            admit_date for the current user ID. 
        --->
        <cfoutput>
            More output.
        </cfoutput
    </cfoutput>
</cfoutput>

这篇关于如何在cfoutput中显示不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！