select a.*,
总张数 = count(1),
总领用数 = sum(case when 是否领用 = 1 then 1 else 0 end),
结存数 = sum(case when 是否领用 = 0 then 1 else 0 end)
from pjlx a
left join pjmx b
on a.票据类型 = b.票据类型
group by a.票据类型,a.名称
总张数 = count(1),
总领用数 = sum(case when 是否领用 = 1 then 1 else 0 end),
结存数 = sum(case when 是否领用 = 0 then 1 else 0 end)
from pjlx a
left join pjmx b
on a.票据类型 = b.票据类型
group by a.票据类型,a.名称
-- Author: happyflystone
-- Date:2008-12-23 17:13:09
-------------------------------------- Test Data: PJMX
IF OBJECT_ID('PJMX') IS NOT NULL
DROP TABLE PJMX
Go
CREATE TABLE PJMX(票据类型 INT,是否领用 INT)
Go
INSERT INTO PJMX
SELECT 1001,1 UNION ALL
SELECT 1001,0 UNION ALL
SELECT 2001,0 UNION ALL
SELECT 2001,1 UNION ALL
SELECT 1001,0 UNION ALL
SELECT 2002,0 UNION ALL
SELECT 2002,1 UNION ALL
SELECT 2001,1
GO
-- Test Data: PJLX
IF OBJECT_ID('PJLX') IS NOT NULL
DROP TABLE PJLX
Go
CREATE TABLE PJLX(票据类型 INT,名称 NVARCHAR(6))
Go
INSERT INTO PJLX
SELECT 1001,'门票' UNION ALL
SELECT 2001,'餐饮定额五元' UNION ALL
SELECT 2002,'餐饮定额十元'
GO
--Start
select a.*,
总张数 = count(1),
总领用数 = sum(case when 是否领用 = 1 then 1 else 0 end),
结存数 = sum(case when 是否领用 = 0 then 1 else 0 end)
from pjlx a
left join pjmx b
on a.票据类型 = b.票据类型
group by a.票据类型,a.名称
order by 1
--Result:
/*
票据类型 名称 总张数 总领用数 结存数
----------- ------ ----------- ----------- -----------
1001 门票 3 1 2
2001 餐饮定额五元 3 2 1
2002 餐饮定额十元 2 1 1(3 行受影响)
*/
--End
INSERT @pjlx
SELECT 1001, N'门票' UNION ALL
SELECT 2001, N'餐饮定额五元' UNION ALL
SELECT 2002, N'餐饮定额十元'
DECLARE @pjmx TABLE(票据类型 INT, 是否领用 INT)
INSERT @pjmx
SELECT 1001, 1 UNION ALL
SELECT 1001, 0 UNION ALL
SELECT 2001, 0 UNION ALL
SELECT 2001, 1 UNION ALL
SELECT 1001, 0 UNION ALL
SELECT 2002, 0 UNION ALL
SELECT 2002, 1 UNION ALL
SELECT 2001, 1SELECT A.票据类型, 名称,COUNT(*) AS 总张数,SUM(CASE WHEN 是否领用=1 THEN 1 ELSE 0 END) AS 总领用数,SUM(CASE WHEN 是否领用=0 THEN 1 ELSE 0 END) AS 结存数
FROM @pjlx AS A JOIN @pjmx AS B ON A.票据类型=B.票据类型
GROUP BY A.票据类型,名称
ORDER BY A.票据类型
/*
票据类型 名称 总张数 总领用数 结存数
----------- ---------- ----------- ----------- -----------
1001 门票 3 1 2
2001 餐饮定额五元 3 2 1
2002 餐饮定额十元 2 1 1
*/
总张数 = count(1),
总领用数 = sum(case when 是否领用 = 1 then 1 else 0 end),
结存数 = sum(case when 是否领用 = 0 then 1 else 0 end)
from pjlx a
left join pjmx b
on a.票据类型 = b.票据类型
group by a.票据类型,a.名称
order by a.票据类型
INSERT INTO PJMX
SELECT 1001,1 UNION ALL
SELECT 1001,0 UNION ALL
SELECT 2001,0 UNION ALL
SELECT 2001,1 UNION ALL
SELECT 1001,0 UNION ALL
SELECT 2002,0 UNION ALL
SELECT 2002,1 UNION ALL
SELECT 2001,1
GO
CREATE TABLE PJLX(票据类型 INT,名称 NVARCHAR(6))
Go
INSERT INTO PJLX
SELECT 1001,'门票' UNION ALL
SELECT 2001,'餐饮定额五元' UNION ALL
SELECT 2002,'餐饮定额十元'
GOselect m.* ,
总张数 = (select count(*) from pjmx n where n.票据类型 = m.票据类型),
总领用数 = (select count(*) from pjmx n where n.票据类型 = m.票据类型 and n.是否领用 = 1),
结存数 = (select count(*) from pjmx n where n.票据类型 = m.票据类型 and n.是否领用 = 0)
from pjlx m drop table pjlx , pjmx/*
票据类型 名称 总张数 总领用数 结存数
----------- ------ ----------- ----------- -----------
1001 门票 3 1 2
2001 餐饮定额五元 3 2 1
2002 餐饮定额十元 2 1 1(所影响的行数为 3 行)*/
from
(select #T1.*,#T2.是否领用 from #T1,#T2 where #T1.票据类型=#T2.票据类型) B
group by 票据类型,名称