求一个去除重复列的SQL
现有表#K
rq spid dwbh dwmch
2013-11-21SPH00008001DWI00199860太原市鑫月贸易有限公司
2013-09-21SPH00008002DWI00000075山西精诚药业有限公司
2013-11-20SPH00008003DWI00200288山东鑫齐药业有限公司
2013-11-20SPH00008004DWI00199660长治市同安康商贸有限公司药品分公司
2013-11-19SPH00008001DWI00199466苏州中化药品工业有限公司
2013-11-19SPH00008002DWI00200280郑州正和医疗器械有限公司
2013-11-19SPH00008003DWI00114752山西君雁药业有限责任公司长治分公司
2013-11-21SPH00008004DWI00199694山西安盛源药业有限公司
2013-10-21SPH00008001DWI00199694山西安盛源药业有限公司
2013-11-01SPH00008002DWI00199694山西安盛源药业有限公司
2013-11-28SPH00008003DWI00000019湖北盛德堂中药饮片有限公司
2013-11-19SPH00008004DWI00199943湖北盛德堂中药饮片有限公司
2013-11-25SPH00008001DWI00000019期初建账
2013-11-15SPH00008002DWI00199943湖北盛德堂中药饮片有限公司
2013-11-01SPH00008003DWI00199943湖北盛德堂中药饮片有限公司
2013-11-01SPH00008004DWI00199943湖北盛德堂中药饮片有限公司
2013-11-25SPH00008001DWI00000019期初建账
2013-11-19SPH00008002DWI00200280郑州正和医疗器械有限公司
2013-11-28SPH00008003DWI00000019湖北盛德堂中药饮片有限公司
2013-11-21SPH00008004DWI00114761河北信德医药有限公司
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-28 10:41:43
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([rq] datetime,[spid] varchar(11),[dwbh] varchar(11),[dwmch] varchar(34))
insert [huang]
select '2013-11-21','SPH00008001','DWI00199860','太原市鑫月贸易有限公司' union all
select '2013-09-21','SPH00008002','DWI00000075','山西精诚药业有限公司' union all
select '2013-11-20','SPH00008003','DWI00200288','山东鑫齐药业有限公司' union all
select '2013-11-20','SPH00008004','DWI00199660','长治市同安康商贸有限公司药品分公司' union all
select '2013-11-19','SPH00008001','DWI00199466','苏州中化药品工业有限公司' union all
select '2013-11-19','SPH00008002','DWI00200280','郑州正和医疗器械有限公司' union all
select '2013-11-19','SPH00008003','DWI00114752','山西君雁药业有限责任公司长治分公司' union all
select '2013-11-21','SPH00008004','DWI00199694','山西安盛源药业有限公司' union all
select '2013-10-21','SPH00008001','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-01','SPH00008002','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-28','SPH00008003','DWI00000019','湖北盛德堂中药饮片有限公司' union all
select '2013-11-19','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-25','SPH00008001','DWI00000019','期初建账' union all
select '2013-11-15','SPH00008002','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008003','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司'
--------------开始查询--------------------------
SELECT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select MAX(rq)rq,[spid]
from [huang]
GROUP BY [spid])b WHERE a.rq=b.rq AND a.[spid]=b.[spid])
----------------结果----------------------------
/*
rq spid dwbh dwmch
----------------------- ----------- ----------- ----------------------------------
2013-11-19 00:00:00.000 SPH00008002 DWI00200280 郑州正和医疗器械有限公司
2013-11-21 00:00:00.000 SPH00008004 DWI00199694 山西安盛源药业有限公司
2013-11-28 00:00:00.000 SPH00008003 DWI00000019 湖北盛德堂中药饮片有限公司
2013-11-25 00:00:00.000 SPH00008001 DWI00000019 期初建账
*/
select rq,spid,dwbh,dwmch
from
(
select *,
ROW_NUMBER() over(partition by spid order by rq desc) rownum
from tb
)t
where rownum = 1
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-28 10:41:43
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[#k]
if object_id('[#k]') is not null drop table [#k]
go
create table [#k]([rq] datetime,[spid] varchar(11),[dwbh] varchar(11),[dwmch] varchar(34))
insert [#k]
select '2013-11-21','SPH00008001','DWI00199860','太原市鑫月贸易有限公司' union all
select '2013-09-21','SPH00008002','DWI00000075','山西精诚药业有限公司' union all
select '2013-11-20','SPH00008003','DWI00200288','山东鑫齐药业有限公司' union all
select '2013-11-20','SPH00008004','DWI00199660','长治市同安康商贸有限公司药品分公司' union all
select '2013-11-19','SPH00008001','DWI00199466','苏州中化药品工业有限公司' union all
select '2013-11-19','SPH00008002','DWI00200280','郑州正和医疗器械有限公司' union all
select '2013-11-19','SPH00008003','DWI00114752','山西君雁药业有限责任公司长治分公司' union all
select '2013-11-21','SPH00008004','DWI00199694','山西安盛源药业有限公司' union all
select '2013-10-21','SPH00008001','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-01','SPH00008002','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-28','SPH00008003','DWI00000019','湖北盛德堂中药饮片有限公司' union all
select '2013-11-19','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-25','SPH00008001','DWI00000019','期初建账' union all
select '2013-11-15','SPH00008002','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008003','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司'
--------------开始查询--------------------------
SELECT *
FROM #k a
WHERE EXISTS (SELECT 1 FROM (
select MAX(rq)rq,[spid]
from [#k]
GROUP BY [spid])b WHERE a.rq=b.rq AND a.[spid]=b.[spid])
----------------结果----------------------------
/*
rq spid dwbh dwmch
----------------------- ----------- ----------- ----------------------------------
2013-11-19 00:00:00.000 SPH00008002 DWI00200280 郑州正和医疗器械有限公司
2013-11-21 00:00:00.000 SPH00008004 DWI00199694 山西安盛源药业有限公司
2013-11-28 00:00:00.000 SPH00008003 DWI00000019 湖北盛德堂中药饮片有限公司
2013-11-25 00:00:00.000 SPH00008001 DWI00000019 期初建账
*/
if object_id('#k') is not null drop table #k
go
create table #k([rq] datetime,[spid] varchar(11),[dwbh] varchar(11),[dwmch] varchar(34))
insert #k
select '2013-11-21','SPH00008001','DWI00199860','太原市鑫月贸易有限公司' union all
select '2013-09-21','SPH00008002','DWI00000075','山西精诚药业有限公司' union all
select '2013-11-20','SPH00008003','DWI00200288','山东鑫齐药业有限公司' union all
select '2013-11-20','SPH00008004','DWI00199660','长治市同安康商贸有限公司药品分公司' union all
select '2013-11-19','SPH00008001','DWI00199466','苏州中化药品工业有限公司' union all
select '2013-11-19','SPH00008002','DWI00200280','郑州正和医疗器械有限公司' union all
select '2013-11-19','SPH00008003','DWI00114752','山西君雁药业有限责任公司长治分公司' union all
select '2013-11-21','SPH00008004','DWI00199694','山西安盛源药业有限公司' union all
select '2013-10-21','SPH00008001','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-01','SPH00008002','DWI00199694','山西安盛源药业有限公司' union all
select '2013-11-28','SPH00008003','DWI00000019','湖北盛德堂中药饮片有限公司' union all
select '2013-11-19','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-25','SPH00008001','DWI00000019','期初建账' union all
select '2013-11-15','SPH00008002','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008003','DWI00199943','湖北盛德堂中药饮片有限公司' union all
select '2013-11-01','SPH00008004','DWI00199943','湖北盛德堂中药饮片有限公司'
select rq,spid,dwbh,dwmch
from
(
select *,
ROW_NUMBER() over(partition by spid order by rq desc) rownum
from #k
)t
where rownum = 1
/*
rq spid dwbh dwmch
2013-11-25 00:00:00.000SPH00008001DWI00000019期初建账
2013-11-19 00:00:00.000SPH00008002DWI00200280郑州正和医疗器械有限公司
2013-11-28 00:00:00.000SPH00008003DWI00000019湖北盛德堂中药饮片有限公司
2013-11-21 00:00:00.000SPH00008004DWI00199694山西安盛源药业有限公司
*/