求一汇总sql语句

[garfieldzf]

有几张会议和旅游的记录统计表
分会一:
--------------------------------------------------------------------------------------------------
username(会员编号)| passport(护照号码)|userChname(用户姓名)|company|Job | signTime(签到时间)
---------------------------------------------------------------------------------------------------
VIP001 | 00100 | 李宁 | 北京 |经理| 2010-08-20 17:46 |

分会二和分会三，分会四，旅游景点一，旅游景点二， 和分会一的表结构相同。

求一汇总表格：如果某位会员参加的话就位对号，如果没参加就为X号。
如：
--------------------------------------------------------------------------------------------------
userName(会员编号)| 分会-| 分会二|分会三|分会四|旅游景点一|旅游景点二|
--------------------------------------------------------------------------------------------------
VIP001 | √ | √ | √ | √ | × | × |
--------------------------------------------------------------------------------------------------
VIP002 | √ | × | × | × | × | × |
---------------------------------------------------------------------------------------------------


请教各位xdjm！

[pt1314917]

--> 测试数据: 分会一
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)
go

create 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   ×    √    ×    ×    ×     ×

[garfieldzf]

谢谢楼上这位前辈。晚饭后结贴。

[garfieldzf]

pt1314917前辈，wsp关键字是？

sql2000不支持wsp。

[pt1314917]

wsp只是个别名而已。。。你是sql2000？是不支持with
如果是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

[garfieldzf]

pt1314917， 我用的2K， 谢谢前辈指点。

sql查询还真是弱项。

[garfieldzf]

  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), mark 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 

回前辈，此时表结构已经出来，但是无数据，
我当前的统计表已经有数据了，求教。

[百年树人]

借PT哥的数据

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 行受影响)
**/

[百年树人]

[Quote=引用 7 楼 josy 的回复:]
借PT哥的数据
SQL code
select
username,
分会一=case when 分会一=1 then '√' else '×' end,
分会二=case when 分会二=1 then '√' else '×' end,
分会三=case when 分会三=1 then '√' else '×' end,
分会四=case when 分会四……
[/Quote]
修正一下

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

[cxmcxm]

select a.username,
	[分会一]=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

[garfieldzf]

回小陈，现在的结果和表结构都正确，但是回有很多冗余数据，

比如，一个会员编号可能会在某个签到记录表里出现很多次记录，但是我在汇总表里只想
让他出现一次，证明他都去过那些地点。

请教中......

[pt1314917]

[Quote=引用 10 楼 lovesongforever 的回复:]
回小陈，现在的结果和表结构都正确，但是回有很多冗余数据，

比如，一个会员编号可能会在某个签到记录表里出现很多次记录，但是我在汇总表里只想
让他出现一次，证明他都去过那些地点。

请教中......
[/Quote]

--那就这样：


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

[juyamei126]

select DISTINCT a.username,
[分会一]=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

[kiruya0]

行转列看下就可以了