OID SS CC
------------------------
001
001 L RED
001 L RED
002 M
003 WHITE想把数据合并成
OID SS CC
------------------------
001 L RED
002 M
003 WHITE能够实现吗,请高手指教!!!
------------------------
001
001 L RED
001 L RED
002 M
003 WHITE想把数据合并成
OID SS CC
------------------------
001 L RED
002 M
003 WHITE能够实现吗,请高手指教!!!
FROM TB
GROUP BY OID
------------------------
insert tb select '001' ,null,null
insert tb select '001', 'L', 'RED'
insert tb select '001', 'L', 'RED'
insert tb select '002', 'M', null
insert tb select '003', 'WHITE',null select oid,max(ss) ss,max(cc) cc from tb group by oid
/*
oid ss cc
---------- ---------- ----------
001 L RED
002 M NULL
003 WHITE NULL
*/
drop table tb
------------------------
insert tb select '001' ,null,null
insert tb select '001', 'L', 'RED'
insert tb select '001', 'L', 'RED'
insert tb select '002', 'M', null
insert tb select '003', 'WHITE',null select oid,max(isnull(ss,'')) ss,max(isnull(cc,'')) cc from tb group by oid
/*
oid ss cc
---------- ---------- ----------
001 L RED
002 M
003 WHITE (3 行受影响)
*/
drop table tb
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-05 08:26:18
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(OID NVARCHAR(3),SS NVARCHAR(1),CC NVARCHAR(5))
Go
INSERT INTO tb
SELECT '001',null,null UNION ALL
SELECT '001','L','RED' UNION ALL
SELECT '001','L','RED' UNION ALL
SELECT '002','M',null UNION ALL
SELECT '003',null,'WHITE'
GOSELECT * FROM TBselect distinct *
from tb t
where not exists (select 1 from tb where oid=t.oid and isnull(ss,0)>isnull(t.ss,0) and isnull(cc,0)>isnull(t.cc,0))OID SS CC
---- ---- -----
001 L RED
002 M NULL
003 NULL WHITE(3 行受影响)