各位高手请教你们一个问题,请各位高手帮帮忙,谢谢!
以下有两张表:
A表如下:
Member_code Name Contact_no_home
1 张三 025-57898785
2 王五 021-58965658
3 小孙 021-89875542
4 李子 028-86589851
B表如下: Id Member_code VisRes VisNum
1 1 BC 1
2 1 CS 2
3 2 D1 1
4 1 F1 3
5 3 VF 1
6 2 CS 2我想得到以下结果
Member_code Name Contact_no_home VisNum VisRes VisNum VisRes VisNum VisRes
1 张三 025-57898785 1 BC 2 CS 3 F1
2 王五 021-58965658 1 D1 2 CS 0 0
3 小孙 028-86589851 1 VF 0 0 0 0
可能比较难,这个是我想要的结果,不知道各位大侠帮帮忙! 我的QQ是:310212206 拜托大家!
以下有两张表:
A表如下:
Member_code Name Contact_no_home
1 张三 025-57898785
2 王五 021-58965658
3 小孙 021-89875542
4 李子 028-86589851
B表如下: Id Member_code VisRes VisNum
1 1 BC 1
2 1 CS 2
3 2 D1 1
4 1 F1 3
5 3 VF 1
6 2 CS 2我想得到以下结果
Member_code Name Contact_no_home VisNum VisRes VisNum VisRes VisNum VisRes
1 张三 025-57898785 1 BC 2 CS 3 F1
2 王五 021-58965658 1 D1 2 CS 0 0
3 小孙 028-86589851 1 VF 0 0 0 0
可能比较难,这个是我想要的结果,不知道各位大侠帮帮忙! 我的QQ是:310212206 拜托大家!
a.Member_code,a.Name,a.Contact_no_home,
max(case b.Member_code when 1 then b.VisNum else 0 end) as VisNum,
max(case b.Member_code when 1 then b.VisRes else '' end) as VisRes,
max(case b.Member_code when 2 then b.VisNum else 0 end) as VisNum,
max(case b.Member_code when 2 then b.VisRes else '' end) as VisRes,
max(case b.Member_code when 3 then b.VisNum else 0 end) as VisNum,
max(case b.Member_code when 3 then b.VisRes else '' end) as VisRes
from
a join b
on
a.Member_code=b.Member_code
group by
a.Member_code,a.Name,a.Contact_no_home
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-28 10:12:13
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([Member_code] int,[Name] varchar(4),[Contact_no_home] varchar(12))
insert [a]
select 1,'张三','025-57898785' union all
select 2,'王五','021-58965658' union all
select 3,'小孙','021-89875542' union all
select 4,'李子','028-86589851'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Id] int,[Member_code] int,[VisRes] varchar(2),[VisNum] int)
insert [B]
select 1,1,'BC',1 union all
select 2,1,'CS',2 union all
select 3,2,'D1',1 union all
select 4,1,'F1',3 union all
select 5,3,'VF',1 union all
select 6,2,'CS',2
--------------开始查询--------------------------
select
a.Member_code,a.Name,a.Contact_no_home,
max(case id0 when 1 then b.VisNum else 0 end) as VisNum,
max(case id0 when 1 then b.VisRes else '' end) as VisRes,
max(case id0 when 2 then b.VisNum else 0 end) as VisNum,
max(case id0 when 2 then b.VisRes else '' end) as VisRes,
max(case id0 when 3 then b.VisNum else 0 end) as VisNum,
max(case id0 when 3 then b.VisRes else '' end) as VisRes
from
a
join
(select id0=row_number()over(partition by Member_code order by getdate()),* from b)b
on
a.Member_code=b.Member_code
group by
a.Member_code,a.Name,a.Contact_no_home
----------------结果----------------------------
/* Member_code Name Contact_no_home VisNum VisRes VisNum VisRes VisNum VisRes
----------- ---- --------------- ----------- ------ ----------- ------ ----------- ------
1 张三 025-57898785 1 BC 2 CS 3 F1
2 王五 021-58965658 1 D1 2 CS 0
3 小孙 021-89875542 1 VF 0 0 (3 行受影响)
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([Member_code] int,[Name] varchar(4),[Contact_no_home] varchar(12))
insert [A]
select 1,'张三','025-57898785' union all
select 2,'王五','021-58965658' union all
select 3,'小孙','021-89875542' union all
select 4,'李子','028-86589851'
if object_id('[B]') is not null drop table [B]
go
create table [B]([Id] int,[Member_code] int,[VisRes] varchar(2),[VisNum] int)
insert [B]
select 1,1,'BC',1 union all
select 2,1,'CS',2 union all
select 3,2,'D1',1 union all
select 4,1,'F1',3 union all
select 5,3,'VF',1 union all
select 6,2,'CS',2
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when px='+ltrim(px)+' then VisNum else 0 end) as [VisNum'+ltrim(px)+'],'
+'max(case when px='+ltrim(px)+' then VisRes else ''0'' end) as [VisRes'+ltrim(px)+']'
from
(select distinct (select count(1)+1from b where member_code=t.member_code and id<t.id) as px from b t) ttset
@sql='select a.Member_code,a.Name,a.Contact_no_home,'
+@sql
+' from a,'
+'(select *,px=(select count(1) from b t where t.member_code=b.member_code and t.id<b.id) from b)b '
+'where a.member_code=b.member_code group by a.Member_code,a.Name,a.Contact_no_home'exec (@sql)---结果---
Member_code Name Contact_no_home VisNum1 VisRes1 VisNum2 VisRes2 VisNum3 VisRes3
----------- ---- --------------- ----------- ------- ----------- ------- ----------- -------
1 张三 025-57898785 2 CS 3 F1 0 0
2 王五 021-58965658 2 CS 0 0 0 0
3 小孙 021-89875542 0 0 0 0 0 0(3 行受影响)
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when px='+ltrim(px)+' then VisNum else 0 end) as [VisNum'+ltrim(px)+'],'
+'max(case when px='+ltrim(px)+' then VisRes else ''0'' end) as [VisRes'+ltrim(px)+']'
from
(select distinct (select count(1)+1from b where member_code=t.member_code and id<t.id) as px from b t) ttset
@sql='select a.Member_code,a.Name,a.Contact_no_home,'
+@sql
+' from a,'
+'(select *,px=(select count(1)+1 from b t where t.member_code=b.member_code and t.id<b.id) from b)b '
+'where a.member_code=b.member_code group by a.Member_code,a.Name,a.Contact_no_home'exec (@sql)---结果---
Member_code Name Contact_no_home VisNum1 VisRes1 VisNum2 VisRes2 VisNum3 VisRes3
----------- ---- --------------- ----------- ------- ----------- ------- ----------- -------
1 张三 025-57898785 1 BC 2 CS 3 F1
2 王五 021-58965658 1 D1 2 CS 0 0
3 小孙 021-89875542 1 VF 0 0 0 0(3 行受影响)
drop table tb_A
create table tb_A(Member_code int, Name varchar(10),Contact_no_home varchar(100))
insert into tb_A
select 1 ,'张三' ,'025-57898785' union all
select 2 ,'王五', '021-58965658' union all
select 3 ,'小孙', '021-89875542' union all
select 4 ,'李子', '028-86589851'
B表如下:create table tb_B(Id int, Member_code int, VisRes varchar(10),VisNum int)
insert into tb_B
select 1 ,1 ,'BC' ,1 union all
select 2, 1, 'CS' ,2union all
select 3, 2, 'D1', 1union all
select 4, 1, 'F1' ,3union all
select 5, 3, 'VF' ,1union all
select 6, 2 ,'CS' ,2select tb_A.Member_code,tb_A.Name,tb_A.Contact_no_home,
MAX(case when tb_B.VisNum = 1 then tb_B.VisNum else '0' end) as VisNum1,
MAX(case when tb_B.VisNum = 1 then tb_B.VisRes else '0' end) as VisRes1,
MAX(case when tb_B.VisNum = 2 then tb_B.VisNum else '0' end) as VisNum2,
MAX(case when tb_B.VisNum = 2 then tb_B.VisRes else '0' end) as VisRes2,
MAX(case when tb_B.VisNum = 3 then tb_B.VisNum else '0' end) as VisNum3,
MAX(case when tb_B.VisNum = 3 then tb_B.VisRes else '0' end) as VisRes3
from tb_A inner join tb_B on tb_A.Member_code=tb_B.Member_code
group by tb_A.Member_code,tb_A.Name,tb_A.Contact_no_home
select
A.member_code, A.Name, A.Contact_no_home,
coalesce((
select
vis_num
from
B
where
member_code=A.member_code and
vis_num=1
),0),
coalesce((
select
vis_res
from
B
where
member_code=A.member_code and
vis_num=1
),'0'),
coalesce((
select
vis_num
from
B
where
member_code=A.member_code and
vis_num=2
),0),
coalesce((
select
vis_res
from
B
where
member_code=A.member_code and
vis_num=2
),'0'),
coalesce((
select
vis_num
from
B
where
member_code=A.member_code and
vis_num=3
),0),
coalesce((
select
vis_res
from
B
where
member_code=A.member_code and
vis_num=3
),'0')
from
A
if object_id('[A]') is not null drop table [A]
create table [A]([Member_code] int,[Name] varchar(4),[Contact_no_home] varchar(12))
go
insert [A]
select 1,'张三','025-57898785' union all
select 2,'王五','021-58965658' union all
select 3,'小孙','021-89875542' union all
select 4,'李子','028-86589851'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([Id] int,[Member_code] int,[VisRes] varchar(2),[VisNum] int)
go
insert [B]
select 1,1,'BC',1 union all
select 2,1,'CS',2 union all
select 3,2,'D1',1 union all
select 4,1,'F1',3 union all
select 5,3,'VF',1 union all
select 6,2,'CS',2declare @sql varchar(8000)select @sql = isnull(@sql+',
','') + 'max(case r.VisNum when '+ltrim(VisNum)+ ' then VisNum else 0 end) as VisNum,
max(case r.VisNum when '+ltrim(VisNum)+ ' then VisRes else ''0'' end) as VisRes'
from (select distinct VisNum from B) hselect @sql = 'select t.*,'+@sql+' from A t join B r
on t.Member_code = r.Member_code
group by t.Member_code,t.name,t.Contact_no_home'exec(@sql)
Member_code Name Contact_no_home VisNum VisRes VisNum VisRes VisNum VisRes
----------- ---- --------------- ----------- ------ ----------- ------ ----------- ------
1 张三 025-57898785 1 BC 2 CS 3 F1
2 王五 021-58965658 1 D1 2 CS 0 0
3 小孙 021-89875542 1 VF 0 0 0 0(3 行受影响)
'row_number' 不是可以识别的 函数名。
create table A
(
Member_code int primary key,
Member_name nvarchar(20),
Contact_no nvarchar(20)
);insert into A values(1,'张三','025-57898785');
insert into A values(2,'李四','025-57898785');
insert into A values(3,'王五','025-57898785');
insert into A values(4,'赵六','025-57898785');create table B
(
Id int primary key,
Member_code int,
VisRes nvarchar(10),
VisNum int
);insert into B values(1,1,'BC',1);
insert into B values(2,1,'CS',2);
insert into B values(3,2,'D1',1);
insert into B values(4,1,'F1',3);
insert into B values(5,3,'WF',1);
insert into B values(6,2,'CS',2);create view view_B
as
select Member_code,
(case when VisNum=1 then VisNum else 0 end) as VisNum1,
(case when VisNum=1 then VisRes else '' end) as VisRes1,
(case when VisNum=2 then VisNum else 0 end) as VisNum2,
(case when VisNum=2 then VisRes else '' end) as VisRes2,
(case when VisNum=3 then VisNum else 0 end) as VisNum3,
(case when VisNum=3 then VisRes else '' end) as VisRes3
from B
select A.Member_code,Member_name,Contact_no,max(VisNum1) VisNum,max(VisRes1) VisRes,max(VisNum2) VisNum,max(VisRes2) VisRes,max(VisNum3) VisNum3,max(VisRes3) VisRes
from A inner join view_B on A.Member_code=view_B.Member_code group by A.Member_code,Member_name,Contact_no