同一行中的多个记录数据。 [英] Multiple records data in same row.

问题描述

我的要求如下。

I have requirement as follows .

Id     createddt createdby   modifideby modifydt

1      21/2/2015    2         NULL       NULL
1      NULL         NULL       4        22/2/2015
2      22/2/2015     3         NULL      NULL
2      NULL         NUll       3         23/2/2015
1      NULL         NULL       5         27/2/2015

我需要如下：

I need As follows :

Id     createddt createdby   modifideby modifydt

1      21/2/2015    2          5         27/2/2015
2      22/2/2015     3         3         23/2/2015

我需要创建日期并上次修改日期在一行......

I need created date and last modified date in one row ......

推荐答案

使用max和min应该做的诀窍：

Use max and min should do the trick:

select id, min(createddt), max(createdby), max(modifideby), max(modifydt) from tablename
group by id

用最简单的术语你可以做

In it's simplest terms you can just do

select Id, max(createddt), max(createdby), max(modifideby), max(modifydt)
from tablename
group by Id

这将为您提供您期望的结果。请注意， MAX [ ^ ]忽略NULL值。



我怀疑那里更符合您的要求 - 您的课程是否与PIVOT有关？若有，请阅读本文在SQL查询中使用Pivot的简单方法 [ ^ ]

which will give you the results you expect. Note that MAX[^] ignores NULL values.

I suspect there is more to your requirement however - is your course work to do with PIVOT? If so then read this article Simple Way To Use Pivot In SQL Query[^]

试试这个：

Try this:

SELECT t1.Id, t1.createddt, t1.createdby, t2.modifiedby, t2.modifydt
FROM TableName AS t1 INNER JOIN 
     (
         SELECT Id, MAX(modifydt) AS modifydt
         FROM TableName 
         GROUP BY Id
     ) AS t2 ON t1.Id = t2.Id AND t1.modifydt = t2.modifydt

这篇关于同一行中的多个记录数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！