如何从查询中获取结果，其中第一列在更改之前不会重复其值 [英] how to get a result from a query where the first columns do not repeat their value until it changes

问题描述

我有一个只返回几行的查询，但我需要以这样一种方式对结果进行分组，即前两列在它们的值发生变化之前不会重复.

I have a query that returns just a few rows but I need the results grouped in such a way that the first 2 columns do not repeat until their values changes.

让我告诉你我的意思，这样会更容易.
这是查询:

Let me show you what I mean that will be easier.
This is the query:

select isnull(d.FirstName, '') + ' ' + isnull(d.LastName, '') as Chauffeur,
       t.securysatnr,
       convert(date, p.PlanningDate) as Datum,
       p.StartTime as LaadUur,
       r.LotNr,
       isnull(pFrom.City, '') + ' - ' + isnull(pTo.City, '') as RitInfo 
from   tblPlanning p
  left outer join vwRit r on p.RitID = r.RitID
  left outer join tblPlace pFrom on r.VertrekID = pFrom.PlaceID
  left outer join tblPlace pTo on r.BestemmingID = pTo.PlaceID
  inner join tblDriver d on p.DriverID = d.DriverID
  inner join tblTruck t on p.TruckID = t.TruckID
where  convert(date, p.PlanningDate) >= convert(date, getdate())
order by Chauffeur, convert(date, p.PlanningDate), p.StartTime

这是返回的结果集:

Chauffeur   securysatnr     Datum       LaadUur  LotNr    RitInfo   
---------   -----------     -----       -------  -----    -------   
Aloyzyas    JBB017          2017-07-12    13     RT-0181  Creutzwald (Tramosa) - Koln TS    
Aloyzyas    JBB017          2017-07-12    20     EMPTY    Koln TS - Neuss (ATN) 
Aloyzyas    JBB017          2017-07-13     6     2094935  Neuss (ATN) - Zulpich WWL 
Andrii      HT5485 FB-CA    2017-07-12    14     EMPTY    Zulpich WWL - Born (Nedcar)   
Andrii      HT5485 FB-CA    2017-07-12    16     1709426  Born (Nedcar) - Zeebrugge Hanze terminal 521-525  
Andrii      HT5485 FB-CA    2017-07-13     8     0006620  Zeebrugge ICO gate 502 - Niederkorn (Collé)   
Darius      HPV472          2017-07-12    17     0006624  Zeebrugge ICO gate 502 - Antwerpen 1333   
and so on...

这是我想要得到的结果:

And this is the result I would like to get:

Chauffeur   securysatnr    Datum      LaadUur   LotNr     RitInfo   
---------   -----------    -----      -------   -----     -------   
Aloyzyas    JBB017          2017-07-12    13    RT-0181   Creutzwald (Tramosa) - Koln TS    
                            2017-07-12    20    EMPTY     Koln TS - Neuss (ATN) 
                            2017-07-13     6    2094935   Neuss (ATN) - Zulpich WWL 
Andrii      HT5485 FB-CA    2017-07-12    14    EMPTY     Zulpich WWL - Born (Nedcar)   
                            2017-07-12    16    1709426   Born (Nedcar) - Zeebrugge Hanze terminal 521-525  
                            2017-07-13     8    0006620   Zeebrugge ICO gate 502 - Niederkorn (Collé)   
Darius      HPV472          2017-07-12    17    0006624   Zeebrugge ICO gate 502 - Antwerpen 1333   
and so on...

我希望这能说明我想要的结果.
这在 sql-server 中可能吗?如果是，那么我怎么能在偏离路线的情况下做到这一点.

I hope this makes clear what I want for result.
Is this possible in sql-server ? And if yes then how can I do it offcourse.

一种选择是在存储过程中执行此操作，然后循环遍历记录并填充并返回临时表.但我希望有一个没有存储过程的解决方案.

One option would be to do it in a stored procedure and just loop over the records and fill and return a temp table. But I was hoping for a solution without stored procedure.

我知道这通常应该在表示层完成，但在这种情况下很难.它是一种从另一个表接收要执行的查询的表单，因此它不知道将收到什么结果集.因此，在这种情况下，不可能在表示层上执行此操作，除非有人知道我在这种情况下如何执行此操作.

I know this should normally be done in presentation layer but in this case it is difficult. It is a form that receives queries to execute from another table, so it has no knowledge of what resultsets will be received. Therefor it will be not possible in this case to do it on the presentation layer, unless someone has an idea how I can do it here in this situation.

推荐答案

这是表示/格式问题，而不是数据检索问题.

This is presentation/formatting issue not a data retrieval problem.

但是，如果您真的被这个问题所困扰，可以这样做...

However in case you really are stuck with this problem, it can be done...

注意:未测试，您可能需要使用 LEAD 而不是 LAG

NOTE: not tested, you may need to use LEAD not LAG

SELECT
    Chauffeur = CASE WHEN  X.prevsecurysatnr =  X.securysatnr THEN '' ELSE   X.Chauffeur END,
    securysatnr = CASE WHEN  X.prevsecurysatnr =  X.securysatnr THEN '' ELSE   X.securysatnr END,
     X.Datum,
     X.LaadUur,
     X.LotNr,
     X.RitInfo
FROM
    (
    SELECT
            LAG(t.securysatnr) OVER (ORDER BY ISNULL(d.FirstName, '') + ' ' + isnull(d.LastName, ''), convert(date, p.PlanningDate), p.StartTime) AS prevsecurysatnr,
            ISNULL(d.FirstName, '') + ' ' + isnull(d.LastName, '') as Chauffeur,
            t.securysatnr,
            convert(date, p.PlanningDate) as Datum,
            p.StartTime as LaadUur,
            r.LotNr,
            isnull(pFrom.City, '') + ' - ' + isnull(pTo.City, '') as RitInfo 
    from   
        tblPlanning p
        left outer join vwRit r on p.RitID = r.RitID
        left outer join tblPlace pFrom on r.VertrekID = pFrom.PlaceID
        left outer join tblPlace pTo on r.BestemmingID = pTo.PlaceID
        inner join tblDriver d on p.DriverID = d.DriverID
        inner join tblTruck t on p.TruckID = t.TruckID
    where  convert(date, p.PlanningDate) = convert(date, getdate())
    ) X
order by 
    X.Chauffeur,  X.Datum,  X.LaadUur

这篇关于如何从查询中获取结果，其中第一列在更改之前不会重复其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！