34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[team](
[id] [int] IDENTITY(1,1) NOT NULL,
[home] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
CREATE TABLE [dbo].[team](
[id] [int] IDENTITY(1,1) NOT NULL,
[home] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
go
declare @zd varchar(5)
set @zd='aaa' --在此改变所调用的队,可写成函数或存储过程
select 轮次=identity(int,1,1),* into # from
(
select 主队=@zd,客队=home from team where home<>@zd
union all
select 主队=home,客队=@zd from team where home<>@zd
)T
select * from #
go
drop table team,#
/*
轮次 主队 客队
----------- -------------------------------------------------- --------------------------------------------------
1 aaa bbb
2 aaa ccc
3 aaa ddd
4 bbb aaa
5 ccc aaa
6 ddd aaa
(6 行受影响)
*/
CREATE TABLE [dbo].[team](
[id] [int] IDENTITY(1,1) NOT NULL,
[home] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
go
declare @zd varchar(5)
set @zd='aaa'
--select 主队=home,客队=@zd from team where home<>@zd
select 轮次=identity(int,1,1),* into # from
(
select 主队=@zd,客队=home from team where home<>@zd
union all
select 主队=home,客队=@zd from team where home<>@zd
)T
select * from #
go
drop table team,#
CREATE TABLE [dbo].[team](
[id] [int] IDENTITY(1,1) NOT NULL,
[home] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
declare @id int
set @id=2 -- 1,2,3,4
select 轮次=row_number() over(order by (case a.id when @id then 0 else 1 end),b.id)
, 主队=a.[home], 客队=b.[home]
from team a join team b on a.id<>b.id and (a.id=@id or b.id=@id)
order by (case a.id when @id then 0 else 1 end),b.id
/*set @id=1
轮次 主队 客队
-------------------- -------------------------------------------------- --------------------------------------------------
1 aaa bbb
2 aaa ccc
3 aaa ddd
4 bbb aaa
5 ccc aaa
6 ddd aaa
(6 行受影响)
*/
/*set @id=2
轮次 主队 客队
-------------------- -------------------------------------------------- --------------------------------------------------
1 bbb aaa
2 bbb ccc
3 bbb ddd
4 aaa bbb
5 ccc bbb
6 ddd bbb
(6 行受影响)
*/
drop table team
CREATE TABLE #team([id] [int] IDENTITY(1,1), [home] [nvarchar](50) )
insert into #team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
declare @team varchar(3)
set @team = 'bbb'
select @team + '--' + home from #team
where home <> @team
union all
select home + '--' + @team from #team
where home <> @team
/*
-------------------------------------------------------
bbb--aaa
bbb--ccc
bbb--ddd
aaa--bbb
ccc--bbb
ddd--bbb
*/
drop table #team