首页 \ 问答 \ 如何重复显示重复值一次，如果重复，则显示“ - ”(How can I display repeated values only one time and have '-' if it repeats)

如何重复显示重复值一次，如果重复，则显示“ - ”(How can I display repeated values only one time and have '-' if it repeats)

 我有一些子查询可以为每个PolicyNumber检索相同的值。 如何用' - '替换重复值，并且只在每个策略的顶行显示一个？ 现在我有这个：  但是我需要这样的东西：   
 SELECT
    -------------/* GrossPremium*/

           (SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
                 FROM tblFin_InvoiceDetails WITH (NOLOCK)
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))
                 AS GrossPremium

--------------/*CompanyCommissionPercentage*/

                ,((SELECT ISNULL(SUM(tblFin_InvoiceDetails.MGAAmt), 0)
                 FROM tblFin_InvoiceDetails
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))
                 +
                    CASE WHEN INV.Remitter = 'B' then
                    (SELECT ISNULL(SUM(tblFin_InvoiceDetails.RemitterAmt), 0)
                     FROM tblFin_InvoiceDetails  
                     WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                     AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))----------------RemitterCommission

                ELSE
                    (SELECT ISNULL(SUM(tblFin_InvoicedItemsPayees.PayeeAmt), 0) 
                     FROM tblFin_InvoicedItemsPayees
                    INNER JOIN tblFin_PolicyCharges pc on pc.ChargeCode = tblFin_InvoicedItemsPayees.ChargeCode and pc.chargeType = 'P'   
                     WHERE (tblFin_InvoicedItemsPayees.InvoiceNum = INV.InvoiceNum and tblFin_InvoicedItemsPayees.PayeeGuid = INV.ProducerLocationGuid))
                END) * 100 / 
                NULLIF((SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
                 FROM tblFin_InvoiceDetails WITH (NOLOCK)
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum)),0)
                 AS CompanyCommissionPercentage
FROM [tblFin_PayablesWorking] PW
INNER JOIN tblFin_Invoices INV ON PW.InvoiceNumber=INV.InvoiceNum

I have some subqueries that retreives the same values for each PolicyNumber. How can I substitute repeated value with '-' and only display it one in a top row for each policy? Right now I have this:  But I need something like this:  
 SELECT
    -------------/* GrossPremium*/

           (SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
                 FROM tblFin_InvoiceDetails WITH (NOLOCK)
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))
                 AS GrossPremium

--------------/*CompanyCommissionPercentage*/

                ,((SELECT ISNULL(SUM(tblFin_InvoiceDetails.MGAAmt), 0)
                 FROM tblFin_InvoiceDetails
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))
                 +
                    CASE WHEN INV.Remitter = 'B' then
                    (SELECT ISNULL(SUM(tblFin_InvoiceDetails.RemitterAmt), 0)
                     FROM tblFin_InvoiceDetails  
                     WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                     AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))----------------RemitterCommission

                ELSE
                    (SELECT ISNULL(SUM(tblFin_InvoicedItemsPayees.PayeeAmt), 0) 
                     FROM tblFin_InvoicedItemsPayees
                    INNER JOIN tblFin_PolicyCharges pc on pc.ChargeCode = tblFin_InvoicedItemsPayees.ChargeCode and pc.chargeType = 'P'   
                     WHERE (tblFin_InvoicedItemsPayees.InvoiceNum = INV.InvoiceNum and tblFin_InvoicedItemsPayees.PayeeGuid = INV.ProducerLocationGuid))
                END) * 100 / 
                NULLIF((SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
                 FROM tblFin_InvoiceDetails WITH (NOLOCK)
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum)),0)
                 AS CompanyCommissionPercentage
FROM [tblFin_PayablesWorking] PW
INNER JOIN tblFin_Invoices INV ON PW.InvoiceNumber=INV.InvoiceNum

原文：https://stackoverflow.com/questions/39605127

更新时间：2024-04-23 11:04

最满意答案

 如果我理解正确，首先要确定与给定datetime范围内的多个category关联的username值：  
SELECT   username
FROM     myTable
WHERE    datetime BETWEEN :start AND :end
GROUP BY username
HAVING   COUNT(DISTINCT category) > 1
 
 然后，您想要检索给定datetime范围内的所有记录，这些用户名按username和datetime时间排序：  
SELECT   *
FROM     myTable NATURAL JOIN (
           SELECT   username
           FROM     myTable
           WHERE    datetime BETWEEN :start AND :end
           GROUP BY username
           HAVING   COUNT(DISTINCT category) > 1
         ) t
WHERE    datetime BETWEEN :start AND :end
ORDER BY username, datetime

If I've understood correctly, you first want identify those username values that are associated with more than one category in the given datetime range: 
SELECT   username
FROM     myTable
WHERE    datetime BETWEEN :start AND :end
GROUP BY username
HAVING   COUNT(DISTINCT category) > 1
 
And then you want to retrieve all records within the given datetime range for those usernames, sorted by username and datetime: 
SELECT   *
FROM     myTable NATURAL JOIN (
           SELECT   username
           FROM     myTable
           WHERE    datetime BETWEEN :start AND :end
           GROUP BY username
           HAVING   COUNT(DISTINCT category) > 1
         ) t
WHERE    datetime BETWEEN :start AND :end
ORDER BY username, datetime

如何重复显示重复值一次，如果重复，则显示“ - ”(How can I display repeated values only one time and have '-' if it repeats)

最满意答案

相关问答

如何从两个不同的表中获取两个字段的计数，并在mysql中对另一个表中的字段进行分组(How to get count of two fields from two different table with grouping a field from another table in mysql)[2023-02-20]

如何按某些字段分组并在oracle中显示另一个字段的第一个值？(How to group by some fields and display the first value of another field in oracle?)[2022-08-26]

查询在一个字段中相同但在另一个字段中不同的记录？(Query for records that are the same in one field, but different in another?)[2021-12-20]

是否可以在MySQL中返回表的第n个字段？(Is it possible to return the nth field of a table in MySQL?)[2021-12-19]

相关文章

最新问答