有几张会议和旅游的记录统计表
分会一:
--------------------------------------------------------------------------------------------------
username(会员编号)| passport(护照号码)|userChname(用户姓名)|company|Job | signTime(签到时间)
---------------------------------------------------------------------------------------------------
VIP001 | 00100 | 李宁 | 北京 |经理| 2010-08-20 17:46 |分会二和分会三,分会四,旅游景点一,旅游景点二, 和分会一的表结构相同。求一汇总表格:如果某位会员参加的话就位对号,如果没参加就为X号。
如:
--------------------------------------------------------------------------------------------------
userName(会员编号)| 分会-| 分会二|分会三|分会四|旅游景点一|旅游景点二|
--------------------------------------------------------------------------------------------------
VIP001 | √ | √ | √ | √ | × | × |
--------------------------------------------------------------------------------------------------
VIP002 | √ | × | × | × | × | × |
---------------------------------------------------------------------------------------------------
请教各位xdjm!
分会一:
--------------------------------------------------------------------------------------------------
username(会员编号)| passport(护照号码)|userChname(用户姓名)|company|Job | signTime(签到时间)
---------------------------------------------------------------------------------------------------
VIP001 | 00100 | 李宁 | 北京 |经理| 2010-08-20 17:46 |分会二和分会三,分会四,旅游景点一,旅游景点二, 和分会一的表结构相同。求一汇总表格:如果某位会员参加的话就位对号,如果没参加就为X号。
如:
--------------------------------------------------------------------------------------------------
userName(会员编号)| 分会-| 分会二|分会三|分会四|旅游景点一|旅游景点二|
--------------------------------------------------------------------------------------------------
VIP001 | √ | √ | √ | √ | × | × |
--------------------------------------------------------------------------------------------------
VIP002 | √ | × | × | × | × | × |
---------------------------------------------------------------------------------------------------
请教各位xdjm!
if object_id('分会一') is not null drop table 分会一
create table 分会一 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)
insert into 分会一
select 'VIP001','00100','李宁','北京','经理','2010-08-20 17:46'--> 测试数据: 分会二
if object_id('分会二') is not null drop table 分会二
create table 分会二 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)
insert into 分会二
select 'VIP002','00200','刘哈','北京','经理','2010-08-20 17:46'--> 测试数据: 分会三
if object_id('分会三') is not null drop table 分会三
create table 分会三 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)--> 测试数据: 分会四
if object_id('分会四') is not null drop table 分会四
create table 分会四 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)--> 测试数据: 旅游景点一
if object_id('旅游景点一') is not null drop table 旅游景点一
create table 旅游景点一 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)--> 测试数据: 旅游景点二
if object_id('旅游景点二') is not null drop table 旅游景点二
create table 旅游景点二 (username varchar(6),passport varchar(5),userChname varchar(4),company varchar(4),Job varchar(4),signTime datetime)
gocreate proc sp_wsp
as
with wsp
as
(
select * from 分会一
union all
select * from 分会二
union all
select * from 分会三
union all
select * from 分会四
union all
select * from 旅游景点一
union all
select * from 旅游景点二
)
select username,
分会一=case when exists(select 1 from 分会一 where username=a.username) then '√' else '×' end,
分会二=case when exists(select 1 from 分会二 where username=a.username) then '√' else '×' end,
分会三=case when exists(select 1 from 分会三 where username=a.username) then '√' else '×' end,
分会四=case when exists(select 1 from 分会四 where username=a.username) then '√' else '×' end,
旅游景点一=case when exists(select 1 from 旅游景点一 where username=a.username) then '√' else '×' end,
旅游景点二=case when exists(select 1 from 旅游景点二 where username=a.username) then '√' else '×' end
from wsp a
go
exec sp_wsp--结果:
username 分会一 分会二 分会三 分会四 旅游景点一 旅游景点二
-------- ---- ---- ---- ---- ----- -----
VIP001 √ × × × × ×
VIP002 × √ × × × ×
如果是2000 。就这样:
create proc sp_wsp
as
declare @t table(username varchar(20),passport varchar(50),userChname varchar(100),company varchar(100),Job varchar(50),signTime datetime) insert into @t
select * from 分会一
union all
select * from 分会二
union all
select * from 分会三
union all
select * from 分会四
union all
select * from 旅游景点一
union all
select * from 旅游景点二
select username,
分会一=case when exists(select 1 from 分会一 where username=a.username) then '√' else '×' end,
分会二=case when exists(select 1 from 分会二 where username=a.username) then '√' else '×' end,
分会三=case when exists(select 1 from 分会三 where username=a.username) then '√' else '×' end,
分会四=case when exists(select 1 from 分会四 where username=a.username) then '√' else '×' end,
旅游景点一=case when exists(select 1 from 旅游景点一 where username=a.username) then '√' else '×' end,
旅游景点二=case when exists(select 1 from 旅游景点二 where username=a.username) then '√' else '×' end
from @t a
go--执行存储过程
exec sp_wsp
create proc proc_total
as
declare @t table (userName varchar(20),cardNo nvarchar(50), passport nvarchar(50), Enname nvarchar(50), Chname nvarchar (50), Job nvarchar(50),
swipTime nvarchar(50) , RoomNo varchar(20), Company varchar(20), varchar(20)) select userName,
分会一=case when exists (select 1 from T_FRecords where userName = a.userName) then '√' else '×' end,
分会二=case when exists (select 1 from T_SRecords where userName =a.userName) then '√' else '×' end,
分会三=case when exists (select 1 from T_TRecords where userName =a.userName) then '√' else '×' end,
分会四=case when exists (select 1 from T_FORecords where userName =a.userName) then '√' else '×' end,
蜈支洲岛=case when exists (select 1 from T_WZZRecords where userName =a.userName) then '√' else '×' end,
分界洲岛=case when exists (select 1 from T_WZZRecords where userName =a.userName) then '√' else '×' end,
高尔夫=case when exists (select 1 from T_WZZRecords where userName =a.userName) then '√' else '×' end,
南山寺=case when exists (select 1 from T_WZZRecords where userName =a.userName) then '√' else '×' end
from @t a
go
回前辈,此时表结构已经出来,但是无数据,
我当前的统计表已经有数据了,求教。
select
username,
分会一=case when 分会一=1 then '√' else '×' end,
分会二=case when 分会二=1 then '√' else '×' end,
分会三=case when 分会三=1 then '√' else '×' end,
分会四=case when 分会四=1 then '√' else '×' end,
旅游景点一=case when 旅游景点一=1 then '√' else '×' end,
旅游景点二 =case when 旅游景点二 =1 then '√' else '×' end
from
(
select userName,1 as 分会一,0 as 分会二,0 as 分会三,0 as 分会四,0 as 旅游景点一,0 as 旅游景点二 from 分会一
union all
select userName,0,1,0,0,0,0 from 分会二
union all
select userName,0,0,1,0,0,0 from 分会三
union all
select userName,0,0,0,1,0,0 from 分会四
union all
select userName,0,1,0,0,1,0 from 旅游景点一
union all
select userName,0,1,0,0,0,1 from 旅游景点二
) t/**
username 分会一 分会二 分会三 分会四 旅游景点一 旅游景点二
-------- ---- ---- ---- ---- ----- -----
VIP001 √ × × × × ×
VIP002 × √ × × × ×(2 行受影响)
**/
select
username,
分会一=case when fh1=1 then '√' else '×' end,
分会二=case when fh2=1 then '√' else '×' end,
分会三=case when fh3=1 then '√' else '×' end,
分会四=case when fh4=1 then '√' else '×' end,
旅游景点一=case when jd1=1 then '√' else '×' end,
旅游景点二 =case when jd2 =1 then '√' else '×' end
from
(
select username,max(fh1) fh1,max(fh2) fh2,max(fh3) fh3,max(fh4) fh4,max(jd1) jd1,max(jd2) jd2
from
(
select userName,1 as fh1,0 as fh2,0 as fh3,0 as fh4,0 as jd1,0 as jd2 from 分会一
union all
select userName,0,1,0,0,0,0 from 分会二
union all
select userName,0,0,1,0,0,0 from 分会三
union all
select userName,0,0,0,1,0,0 from 分会四
union all
select userName,0,1,0,0,1,0 from 旅游景点一
union all
select userName,0,1,0,0,0,1 from 旅游景点二
) t1
group by username
) t2
[分会一]=case when b.username is null then '×' else '√ ' end,
[分会二]=case when c.username is null then '×' else '√ ' end,
[分会三]=case when d.username is null then '×' else '√ ' end,
[分会四]=case when e.username is null then '×' else '√ ' end,
[旅游景点一]=case when f.username is null then '×' else '√ ' end,
[旅游景点二]=case when g.username is null then '×' else '√ ' end
from
(select username from 分会一
union select username from 分会二
union select username from 分会三
union select username from 分会四
union select username from 旅游景点一
union select username from 旅游景点二) a left join 分会一 as b on a.username=b.username
left join 分会二 as c on a.username=c.username
left join 分会三 as d on a.username=d.username
left join 分会四 as e on a.username=e.username
left join 旅游景点一 as f on a.username=f.username
left join 旅游景点二 as g on a.username=g.username
--那就这样:
create proc sp_wsp
as
declare @t table(username varchar(20)) insert into @t
select username from 分会一
union
select username from 分会二
union
select username from 分会三
union
select username from 分会四
union
select username from 旅游景点一
union
select username from 旅游景点二
select username,
分会一=case when exists(select 1 from 分会一 where username=a.username) then '√' else '×' end,
分会二=case when exists(select 1 from 分会二 where username=a.username) then '√' else '×' end,
分会三=case when exists(select 1 from 分会三 where username=a.username) then '√' else '×' end,
分会四=case when exists(select 1 from 分会四 where username=a.username) then '√' else '×' end,
旅游景点一=case when exists(select 1 from 旅游景点一 where username=a.username) then '√' else '×' end,
旅游景点二=case when exists(select 1 from 旅游景点二 where username=a.username) then '√' else '×' end
from @t a
go--执行存储过程
exec sp_wsp
[分会一]=case when b.username is null then '×' else '√ ' end,
[分会二]=case when c.username is null then '×' else '√ ' end,
[分会三]=case when d.username is null then '×' else '√ ' end,
[分会四]=case when e.username is null then '×' else '√ ' end,
[旅游景点一]=case when f.username is null then '×' else '√ ' end,
[旅游景点二]=case when g.username is null then '×' else '√ ' end
from
(select username from 分会一
union select username from 分会二
union select username from 分会三
union select username from 分会四
union select username from 旅游景点一
union select username from 旅游景点二) a left join 分会一 as b on a.username=b.username
left join 分会二 as c on a.username=c.username
left join 分会三 as d on a.username=d.username
left join 分会四 as e on a.username=e.username
left join 旅游景点一 as f on a.username=f.username
left join 旅游景点二 as g on a.username=g.username