--建表
CREATE TABLE aa
(
Name varchar(200),
birth datetime
)
--添加测试数据
insert into aa(Name,birth) values('张先生',1982-10-24)
insert into aa(Name,birth) values('林先生',1982-01-24)
insert into aa(Name,birth) values('余小姐',1982-10-24)
insert into aa(Name,birth) values('李先生',1982-05-24)
insert into aa(Name,birth) values('李小姐',1982-05-24)
insert into aa(Name,birth) values('郑先生',1982-02-24)
insert into aa(Name,birth) values('黄先生',1982-03-24)
insert into aa(Name,birth) values('唐小姐',1982-03-24)--查询
select
'一月份' = (case month(aa.birth) when 1 then name else '' end),
'二月份' = (case month(aa.birth) when 2 then name else '' end),
'三月份' = (case month(aa.birth) when 3 then name else '' end),
'四月份' = (case month(aa.birth) when 4 then name else '' end),
'五月份' = (case month(aa.birth) when 5 then name else '' end),
'六月份' = (case month(aa.birth) when 6 then name else '' end),
'七月份' = (case month(aa.birth) when 7 then name else '' end),
'八月份' = (case month(aa.birth) when 8 then name else '' end),
'九月份' = (case month(aa.birth) when 9 then name else '' end),
'十月份' = (case month(aa.birth) when 10 then name else '' end),
'十一月份' = (case month(aa.birth) when 11 then name else '' end),
'十二月份' = (case month(aa.birth) when 12 then name else '' end)
from aa
--删除
--drop aa
CREATE TABLE aa
(
Name varchar(200),
birth datetime
)
--添加测试数据
insert into aa(Name,birth) values('张先生',1982-10-24)
insert into aa(Name,birth) values('林先生',1982-01-24)
insert into aa(Name,birth) values('余小姐',1982-10-24)
insert into aa(Name,birth) values('李先生',1982-05-24)
insert into aa(Name,birth) values('李小姐',1982-05-24)
insert into aa(Name,birth) values('郑先生',1982-02-24)
insert into aa(Name,birth) values('黄先生',1982-03-24)
insert into aa(Name,birth) values('唐小姐',1982-03-24)--查询
select
'一月份' = (case month(aa.birth) when 1 then name else '' end),
'二月份' = (case month(aa.birth) when 2 then name else '' end),
'三月份' = (case month(aa.birth) when 3 then name else '' end),
'四月份' = (case month(aa.birth) when 4 then name else '' end),
'五月份' = (case month(aa.birth) when 5 then name else '' end),
'六月份' = (case month(aa.birth) when 6 then name else '' end),
'七月份' = (case month(aa.birth) when 7 then name else '' end),
'八月份' = (case month(aa.birth) when 8 then name else '' end),
'九月份' = (case month(aa.birth) when 9 then name else '' end),
'十月份' = (case month(aa.birth) when 10 then name else '' end),
'十一月份' = (case month(aa.birth) when 11 then name else '' end),
'十二月份' = (case month(aa.birth) when 12 then name else '' end)
from aa
--删除
--drop aa
CREATE TABLE aa
(
name varchar(200),
birth datetime
)
--添加测试数据
insert into aa(name,birth) values('张先生','1982-10-24')
insert into aa(name,birth) values('林先生','1982-01-24')
insert into aa(name,birth) values('余小姐','1982-10-24')
insert into aa(name,birth) values('李先生','1982-05-24')
insert into aa(name,birth) values('李小姐','1982-05-24')
insert into aa(name,birth) values('郑先生','1982-02-24')
insert into aa(name,birth) values('黄先生','1982-03-24')
insert into aa(name,birth) values('唐小姐','1982-03-24')
go--查询
declare @1 table(id int identity,name varchar(20))
declare @2 table(id int identity,name varchar(20))
declare @3 table(id int identity,name varchar(20))
declare @4 table(id int identity,name varchar(20))
declare @5 table(id int identity,name varchar(20))
declare @6 table(id int identity,name varchar(20))
declare @7 table(id int identity,name varchar(20))
declare @8 table(id int identity,name varchar(20))
declare @9 table(id int identity,name varchar(20))
declare @10 table(id int identity,name varchar(20))
declare @11 table(id int identity,name varchar(20))
declare @12 table(id int identity,name varchar(20))insert @1(name) select name from aa where month(birth)=1
insert @2(name) select name from aa where month(birth)=2
insert @3(name) select name from aa where month(birth)=3
insert @4(name) select name from aa where month(birth)=4
insert @5(name) select name from aa where month(birth)=5
insert @6(name) select name from aa where month(birth)=6
insert @7(name) select name from aa where month(birth)=7
insert @8(name) select name from aa where month(birth)=8
insert @9(name) select name from aa where month(birth)=9
insert @10(name) select name from aa where month(birth)=10
insert @11(name) select name from aa where month(birth)=11
insert @12(name) select name from aa where month(birth)=12 select [1].name
,[2].name
,[3].name
,[4].name
,[5].name
,[6].name
,[7].name
,[8].name
,[9].name
,[10].name
,[11].name
,[12].namefrom @1 [1]
full join @2 [2] on [1].id=[2].id
full join @3 [3] on [1].id=[3].id
full join @4 [4] on [1].id=[4].id
full join @5 [5] on [1].id=[5].id
full join @6 [6] on [1].id=[6].id
full join @7 [7] on [1].id=[7].id
full join @8 [8] on [1].id=[8].id
full join @9 [9] on [1].id=[9].id
full join @10 [10] on [1].id=[10].id
full join @11 [11] on [1].id=[11].id
full join @12 [12] on [1].id=[12].id--删除测试环境
drop table aa--结果
/*
name name name name name name name name name name name name
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
林先生 郑先生 黄先生 NULL 李先生 NULL NULL NULL NULL 张先生 NULL NULL
NULL NULL 唐小姐 NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL 李小姐 NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL 余小姐 NULL NULL(所影响的行数为 4 行)
*/
小山的办法虽然可以实现,但是效果不好,因为统计后的行数与原表的行数(记录数)是一样的,如果用来做统计报表,那这个报表也太长了
一月份 二月份 三月份 四月份 五月份
林先生 郑先生 黄先生 李先生
唐小姐 李小姐
这种的话
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
林先生 郑先生 黄先生 NULL 李先生 NULL NULL NULL NULL 张先生 NULL NULL根本就不是要的结果!
--建表
CREATE TABLE aa
(
name varchar(200),
birth datetime
)
--添加测试数据
insert into aa(name,birth) values('张先生','1982-10-24')
insert into aa(name,birth) values('林先生','1982-01-24')
insert into aa(name,birth) values('余小姐','1982-10-24')
insert into aa(name,birth) values('李先生','1982-05-24')
insert into aa(name,birth) values('李小姐','1982-05-24')
insert into aa(name,birth) values('郑先生','1982-02-24')
insert into aa(name,birth) values('黄先生','1982-03-24')
insert into aa(name,birth) values('唐小姐','1982-03-24')
insert into aa(name,birth) values('郑先生','1982-02-24')
insert into aa(name,birth) values('黄先生','1982-03-24')
insert into aa(name,birth) values('唐小姐','1982-03-24')
go--查询
declare @1 table(id int identity,name varchar(20))
declare @2 table(id int identity,name varchar(20))
declare @3 table(id int identity,name varchar(20))
declare @4 table(id int identity,name varchar(20))
declare @5 table(id int identity,name varchar(20))
declare @6 table(id int identity,name varchar(20))
declare @7 table(id int identity,name varchar(20))
declare @8 table(id int identity,name varchar(20))
declare @9 table(id int identity,name varchar(20))
declare @10 table(id int identity,name varchar(20))
declare @11 table(id int identity,name varchar(20))
declare @12 table(id int identity,name varchar(20))insert @1(name) select name from aa where month(birth)=1
insert @2(name) select name from aa where month(birth)=2
insert @3(name) select name from aa where month(birth)=3
insert @4(name) select name from aa where month(birth)=4
insert @5(name) select name from aa where month(birth)=5
insert @6(name) select name from aa where month(birth)=6
insert @7(name) select name from aa where month(birth)=7
insert @8(name) select name from aa where month(birth)=8
insert @9(name) select name from aa where month(birth)=9
insert @10(name) select name from aa where month(birth)=10
insert @11(name) select name from aa where month(birth)=11
insert @12(name) select name from aa where month(birth)=12 select * from @5
select * from @1
select * from @3select [一月份]=case when [1].name is null then '' else [1].name end,
[二月份]=case when [2].name is null then '' else [2].name end,
[三月份]=case when [3].name is null then '' else [3].name end,
[四月份]=case when [4].name is null then '' else [4].name end,
[五月份]=case when [5].name is null then '' else [5].name end,
[六月份]=case when [6].name is null then '' else [6].name end,
[七月份]=case when [7].name is null then '' else [7].name end,
[八月份]=case when [8].name is null then '' else [8].name end,
[九月份]=case when [9].name is null then '' else [9].name end,
[十月份]=case when [10].name is null then '' else [10].name end,
[十一月份]=case when [11].name is null then '' else [11].name end,
[十二月份]=case when [12].name is null then '' else [12].name end
from @1 [1] full join @2 [2] on [1].id=[2].id
full join @3 [3] on [2].id=[3].id
full join @4 [4] on [1].id=[4].id
full join @5 [5] on [3].id=[5].id
full join @6 [6] on [1].id=[6].id
full join @7 [7] on [1].id=[7].id
full join @8 [8] on [1].id=[8].id
full join @9 [9] on [1].id=[9].id
full join @10 [10] on [5].id=[10].id
full join @11 [11] on [1].id=[11].id
full join @12 [12] on [1].id=[12].id
--删除测试环境
drop table aa
一月份 二月份 三月份 四月份 五月份 六月份 七月份 八月份 九月份 十月份 十一月份 十二月份
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
林先生 郑先生 黄先生 李先生 张先生
郑先生 唐小姐 李小姐 余小姐
唐小姐
黄先生 (所影响的行数为 4 行)
晕倒........一个是给字段起个别名,一个是把null变为'',这种小问题也要拿出来说?
WHEN 2 THEN name ELSE '' END) AS '2', (CASE month(birth)
WHEN 3 THEN name ELSE '' END) AS '3 ', (CASE month(birth)
WHEN 4 THEN name ELSE '' END) AS ' 4 ', (CASE month(birth)
WHEN 5 THEN name ELSE '' END) AS ' 5 ', (CASE month(birth)
WHEN 6 THEN name ELSE '' END) AS '6 ', (CASE month(birth)
WHEN 7 THEN name ELSE '' END) AS ' 7 ', (CASE month(birth)
WHEN 8 THEN name ELSE '' END) AS ' 8 ', (CASE month(birth)
WHEN 9 THEN name ELSE '' END) AS ' 9 ', (CASE month(birth)
WHEN 10 THEN name ELSE '' END) AS ' 10 ', (CASE month(birth)
WHEN 11 THEN name ELSE '' END) AS ' 11 ', (CASE month(birth)
WHEN 12 THEN name ELSE '' END) AS ' 12 '
FROM aa
靠 vivianfdlpw 你嚣张个什么劲!!!
结果你还不是要靠表变量量来解决!靠!跟用临时表还不是换汤不换药!!
楼主建一张这样格式的表阿!
这多快!!直接 select * from 表就可以哦!!!
vivianfdlpw() 我想你也不是为分才来这回贴的吗!
什么5星大斑竹也好!MVP也好!算什么阿!
我们在那些BOSS眼里还不跟机器一样!!
作为技术人员!唯一的快乐也即即就是开发过程中解决难题的那一瞬间
而我也即即是这CSDN上找寻我的快乐而已!!
CREATE TABLE a
(
name varchar(200),
birth datetime
)
--添加测试数据
insert into a(name,birth) values('张先生','1982-10-24')
insert into a(name,birth) values('林先生','1982-01-24')
insert into a(name,birth) values('余小姐','1982-10-24')
insert into a(name,birth) values('李先生','1982-05-24')
insert into a(name,birth) values('李小姐','1982-05-24')
insert into a(name,birth) values('郑先生','1982-02-24')
insert into a(name,birth) values('黄先生','1982-03-24')
insert into a(name,birth) values('唐小姐','1982-03-24')declare
@a int,
@sql varchar(8000)
set @a=1
set @sql=''
while (@a<=12)
begin
select @sql=@sql+','+''''+rtrim(convert(char,@a))+''''+'
=case when month(birth)='+rtrim(convert(char,@a))+' then a.name else '''' end '
set @a=@a+1
end
set @sql='select ' + stuff(@sql,1,1,'')+ ' from a'
exec (@sql)看下我的这个,能用不