34,571
社区成员
发帖
与我相关
我的任务
分享
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([编号] int,[名称] varchar(4))
insert [T1]
select 10,'家电' union all
select 20,'办公'
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([编号] int,[名称] varchar(4),[所属大类] int)
insert [T2]
select 1001,'桌类',10 union all
select 1002,'电脑',20
if object_id('[T3]') is not null drop table [T3]
go
create table [T3]([编号] varchar(1),[名称] varchar(3),[所属] int)
insert [T3]
select 'A','XXX',10 union all
select 'B','YYY',1001 union all
select 'C','ZZZ',1002
select t1.编号,t1.名称,t2.cnt as 产品数
from t1
left join
(select t2.所属大类,count(1) as cnt
from t2,t3
where t2.编号=t3.所属 or t2.所属大类=t3.所属
group by t2.所属大类
) t2
on t1.编号=t2.所属大类
/**
编号 名称 产品数
----------- ---- -----------
10 家电 2
20 办公 1
(2 行受影响)
**/
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-22 15:23:49
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:t1
IF NOT OBJECT_ID('[t1]') IS NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1]([编号] INT,[名称] NVARCHAR(10))
INSERT [t1]
SELECT 10,N'家电' UNION ALL
SELECT 20,N'办公'
GO
--SELECT * FROM [t1]
--> 生成测试数据表:T2
IF NOT OBJECT_ID('[T2]') IS NULL
DROP TABLE [T2]
GO
CREATE TABLE [T2]([编号] INT,[名称] NVARCHAR(10),[所属大类] INT)
INSERT [T2]
SELECT 1001,N'桌类',10 UNION ALL
SELECT 1002,N'电脑',20
GO
--SELECT * FROM [T2]
--> 生成测试数据表:T3
IF NOT OBJECT_ID('[T3]') IS NULL
DROP TABLE [T3]
GO
CREATE TABLE [T3]([编号] NVARCHAR(10),[名称] NVARCHAR(10),[所属] INT)
INSERT [T3]
SELECT 'A','XXX',10 UNION ALL
SELECT 'B','YYY',1001 UNION ALL
SELECT 'C','ZZZ',1002
GO
--SELECT * FROM [T3]
-->SQL查询如下:
select a.*,count(1) 产品数
from t1 a
join t2 b on a.编号=b.所属大类
join t3 c on c.所属=b.编号 or c.所属=b.所属大类
group by a.编号,a.名称
/*
编号 名称 产品数
----------- ---------- -----------
10 家电 2
20 办公 1
(2 行受影响)
*/
select a.*,count(1) 产品数
from t1 a
join t2 b on a.编号=b.所属大类
join t3 c on c.所属=b.编号 or c.所属=b.所属大类
group by a.编号,a.名称
select t1.编号,t1.名称,t2.cnt as 产品数
from t1
left join
(select t2.所属大类,count(1) as cnt
from t2,t3
where t2.编号=t3.所属 or t2.所属大类=t2.所属
group by t2.所属大类
) t2
on t1.编号=t2.所属大类