create table # (name varchar(1))
insert into #
select 'A'
union
select 'B'
union
select 'C'
union
select 'D'
select a.name,b.name
from # A,# B
where a.name<>b.name
insert into #
select 'A'
union
select 'B'
union
select 'C'
union
select 'D'
select a.name,b.name
from # A,# B
where a.name<>b.name
第二个,我想大概只需要做交叉连接可能就能做出来吧!
原始表如下格式:
Class CallDate CallCount
1 2005-8-8 40
1 2005-8-7 6
2 2005-8-8 77
3 2005-8-9 33
3 2005-8-8 9
3 2005-8-7 21根据Class的值,按日期分别统计出CallCount1,CallCount2,CallCount3。
当该日期无记录时值为0
要求合并成如下格式:
CallDate CallCount1 CallCount2 CallCount3
2005-8-9 0 0 33
2005-8-8 40 77 9
2005-8-7 6 0 21
--创建测试环境
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1','2005-8-8',40
union all select '1','2005-8-7',6
union all select '2','2005-8-8',77
union all select '3','2005-8-9',33
union all select '3','2005-8-8',9
union all select '3','2005-8-7',21
--动态SQL
declare @s varchar(8000)
set @s='select CallDate '
select @s=@s+',[CallCount'+Class+']=sum(case when Class='''+Class+''' then CallCount else 0 end)'
from T
group by Class
set @s=@s+' from T group by CallDate order by CallDate desc '
exec(@s)--结果CallDate CallCount1 CallCount2 CallCount3
------------------------------------------------------ ----------- ----------- -----------
2005-08-09 00:00:00.000 0 0 33
2005-08-08 00:00:00.000 40 77 9
2005-08-07 00:00:00.000 6 0 21--第二题(包括主场和客场比赛)
select a.Name,b.name from @t A , @t B
where a.name<>b.name
2.declare @team table(name char(1))insert @team select 'a'
insert @team select 'b'
insert @team select 'c'
insert @team select 'd'select b.name + '-' + a.name 对阵 from @team a join @team b
on a.name > b.name/*
对阵
----
a-b
a-c
a-d
b-c
b-d
c-d
*/
强 !!! 我顶
insert tb
select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
select * from tb a,tb b where a.name<b.name order by a.name
/*
name name
---- ----
a b
a c
a d
b c
b d
c d(6 row(s) affected)
*/
drop table tb
http://community.csdn.net/Expert/topic/4324/4324734.xml?temp=.5537836
create table team
(
name varchar(5)
)
insert into team select 'a'
union select 'b'
union select 'c'
union select 'd'select * from team as a left outer join team as b on a.name<b.name where b.name is not null
create table pivot
(
year int,
quarter int,
amount real
)
--季度营业额表
insert into pivot values (1990, 1, 1.1)
insert into pivot values (1990, 2, 1.2)
insert into pivot values (1990, 3, 1.3)
insert into pivot values (1990, 4, 1.4)
insert into pivot values (1991, 1, 2.1)
insert into pivot values (1991, 2, 2.2)
insert into pivot values (1991, 3, 2.3)
insert into pivot values (1991, 4, 2.4)
select * from pivot
--原始结果集
select year,
sum(case quarter when 1 then amount else 0 end) as Q1,
sum(case quarter when 2 then amount else 0 end) as Q2,
sum(case quarter when 3 then amount else 0 end) as Q3,
sum(case quarter when 4 then amount else 0 end) as Q4
from pivot
group by year
--行列互换后的结果集
/*
case:如果季度的值为1,则转换为amount列,否则计0,并将次列起别名为Q1
sum:求和,若符合条件则对该季度求和,不符合聚合0,
为0处不可为null,null将被sum和avg等聚合函数忽略,造成错误结果
*/
难道是ASC码不成??
insert into @tb select 'b'
insert into @tb select 'c'
insert into @tb select 'd'
select * from @tbselect a.name+b.name from @tb a cross join @tb b where a.name<b.name