--> 测试数据:#TabZL IF OBJECT_ID('tempdb.dbo.#TabZL') IS NOT NULL DROP TABLE #TabZL GO CREATE TABLE #TabZL([kjnd] INT,[fzdm] INT,[fzmc] VARCHAR(9)) INSERT #TabZL SELECT 2013,201,'mc201' UNION ALL SELECT 2013,20101,'mc20101' UNION ALL SELECT 2013,2010101,'mc2010101' UNION ALL SELECT 2013,20102,'mc20102' UNION ALL SELECT 2013,2010201,'mc2010201' UNION ALL SELECT 2013,2010202,'mc2010202' UNION ALL SELECT 2013,202,'mc202' UNION ALL SELECT 2013,301,'mc301' UNION ALL SELECT 2013,30101,'mc30101' UNION ALL SELECT 2013,30102,'mc30102' UNION ALL SELECT 2013,302,'mc302' UNION ALL SELECT 2013,30201,'mc30201' UNION ALL SELECT 2013,30202,'mc30202'--> 测试数据:#TabSJ IF OBJECT_ID('tempdb.dbo.#TabSJ') IS NOT NULL DROP TABLE #TabSJ GO CREATE TABLE #TabSJ([kjnd] INT,[kmdm] VARCHAR(4),[fzdm4] INT,[fzdm5] INT) INSERT #TabSJ SELECT 2013,'A001',2010101,30101 UNION ALL SELECT 2013,'A001',2010101,30102 UNION ALL SELECT 2013,'A001',2010102,30101 UNION ALL SELECT 2013,'A001',2010101,30202 --------------开始查询--------------------------SELECT *,sfmx=CASE WHEN c.[fzmc] IS NULL THEN 0 ELSE 1 END FROM ( SELECT * FROM #TabZL WHERE LEFT([fzdm],1)=2 ) a LEFT JOIN #TabSJ b ON a.[kjnd]=b.[kjnd] AND [fzdm]=b.[fzdm4] LEFT JOIN ( SELECT * FROM #TabZL WHERE LEFT([fzdm],1)=3 )c ON b.[fzdm5]=c.[fzdm]----------------结果---------------------------- /* kjnd fzdm fzmc kjnd kmdm fzdm4 fzdm5 kjnd fzdm fzmc sfmx 2013 201 mc201 NULL NULL NULL NULL NULL NULL NULL 0 2013 20101 mc20101 NULL NULL NULL NULL NULL NULL NULL 0 2013 2010101 mc2010101 2013 A001 2010101 30101 2013 30101 mc30101 1 2013 2010101 mc2010101 2013 A001 2010101 30102 2013 30102 mc30102 1 2013 2010101 mc2010101 2013 A001 2010101 30202 2013 30202 mc30202 1 2013 20102 mc20102 NULL NULL NULL NULL NULL NULL NULL 0 2013 2010201 mc2010201 NULL NULL NULL NULL NULL NULL NULL 0 2013 2010202 mc2010202 NULL NULL NULL NULL NULL NULL NULL 0 2013 202 mc202 NULL NULL NULL NULL NULL NULL NULL 0 */
select 2013 kjnd,'201' fzdm,'mc201' fzmc into #TabZL union all select 2013,'20101' ,'mc20101' union all select 2013,'2010101' ,'mc2010101' union all select 2013,'20102' ,'mc20102' union all select 2013,'2010201' ,'mc2010201' union all select 2013,'2010202' ,'mc2010202' union all select 2013,'202' ,'mc202' union all select 2013,'301' ,'mc301' union all select 2013,'30101' ,'mc30101' union all select 2013,'30102' ,'mc30102' union all select 2013,'302' ,'mc302' union all select 2013,'30201' ,'mc30201' union all select 2013,'30202' ,'mc30202'
select 2013 kjnd,'A001' kmdm,'2010101' fzdm4,'30101' fzdm5 into #TabSJ union all select 2013,'A001','2010101','30102' union all select 2013,'A001','2010102','30101' union all select 2013,'A001','2010101','30202';with t as ( select a.kjnd,a.fzdm as fzdm4 ,a.fzmc as fzdm4mc ,b.fzdm as fzdm5 ,b.fzmc as fzdm5mc from #TabZL a,#TabZL b where a.fzdm like '2%' and b.fzdm like '3%' ) select t.*,case when j.kjnd is not null then 1 else 0 end as sfmx from t left join #TabSJ j on t.fzdm4=j.fzdm4 and t.fzdm5=j.fzdm5这样?
IF OBJECT_ID('tempdb.dbo.#TabZL') IS NOT NULL DROP TABLE #TabZL
GO
CREATE TABLE #TabZL([kjnd] INT,[fzdm] INT,[fzmc] VARCHAR(9))
INSERT #TabZL
SELECT 2013,201,'mc201' UNION ALL
SELECT 2013,20101,'mc20101' UNION ALL
SELECT 2013,2010101,'mc2010101' UNION ALL
SELECT 2013,20102,'mc20102' UNION ALL
SELECT 2013,2010201,'mc2010201' UNION ALL
SELECT 2013,2010202,'mc2010202' UNION ALL
SELECT 2013,202,'mc202' UNION ALL
SELECT 2013,301,'mc301' UNION ALL
SELECT 2013,30101,'mc30101' UNION ALL
SELECT 2013,30102,'mc30102' UNION ALL
SELECT 2013,302,'mc302' UNION ALL
SELECT 2013,30201,'mc30201' UNION ALL
SELECT 2013,30202,'mc30202'--> 测试数据:#TabSJ
IF OBJECT_ID('tempdb.dbo.#TabSJ') IS NOT NULL DROP TABLE #TabSJ
GO
CREATE TABLE #TabSJ([kjnd] INT,[kmdm] VARCHAR(4),[fzdm4] INT,[fzdm5] INT)
INSERT #TabSJ
SELECT 2013,'A001',2010101,30101 UNION ALL
SELECT 2013,'A001',2010101,30102 UNION ALL
SELECT 2013,'A001',2010102,30101 UNION ALL
SELECT 2013,'A001',2010101,30202
--------------开始查询--------------------------SELECT *,sfmx=CASE WHEN c.[fzmc] IS NULL THEN 0 ELSE 1 END FROM
(
SELECT * FROM #TabZL WHERE LEFT([fzdm],1)=2
) a
LEFT JOIN #TabSJ b
ON a.[kjnd]=b.[kjnd] AND [fzdm]=b.[fzdm4]
LEFT JOIN
(
SELECT * FROM #TabZL WHERE LEFT([fzdm],1)=3
)c
ON b.[fzdm5]=c.[fzdm]----------------结果----------------------------
/*
kjnd fzdm fzmc kjnd kmdm fzdm4 fzdm5 kjnd fzdm fzmc sfmx
2013 201 mc201 NULL NULL NULL NULL NULL NULL NULL 0
2013 20101 mc20101 NULL NULL NULL NULL NULL NULL NULL 0
2013 2010101 mc2010101 2013 A001 2010101 30101 2013 30101 mc30101 1
2013 2010101 mc2010101 2013 A001 2010101 30102 2013 30102 mc30102 1
2013 2010101 mc2010101 2013 A001 2010101 30202 2013 30202 mc30202 1
2013 20102 mc20102 NULL NULL NULL NULL NULL NULL NULL 0
2013 2010201 mc2010201 NULL NULL NULL NULL NULL NULL NULL 0
2013 2010202 mc2010202 NULL NULL NULL NULL NULL NULL NULL 0
2013 202 mc202 NULL NULL NULL NULL NULL NULL NULL 0
*/
select 2013 kjnd,'201' fzdm,'mc201' fzmc
into #TabZL
union all select 2013,'20101' ,'mc20101'
union all select 2013,'2010101' ,'mc2010101'
union all select 2013,'20102' ,'mc20102'
union all select 2013,'2010201' ,'mc2010201'
union all select 2013,'2010202' ,'mc2010202'
union all select 2013,'202' ,'mc202'
union all select 2013,'301' ,'mc301'
union all select 2013,'30101' ,'mc30101'
union all select 2013,'30102' ,'mc30102'
union all select 2013,'302' ,'mc302'
union all select 2013,'30201' ,'mc30201'
union all select 2013,'30202' ,'mc30202'
select 2013 kjnd,'A001' kmdm,'2010101' fzdm4,'30101' fzdm5
into #TabSJ
union all select 2013,'A001','2010101','30102'
union all select 2013,'A001','2010102','30101'
union all select 2013,'A001','2010101','30202';with t as
(
select a.kjnd,a.fzdm as fzdm4
,a.fzmc as fzdm4mc
,b.fzdm as fzdm5
,b.fzmc as fzdm5mc
from #TabZL a,#TabZL b
where a.fzdm like '2%'
and b.fzdm like '3%'
)
select t.*,case when j.kjnd is not null then 1 else 0 end as sfmx
from t
left join #TabSJ j
on t.fzdm4=j.fzdm4
and t.fzdm5=j.fzdm5这样?
不对吧
请问你的
2013 2010101 mc2010101 301 mc301 0
这一行记录是怎么得到的?
不对吧
请问你的
2013 2010101 mc2010101 301 mc301 0
这一行记录是怎么得到的?是根据业务表TabSJ的这条记录来的
2013 A001 2010101 30101业务表记录的fzdm4,fzdm5等都是在基础表的最明细级;
想得到结果是按照fzdm4+fzdm5的形式上下级汇总
如:业务表有fzdm4值:2080506,fzdm5值:30810
希望得到的是:
fzdm4 fzdm4mc fzdm5 fzdm5mc ...
208 mc208
20805 mc20805
2080506 mc2080506 308 mc308
2080506 mc2080506 30810 mc30810fzdm4的基础表级次是3-2-2
fzdm5的基础表级次是3-2
所以fzdm4与fzdm5的基础数据是放在同一基础表的。
不对吧
请问你的
2013 2010101 mc2010101 301 mc301 0
这一行记录是怎么得到的?是根据业务表TabSJ的这条记录来的
2013 A001 2010101 30101业务表记录的fzdm4,fzdm5等都是在基础表的最明细级;
想得到结果是按照fzdm4+fzdm5的形式上下级汇总
如:业务表有fzdm4值:2080506,fzdm5值:30810
希望得到的是:
fzdm4 fzdm4mc fzdm5 fzdm5mc ...
208 mc208
20805 mc20805
2080506 mc2080506 308 mc308
2080506 mc2080506 30810 mc30810fzdm4的基础表级次是3-2-2
fzdm5的基础表级次是3-2
所以fzdm4与fzdm5的基础数据是放在同一基础表的。
业务表有fzdm4值:2080506,fzdm5值:30810 的时候
为何没有
fzdm4 fzdm4mc fzdm5 fzdm5mc
208 mc208 308 mc308
这行呢
不对吧
请问你的
2013 2010101 mc2010101 301 mc301 0
这一行记录是怎么得到的?是根据业务表TabSJ的这条记录来的
2013 A001 2010101 30101业务表记录的fzdm4,fzdm5等都是在基础表的最明细级;
想得到结果是按照fzdm4+fzdm5的形式上下级汇总
如:业务表有fzdm4值:2080506,fzdm5值:30810
希望得到的是:
fzdm4 fzdm4mc fzdm5 fzdm5mc ...
208 mc208
20805 mc20805
2080506 mc2080506 308 mc308
2080506 mc2080506 30810 mc30810fzdm4的基础表级次是3-2-2
fzdm5的基础表级次是3-2
所以fzdm4与fzdm5的基础数据是放在同一基础表的。
业务表有fzdm4值:2080506,fzdm5值:30810 的时候
为何没有
fzdm4 fzdm4mc fzdm5 fzdm5mc
208 mc208 308 mc308
这行呢因为业务表只是记录基础表的明细记录值,
208 mc208 308 mc308
这些都不是明细数据值,所以在业务表不体现。
谢谢你啦,我已经自己想办法写得差不多了,在测试。