22,209
社区成员
发帖
与我相关
我的任务
分享
IF NOT OBJECT_ID('ITEMINFO') IS NULL DROP TABLE ITEMINFO
GO
CREATE TABLE ITEMINFO([ITEMBM] VARCHAR(20),[ITEMMC] NVARCHAR(5))
Insert iteminfo
SELECT '1001',N'现金' UNION ALL
SELECT '1002',N'银行存款' UNION ALL
SELECT '1211',N'原材料' UNION ALL
SELECT '1211001',N'精面粉' UNION ALL
SELECT '1211001001',N'双福精面粉'
Go
IF NOT OBJECT_ID('PZB') IS NULL DROP TABLE PZB
GO
CREATE TABLE PZB([ITEMBM] VARCHAR(50),[JFJE] INT,[DFJE] DATETIME)
Insert pzb
SELECT '1001',10,'2009-02-19' UNION ALL
SELECT '1002',10,'2009-02-19' UNION ALL
SELECT '1001',20,'2009-02-19' UNION ALL
SELECT '1211001001',20,'2009-02-19'
Go
SELECT A.ITEMBM,A.ITEMMC,JFJE=SUM(ISNULL(B.JFJE,0)) FROM ITEMINFO A
LEFT JOIN PZB B
ON B.ITEMBM LIKE A.ITEMBM+'%'
GROUP BY A.ITEMBM,A.ITEMMC
ORDER BY A.ITEMBM
/*ITEMBM ITEMMC JFJE
-------------------- ------ -----------
1001 现金 30
1002 银行存款 10
1211 原材料 20
1211001 精面粉 20
1211001001 双福精面粉 20
(所影响的行数为 5 行)
*/
create table iteminfo (itembm varchar(20), itemmc varchar(20))
insert into iteminfo values('1001' , '现金')
insert into iteminfo values('1002' , '银行存款')
insert into iteminfo values('1211' , '原材料')
insert into iteminfo values('1211001' , '精面粉')
insert into iteminfo values('1211001001', '双福精面粉')
create table pzb(itembm varchar(10), jfje int, dfje int, rq datetime)
insert into pzb values('1001' , 10 , 0 , '2009-02-19')
insert into pzb values('1002' , 0 , 10 , '2009-02-19')
insert into pzb values('1001' , 20 , 0 , '2009-02-19')
insert into pzb values('1211001001', 0 , 20 , '2009-02-19')
--方法1
select m.itembm , isnull(sum(n.jfje),0) jfje , isnull(sum(n.dfje),0) dfje
from iteminfo m left join pzb n
on n.itembm like m.itembm + '%'
group by m.itembm
--方法2
select m.itembm , isnull(sum(n.jfje),0) jfje , isnull(sum(n.dfje),0) dfje
from iteminfo m left join pzb n
on charindex(m.itembm , n.itembm) > 0
group by m.itembm
drop table iteminfo , pzb
/*
itembm jfje dfje
-------------------- ----------- -----------
1001 30 20
1002 0 10
1211 0 20
1211001 0 20
1211001001 0 20
(所影响的行数为 5 行)
*/
create table iteminfo (itembm varchar(20), itemmc varchar(20))
insert into iteminfo values('1001' , '现金')
insert into iteminfo values('1002' , '银行存款')
insert into iteminfo values('1211' , '原材料')
insert into iteminfo values('1211001' , '精面粉')
insert into iteminfo values('1211001001', '双福精面粉')
create table pzb(itembm varchar(10), jfje int, dfje int, rq datetime)
insert into pzb values('1001' , 10 , 0 , '2009-02-19')
insert into pzb values('1002' , 0 , 10 , '2009-02-19')
insert into pzb values('1001' , 20 , 0 , '2009-02-19')
insert into pzb values('1211001001', 0 , 20 , '2009-02-19')
select m.itembm , isnull(sum(n.jfje),0) jfje , isnull(sum(n.dfje),0) dfje
from iteminfo m left join pzb n
on charindex(m.itembm , n.itembm) > 0
group by m.itembm
drop table iteminfo , pzb
/*
itembm jfje dfje
-------------------- ----------- -----------
1001 30 20
1002 0 10
1211 0 20
1211001 0 20
1211001001 0 20
(所影响的行数为 5 行)
*/
create table iteminfo (itembm varchar(20), itemmc varchar(20))
insert into iteminfo values('1001' , '现金')
insert into iteminfo values('1002' , '银行存款')
insert into iteminfo values('1211' , '原材料')
insert into iteminfo values('1211001' , '精面粉')
insert into iteminfo values('1211001001', '双福精面粉')
create table pzb(itembm varchar(10), jfje int, dfje int, rq datetime)
insert into pzb values('1001' , 10 , 0 , '2009-02-19')
insert into pzb values('1002' , 0 , 10 , '2009-02-19')
insert into pzb values('1001' , 20 , 0 , '2009-02-19')
insert into pzb values('1211001001', 0 , 20 , '2009-02-19')
select m.itembm , isnull(sum(n.jfje),0) jfje , isnull(sum(n.dfje),0) dfje
from iteminfo m left join pzb n
on m.itembm like n.itembm + '%'
group by m.itembm
drop table iteminfo , pzb
/*
itembm jfje dfje
-------------------- ----------- -----------
1001 30 0
1002 0 10
1211 0 0
1211001 0 0
1211001001 0 20
(所影响的行数为 5 行)
警告: 聚合或其它 SET 操作消除了空值。
*/