下表筛选并增加几个字段？

Question

------建表
create table #test(计算机名 varchar(20), MAC地址 varchar(20), 创建日期 varchar(20))
insert into #test
select 'A_A', '00:00:00:00:00:01' ,'2014/8/4 7:58' union all
select 'B', '00:00:00:00:00:02' ,'2014/8/4 0:00' union all
select 'C' ,'00:00:00:00:00:03', '2014/8/3 0:00' union all
select 'B_B', '00:00:00:00:00:02' ,'2014/8/3 0:00' union all
select 'A' ,'00:00:00:00:00:01', '2014/8/2 14:03' union all
select 'C' ,'00:00:00:00:00:03', '2014/8/2 14:03' union all
select 'B' ,'00:00:00:00:00:02', '2014/8/2 14:03'---查询
select 计算机名,MAC地址,MAX(创建日期)[创建日期],MIN(创建日期)[最早创建日期],COUNT(1)[条数]
from #test
group by 计算机名,MAC地址 ---结果 
计算机名   MAC地址            创建日期          最早创建日期      条数
A         00:00:00:00:00:01   2014/8/2 14:03    2014/8/2 14:03      1
A_A       00:00:00:00:00:01   2014/8/4 7:58     2014/8/4 7:58       1
B         00:00:00:00:00:02   2014/8/4 0:00     2014/8/2 14:03      2
B_B       00:00:00:00:00:02   2014/8/3 0:00     2014/8/3 0:00       1
C         00:00:00:00:00:03   2014/8/3 0:00     2014/8/2 14:03      2

Answer 1

WITH CTE AS(
SELECT 
[计算机名],
[MAC地址],
[创建日期],
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期] DESC) CNT
)
, CTE2 AS(
SELECT 
[计算机名],
[MAC地址],
[创建日期],
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期]) CNT
)
SELECT A.[计算机名],A.[MAC地址],A.[创建日期],B.[创建日期] AS [最早创建日期],a.cnt+b.cnt-1 AS [条数]
FROM cte a JOIN b.cte2 ON a.[MAC地址]=b.[MAC地址] WHERE a.cnt=1

Answer 2

create table #test(计算机名 varchar(20),    MAC地址 varchar(20),    创建日期 varchar(20))
insert into #test
select 'A_A',    '00:00:00:00:00:01'    ,'2014/8/4 7:58' union all
select 'B',    '00:00:00:00:00:02'    ,'2014/8/4 0:00' union all
select 'C'    ,'00:00:00:00:00:03',    '2014/8/3 0:00' union all
select 'B_B',    '00:00:00:00:00:02'    ,'2014/8/3 0:00' union all
select 'A'    ,'00:00:00:00:00:01',    '2014/8/2 14:03' union all
select 'C'    ,'00:00:00:00:00:03',    '2014/8/2 14:03' union all
select 'B'    ,'00:00:00:00:00:02',    '2014/8/2 14:03';WITH CTE AS(
SELECT 
[计算机名],
[MAC地址],
[创建日期],
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期] DESC) CNT
FROM #test
)
, CTE2 AS(
SELECT 
[计算机名],
[MAC地址],
[创建日期],
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期]) CNT
FROM #test
)
, cte3 AS(
SELECT A.[计算机名],A.[MAC地址],A.[创建日期],B.[创建日期] AS [最早创建日期],a.cnt+b.cnt-1 AS [条数]
FROM cte a JOIN cte2 b ON a.[MAC地址]=b.[MAC地址] WHERE a.cnt=1
)
SELECT * FROM cte3 WHERE [条数]=1计算机名                 MAC地址                创建日期                 最早创建日期               条数
-------------------- -------------------- -------------------- -------------------- --------------------
A_A                  00:00:00:00:00:01    2014/8/4 7:58        2014/8/2 14:03       1
B                    00:00:00:00:00:02    2014/8/4 0:00        2014/8/2 14:03       1
C                    00:00:00:00:00:03    2014/8/3 0:00        2014/8/2 14:03       1(3 行受影响)

Answer 3

不对呀，请注意上面我要的结果，谢谢！
你那个A_A跟A是什么关系，是一台主机吗，为啥名字不一样啊

Answer 4

不对呀，请注意上面我要的结果，谢谢！
你那个A_A跟A是什么关系，是一台主机吗，为啥名字不一样啊计算机名   MAC地址            创建日期          最早创建日期      条数
A         00:00:00:00:00:01   2014/8/2 14:03    2014/8/2 14:03      1
A_A       00:00:00:00:00:01   2014/8/4 7:58     2014/8/4 7:58       1
B         00:00:00:00:00:02   2014/8/4 0:00     2014/8/2 14:03      2
B_B       00:00:00:00:00:02   2014/8/3 0:00     2014/8/3 0:00       1
C         00:00:00:00:00:03   2014/8/3 0:00     2014/8/2 14:03      2
刚才结果贴错了

Answer 5

不对呀，请注意上面我要的结果，谢谢！
你那个A_A跟A是什么关系，是一台主机吗，为啥名字不一样啊..
刚才结果贴错了
计算机名发生的改变，但MAC地址没有变，最后的结果还是不对。MAC为01的要合并取最新一条，但是又要保留第一次的创建日期。

Answer 6

上面的条数有点问题，改了一下，可以用了
WITH CTE AS(
SELECT 
*,ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期] DESC) cnt_desc,
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期] DESC)+
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期])-1 CNT
FROM #test
)
,cte2 AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [MAC地址] ORDER BY [创建日期]) cnt
FROM #test
)
SELECT  a.[计算机名], a.[MAC地址],a.[创建日期],b.[创建日期] AS [最早创建日期],a.cnt AS [条数] FROM cte a JOIN cte2 b ON a.[MAC地址]=b.[MAC地址]
WHERE b.cnt=1 AND a.cnt_desc=1.计算机名                 MAC地址                创建日期                 最早创建日期               条数
-------------------- -------------------- -------------------- -------------------- --------------------
A_A                  00:00:00:00:00:01    2014/8/4 7:58        2014/8/2 14:03       2
B                    00:00:00:00:00:02    2014/8/4 0:00        2014/8/2 14:03       3
C                    00:00:00:00:00:03    2014/8/3 0:00        2014/8/2 14:03       2(3 行受影响)