表记录
类型 A名称 A值 B名称 B值 C名称 C值
1 1 11 0 0 0 0
1 2 5 0 0 0 0
1 3 6 0 0 0 0
1 0 0 1 40 0 0
1 0 0 2 51 0 0
1 0 0 0 0 1 100
2 21 3 0 0 0 0
2 0 0 1 30 0 0
2 0 0 0 0 5 10要求返回结果
类型 A名称 A值 B名称 B值 C名称 C值
1 1 11 1 40 1 100
1 2 5 2 51 0 0
1 3 6 0 0 0 0
2 21 3 1 30 5 10就是按照类型,把A值,B值,C值中为0的记录删除掉,并成最少的记录数返回,不知道这样描述清楚不。
类型 A名称 A值 B名称 B值 C名称 C值
1 1 11 0 0 0 0
1 2 5 0 0 0 0
1 3 6 0 0 0 0
1 0 0 1 40 0 0
1 0 0 2 51 0 0
1 0 0 0 0 1 100
2 21 3 0 0 0 0
2 0 0 1 30 0 0
2 0 0 0 0 5 10要求返回结果
类型 A名称 A值 B名称 B值 C名称 C值
1 1 11 1 40 1 100
1 2 5 2 51 0 0
1 3 6 0 0 0 0
2 21 3 1 30 5 10就是按照类型,把A值,B值,C值中为0的记录删除掉,并成最少的记录数返回,不知道这样描述清楚不。
--没有测试环境,随手写的。。
select isnull(D1.类型,D2类型) as 类型,isnull(A名称,0) as A名称,isnull(A值,0) as A值,isnull(B名称,0) as B名称,isnull(B值,0) as B值,isnull(C名称,0) as C名称,isnull(C值,0) as C值
from
(
select isnull(X1.id,X2.id) .as id, isnull(X1.类型,X2.类型) as 类型,isnull(A名称,0) as A名称,isnull(A值,0) as A值,isnull(B名称,0) as B名称,isnull(B值,0) as B值
from
(
select id=(select count(*) from 表 a where a.类型=表.类型 and a.A值<>0 and a.A名称<=表.A名称),类型,A名称,A值
from 表
where A值<>0
) X1
full join
(
select id=(select count(*) from 表 b where b.类型=表.类型 and b.B值<>0 and b.B名称<=表.B名称),类型,B名称,B值
from 表
where B值<>0
) X2
on X1.id=X2.id and X1.类型=X2.类型
) D1
full join
(
select id=(select count(*) from 表 c where c.类型=表.类型 and c.C值<>0 and c.C名称<=表.C名称),类型,A名称,A值
from 表
where A值<>0
) D2
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-14 22:33:36
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(类型 INT,A名称 INT,A值 INT,B名称 NVARCHAR(1),B值 NVARCHAR(2),C名称 NVARCHAR(1),C值 NVARCHAR(3))
Go
INSERT INTO tb
SELECT 1,1,11,'0','0','0','0' UNION ALL
SELECT 1,2,5,'0','0','0','0' UNION ALL
SELECT 1,3,6,'0','0','0','0' UNION ALL
SELECT 1,0,0,'1','40','0','0' UNION ALL
SELECT 1,0,0,'2','51','0','0' UNION ALL
SELECT 1,0,0,'0','0','1','100' UNION ALL
SELECT 2,21,3,'0','0','0','0' UNION ALL
SELECT 2,0,0,'1','30','0','0' UNION ALL
SELECT 2,0,0,'0','0','5','10'
GOSELECT * FROM TB
with
wang1 as(select row=row_number() over (partition by 类型 order by 类型),类型 ,A名称, A值 from tb where A名称<>0 and A值<>0),
wang2 as (select row=row_number() over (partition by 类型 order by 类型),类型 ,b名称, b值 from tb where b名称<>0 and b值<>0),
wang3 as (select row=row_number() over (partition by 类型 order by 类型),类型 ,c名称, c值 from tb where c名称<>0 and c值<>0)select wang1.类型,A名称, A值,isnull(b名称,0),isnull(b值,0) ,isnull(c名称,0),isnull(c值,0)
from wang1 left join wang2 on wang1.类型=wang2.类型 and wang1.row=wang2.row
left join wang3 on wang1.类型=wang3.类型 and wang1.row=wang3.row类型 A名称 A值 (无列名) (无列名) (无列名) (无列名)
1 1 11 1 40 1 100
1 2 5 2 51 0 0
1 3 6 0 0 0 0
2 21 3 1 30 5 10
playwarcraft 的我先看一下,先谢了。
--昨天那個沒有測試的,有手誤,用下面這個試試CREATE TABLE 表(类型 INT,A名称 INT,A值 INT,B名称 NVARCHAR(1),B值 NVARCHAR(2),C名称 NVARCHAR(1),C值 NVARCHAR(3))
Go
INSERT INTO 表
SELECT 1,1,11,'0','0','0','0' UNION ALL
SELECT 1,2,5,'0','0','0','0' UNION ALL
SELECT 1,3,6,'0','0','0','0' UNION ALL
SELECT 1,0,0,'1','40','0','0' UNION ALL
SELECT 1,0,0,'2','51','0','0' UNION ALL
SELECT 1,0,0,'0','0','1','100' UNION ALL
SELECT 2,21,3,'0','0','0','0' UNION ALL
SELECT 2,0,0,'1','30','0','0' UNION ALL
SELECT 2,0,0,'0','0','5','10'
GO
select isnull(D1.类型,D2.类型) as 类型,isnull(A名称,0) as A名称,isnull(A值,0) as A值,isnull(B名称,0) as B名称,isnull(B值,0) as B值,isnull(C名称,0) as C名称,isnull(C值,0) as C值
from
(
select isnull(X1.id,X2.id) as id, isnull(X1.类型,X2.类型) as 类型,isnull(A名称,0) as A名称,isnull(A值,0) as A值,isnull(B名称,0) as B名称,isnull(B值,0) as B值
from
(
select id=(select count(*) from 表 a where a.类型=表.类型 and a.A值<>0 and a.A名称<=表.A名称),类型,A名称,A值
from 表
where A值<>0
) X1
full join
(
select id=(select count(*) from 表 b where b.类型=表.类型 and b.B值<>0 and b.B名称<=表.B名称),类型,B名称,B值
from 表
where B值<>0
) X2
on X1.id=X2.id and X1.类型=X2.类型
) D1
full join
(
select id=(select count(*) from 表 c where c.类型=表.类型 and c.C值<>0 and c.C名称<=表.C名称),类型,C名称,C值
from 表
where C值<>0
) D2
on D1.id=D2.id and D1.类型=D2.类型/*
类型 A名称 A值 B名称 B值 C名称 C值
----------- ----------- ----------- ---- ---- ---- ----
1 1 11 1 40 1 100
1 2 5 2 51 0 0
1 3 6 0 0 0 0
2 21 3 1 30 5 10
*/drop table 表
你可以把每一个wang1,wang2,wang3 放到一个临时表中
row_number() 换成identity(int,1,1)
然后在使用下面的连接