表连接统计的有关问题
表连接统计的问题
A表:
ID Name Setss
-------------------------------
1 张三 001
2 张三 002
3 李四 003
4 王五 004
B表:
Setss Value
-----------------------
001 aaa
001 bbb
001 ccc
002 aaa
003 aaa
003 bbb
004 aaa
004 ccc
------------------------------------------------------------要求得到如下数据格式
Name count aaa bbb ccc
张三 2 2 1 1
李四 1 1 1 0
王五 1 1 0 1
--------------------------------------------------------------------------------------------------------
测试SQL:
----------------------------------------------------
CREATE TABLE [dbo].[A](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[setss] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[B](
[setss] [int] NULL,
[value] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into A (NAME,SETSS)values('张三','001')
insert into A (NAME,SETSS)values('张三','002')
insert into A (NAME,SETSS)values('李四','003')
insert into A (NAME,SETSS)values('王五','004')
GO
insert into B (SETSS,Value)values('001','aaa')
insert into B (SETSS,Value)values('001','bbb')
insert into B (SETSS,Value)values('001','ccc')
insert into B (SETSS,Value)values('002','aaa')
insert into B (SETSS,Value)values('003','aaa')
insert into B (SETSS,Value)values('003','bbb')
insert into B (SETSS,Value)values('004','aaa')
insert into B (SETSS,Value)values('004','ccc')
GO
-------------------------------------------------------------------------------------------------------------------------------------
求结果啊。。。。。各位大能速速来显神通
------解决方案--------------------
A表:
ID Name Setss
-------------------------------
1 张三 001
2 张三 002
3 李四 003
4 王五 004
B表:
Setss Value
-----------------------
001 aaa
001 bbb
001 ccc
002 aaa
003 aaa
003 bbb
004 aaa
004 ccc
------------------------------------------------------------要求得到如下数据格式
Name count aaa bbb ccc
张三 2 2 1 1
李四 1 1 1 0
王五 1 1 0 1
--------------------------------------------------------------------------------------------------------
测试SQL:
----------------------------------------------------
CREATE TABLE [dbo].[A](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[setss] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[B](
[setss] [int] NULL,
[value] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into A (NAME,SETSS)values('张三','001')
insert into A (NAME,SETSS)values('张三','002')
insert into A (NAME,SETSS)values('李四','003')
insert into A (NAME,SETSS)values('王五','004')
GO
insert into B (SETSS,Value)values('001','aaa')
insert into B (SETSS,Value)values('001','bbb')
insert into B (SETSS,Value)values('001','ccc')
insert into B (SETSS,Value)values('002','aaa')
insert into B (SETSS,Value)values('003','aaa')
insert into B (SETSS,Value)values('003','bbb')
insert into B (SETSS,Value)values('004','aaa')
insert into B (SETSS,Value)values('004','ccc')
GO
-------------------------------------------------------------------------------------------------------------------------------------
求结果啊。。。。。各位大能速速来显神通
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-04-03 10:44:25
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[Name] varchar(4),[Setss] varchar(3))
insert [A]
select 1,'张三','001' union all
select 2,'张三','002' union all
select 3,'李四','003' union all
select 4,'王五','004'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Setss] varchar(3),[Value] varchar(3))
insert [B]
select '001','aaa' union all
select '001','bbb' union all
select '001','ccc' union all
select '002','aaa' union all
select '003','aaa' union all
select '003','bbb' union all
select '004','aaa' union all
select '004','ccc'
--------------开始查询--------------------------
SELECT
a.name,b.COUNT,a.aaa,a.bbb,a.ccc
FROM
(
SELECT
a.Name,
SUM(CASE WHEN b.Value='aaa' THEN 1 ELSE 0 END) AS 'aaa',
SUM(CASE WHEN b.Value='bbb' THEN 1 ELSE 0 END) AS 'bbb',
SUM(CASE WHEN b.Value='ccc' THEN 1 ELSE 0 END) AS 'ccc'
FROM
a INNER JOIN b ON a.Setss=b.Setss
GROUP BY
a.name )a
INNER JOIN
(SELECT DISTINCT b.* FROM dbo.A
INNER JOIN