维护表，使用记录详细信息作为列标题并仅提取最新维护的数据表示

I've tried to find a solution to this issue I'm facing but I'm not sure how to advance. To be honest I am not too sure how to even phrase my question, but please bear with me.

我试图找到解决这个问题的方法,但我不确定如何推进。说实话,我不太确定如何表达我的问题,但请耐心等待我。

I have a table which is essentially a table which is written into every time an update on a customer record is made, its an odd table but it has been in use for quite awhile and contains over a thousand records. The customer records are not often updated due to the nature of the business.

我有一个表,它基本上是一个表,每当客户记录的更新被写入时,它是一个奇怪的表,但它已经使用了很长一段时间,包含超过一千条记录。由于业务性质,客户记录通常不会更新。

Now since the table is updated every time an update to the customer record is performed, it is such that each record in this table only contains 4 parts, the customerID, the field which was updated, the details which were updated into the said field, and the date on which the update was performed. Sample:

现在,由于每次执行客户记录更新时表都会更新,因此该表中的每条记录只包含4个部分,即customerID,已更新的字段,更新到所述字段的详细信息,以及执行更新的日期。样品:

--------------------------------------------------------------------
| CUSTID            UPDFIELD    UPDDETAILS              UPDDATE    | 
|------------------------------------------------------------------|
| 444001001201111   custName    Smith Worlogh           2014-02-01 |
| 444001001201111   discRate    5%                      2014-05-01 |
| 444001001201111   custName    Smith Warlogh           2014-05-07 |
| 444001001201111   custEmail   smithkiw67@yahoo.com    2014-05-10 |
| 444001001201112   custName    Pauline HG Stone        2014-02-01 |
| 444001001201112   discRate    5%                      2014-05-07 |
| 444001001201112   custEmail   sama8kitty@live.co.uk   2014-05-11 |
| 444001001201112   discRate    8%                      2014-05-15 |
| 444001001201113   custName    Jayden Rothschild       2014-05-01 |
| 444001001201113   discRate    8%                      2014-05-07 |
| 444001001201113   custEmail   mr.bombastic@gmail.com  2014-05-11 |
--------------------------------------------------------------------

From that table I want to present the data in a way where all the details are shown for each customerID and in the last column a date, corresponding to the last update for that particular record is shown. Using the Sample above, I want to present the data like this

从该表中我想以一种方式呈现数据,其中显示每个customerID的所有细节,并且在最后一列中显示与该特定记录的最后更新相对应的日期。使用上面的示例,我想呈现这样的数据

-------------------------------------------------------------------------------------
| CUSTID           CUSTOMERNAME       CUSTOMEREMAIL           DISCRATE  LASTUPDATED |
|-----------------------------------------------------------------------------------|
| 444001001201111  Smith Warlogh      smithkiw67@yahoo.com    5%        2014-05-10  |
| 444001001201112  Pauline HG Stone   sama8kitty@live.co.uk   8%        2014-05-15  |
| 444001001201113  Jayden Rothschild  mr.bombastic@gmail.com  8%        2014-05-11  |
-------------------------------------------------------------------------------------

I believe my first step should be to find the most recent update for each particular customer which I managed to do with this simple SELECT statement

我相信我的第一步应该是找到每个特定客户的最新更新,我使用这个简单的SELECT语句设法完成

SELECT MAX(UpdDate) AS LatestUpd,CustID
FROM trackChangeA
GROUP BY CustID;

After that I thought I could use the above results in a subquery to extract the latest updates for each field for each respective customer but instead everything is returned.

之后我想我可以在子查询中使用上述结果来为每个相应的客户提取每个字段的最新更新,但是返回所有内容。

I am unsure how to proceed from here. Again I did try looking through the SQL section for something similar but all I managed to find was a way to use DISTINCT but trying that didn't change the results much.

我不确定如何从这里开始。我再次尝试通过SQL部分查找类似的内容,但我设法找到的是一种使用DISTINCT的方法,但尝试这并没有改变结果。

I've been trying to try solutions on the sample table in SQL fiddle but so far to no success. Here's the link if it helps: SQL Fiddle

我一直在尝试在SQL小提琴中尝试解决方案,但到目前为止还没有成功。这是链接,如果它有帮助:SQL小提琴

2 个解决方案

#1

You are definitely on the right track by using the max() aggregate to get the most recent date for each CustId. I'd suggest a few minor changes to get the final result.

通过使用max()聚合来获取每个CustId的最新日期,您肯定是在正确的轨道上。我建议进行一些小改动以获得最终结果。

First, using your existing query to join the max(UpdDate) to your table, then use the windowing function row_number() to get the most recent value for each of the UpdField values. So the start of your query will be:

首先,使用现有查询将max(UpdDate)连接到表,然后使用窗口函数row_number()获取每个UpdField值的最新值。所以你的查询的开始将是:

select a.CustId, a.UpdField, a.UpdDetails, a.UpdDate, md.maxDate,
  row_number() over(partition by a.CustId, a.UpdField
                    order by a.UpdDate desc) seq
from trackChangeA a
inner join
(
  select max(UpdDate) maxDate, CustId
  from trackChangeA
  group by CustId
) md
  on a.CustId = md.CustId;

See SQL Fiddle with Demo. This windowing function will create a unique sequenced value for each combination of the CustId and the UpdField, ordering those values by the UpdDate. From this query, you'll only need those rows where the sequence is 1 or the first row.

请参阅SQL Fiddle with Demo。此窗口函数将为CustId和UpdField的每个组合创建唯一的排序值,并通过UpdDate对这些值进行排序。从此查询中,您只需要序列为1或第一行的行。

Once you've done this, then you can pivot the data easily using an aggregate function with a CASE expression so the final query will be similar to:

完成此操作后,您可以使用带有CASE表达式的聚合函数轻松地转动数据,以便最终查询类似于:

select CustId,
  CustomerName = max(case when updfield = 'custName' then UpdDetails end),
  CustomerEmail = max(case when updfield = 'custEmail' then UpdDetails end),
  DiscRate = max(case when updfield = 'discRate' then UpdDetails end),
  LastUpdated = MaxDate 
from
(
  select a.CustId, a.UpdField, a.UpdDetails, a.UpdDate, md.maxDate,
    row_number() over(partition by a.CustId, a.UpdField
                      order by a.UpdDate desc) seq
  from trackChangeA a
  inner join
  (
    select max(UpdDate) maxDate, CustId
    from trackChangeA
    group by CustId
  ) md
    on a.CustId = md.CustId
) d
where seq = 1
group by CustId, MaxDate;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

You could also use the PIVOT function to get the result. The syntax would be similar to:

您也可以使用PIVOT函数来获得结果。语法类似于:

select CustId,
  CustomerName = custName,
  CustomerEmail = custEmail,
  DiscRate = discRate,
  LastUpdated = MaxDate 
from
(
  select a.CustId, a.UpdField, a.UpdDetails, a.UpdDate, md.maxDate,
    row_number() over(partition by a.CustId, a.UpdField
                      order by a.UpdDate desc) seq
  from trackChangeA a
  inner join
  (
    select max(UpdDate) maxDate, CustId
    from trackChangeA
    group by CustId
  ) md
    on a.CustId = md.CustId
) d
pivot
(
  max(UpdDetails)
  for UpdField in (custName, custEmail, discRate)
) piv
where seq = 1;

See SQL Fiddle with Demo. Both will return the result:

请参阅SQL Fiddle with Demo。两者都将返回结果:

|          CUSTID |      CUSTOMERNAME |          CUSTOMEREMAIL | DISCRATE | LASTUPDATED |
|-----------------|-------------------|------------------------|----------|-------------|
| 444001001201111 |     Smith Warlogh |   smithkiw67@yahoo.com |       5% |  2014-05-10 |
| 444001001201112 |  Pauline HG Stone |  sama8kitty@live.co.uk |       8% |  2014-05-15 |
| 444001001201113 | Jayden Rothschild | mr.bombastic@gmail.com |       8% |  2014-05-11 |

Edit: Based on your question whether or not you can use select distinct to get the list of columns names for the pivot, the answer is Yes. You can use dynamic SQL to generate the list of names that will be displayed in the final result. This process creates a query string that will then be executed by SQL Server. The basic syntax will be:

编辑:根据您的问题,您是否可以使用select distinct获取数据透视表的列名列表,答案为是。您可以使用动态SQL生成将在最终结果中显示的名称列表。此过程创建一个查询字符串,然后由SQL Server执行。基本语法是:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- generate the list of columns
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(UpdField) 
                    from trackChangeA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- generate the sql string
set @query = N'SELECT CustId,' + @cols + N',  LastUpdated = MaxDate 
            from 
            (
              select a.CustId, a.UpdField, a.UpdDetails, md.maxDate,
                row_number() over(partition by a.CustId, a.UpdField
                                  order by a.UpdDate desc) seq
              from trackChangeA a
              inner join
              (
                select max(UpdDate) maxDate, CustId
                from trackChangeA
                group by CustId
              ) md
                on a.CustId = md.CustId
            ) x
            pivot 
            (
                max(UpdDetails)
                for UpdField in (' + @cols + N')
            ) p 
            where seq = 1'

-- execute the sql string
execute sp_executesql @query;

See SQL Fiddle with Demo. This will give the same result as the previous version.

请参阅SQL Fiddle with Demo。这将得到与先前版本相同的结果。

#2

I think this is what you need:

我想这就是你需要的:

SELECT DISTINCT a.CustID,
   (SELECT TOP 1 UpdDetails
    FROM trackChangeA
    WHERE CustID=a.CustID AND UpdField='custName'
    ORDER BY UpdDate DESC
   ) AS custName,
   (SELECT TOP 1 UpdDetails
    FROM trackChangeA
    WHERE CustID=a.CustID AND UpdField='custEmail'
    ORDER BY UpdDate DESC
   ) AS custEmail,
   (SELECT TOP 1 UpdDetails
    FROM trackChangeA
    WHERE CustID=a.CustID AND UpdField='discRate'
    ORDER BY UpdDate DESC
   ) AS discRate,
   MAX(UpdDate) AS LastUpdated
FROM trackChangeA a
GROUP BY a.CustID;

The TOP 1 can be substituted with LAST() on some RDBMS, but not on SQL-Server.

TOP 1可以在某些RDBMS上替换为LAST(),但不能在SQL-Server上替换。

Essentially you are getting the CustID list with the last global update date from a grouped by select on the table, while the last values of the 3 update type are fetched by subselects.

从本质上讲,您将获得CustID列表,其中最后一个全局更新日期来自表中的select by,而3更新类型的最后一个值是通过子选择获取的。

#1