category
cid cname
1 a
2 bproducts
pid cid pname passed
1 1 a_A 0
2 1 a_B 1
3 1 a_C 1现在要求合并一个视图g_cid g_cname g_pcount g_passed
1 a 3 2
2 b 0 0slq的语句怎么写,帮帮忙啊
cid cname
1 a
2 bproducts
pid cid pname passed
1 1 a_A 0
2 1 a_B 1
3 1 a_C 1现在要求合并一个视图g_cid g_cname g_pcount g_passed
1 a 3 2
2 b 0 0slq的语句怎么写,帮帮忙啊
from category a
left join
(select cid,count(1) as cnt,sum(passed) as pas from products group by cid) as b
on a.cid=b.cid
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-12 01:35:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[category]
if object_id('[category]') is not null drop table [category]
go
create table [category]([cid] int,[cname] varchar(1))
insert [category]
select 1,'a' union all
select 2,'b'
--> 测试数据:[products]
if object_id('[products]') is not null drop table [products]
go
create table [products]([pid] int,[cid] int,[pname] varchar(3),[passed] int)
insert [products]
select 1,1,'a_A',0 union all
select 2,1,'a_B',1 union all
select 3,1,'a_C',1
--------------开始查询--------------------------
select
a.cid as g_cid,a.cname as g_cname,isnull(b.g_pcount,0) as g_pcount,isnull(b.g_passed,0) as g_passed
from
[category] a
left join
(select cid,count(pname) as g_pcount,sum(case passed when 1 then 1 else 0 end) as g_passed from [products] group by cid)b
on
a.cid=b.cid
----------------结果----------------------------
/* g_cid g_cname g_pcount g_passed
----------- ------- ----------- -----------
1 a 3 2
2 b 0 0(2 行受影响)
*/