//测试表结构
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'//想要的结果
如果是aaa这个队调用的话结果如下:
轮次 主队 客队
1 aaa--bbb
2 aaa--ccc
3 aaa--ddd
4 bbb--aaa
5 ccc--aaa
6 ddd--aaa
如果是bbb这个队调用的话结果如下:
轮次 主队 客队
1 bbb--aaa
2 bbb--ccc
3 bbb--ddd
4 aaa--bbb
5 ccc--bbb
6 ddd--bbb
如果是ccc这个队调用的话结果如下:
轮次 主队 客队
1 ccc--ddd
2 ccc--aaa
3 ccc--bbb
4 ddd--ccc
5 ddd--aaa
6 ddd--bbb
如果是ddd这个队调用的话结果如下:
轮次 主队 客队
1 ddd--ccc
2 ddd--aaa
3 ddd--bbb
4 ccc--ddd
5 aaa--ddd
6 bbb--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'//想要的结果
如果是aaa这个队调用的话结果如下:
轮次 主队 客队
1 aaa--bbb
2 aaa--ccc
3 aaa--ddd
4 bbb--aaa
5 ccc--aaa
6 ddd--aaa
如果是bbb这个队调用的话结果如下:
轮次 主队 客队
1 bbb--aaa
2 bbb--ccc
3 bbb--ddd
4 aaa--bbb
5 ccc--bbb
6 ddd--bbb
如果是ccc这个队调用的话结果如下:
轮次 主队 客队
1 ccc--ddd
2 ccc--aaa
3 ccc--bbb
4 ddd--ccc
5 ddd--aaa
6 ddd--bbb
如果是ddd这个队调用的话结果如下:
轮次 主队 客队
1 ddd--ccc
2 ddd--aaa
3 ddd--bbb
4 ccc--ddd
5 aaa--ddd
6 bbb--ddd
以上就是我想要的结果,请看好结果后,找到规则后写语句,先谢谢各位
FROM team a,team b
WHERE a.id<>b.id and (a.id=1 or b.id=1) --将id改成自己要的teamID.
ORDER BY a.id
SET @TeamID=1
SELECT IDENTITY(int,1,1) as Row,a.home as Home,b.home as C
INTO #tmp
FROM team a,team b
WHERE a.id<>b.id and (a.id=@TeamID or b.id=@TeamID)
ORDER BY a.id
SELECT 轮次 =ROW, 主队 =HOME,客队=C FROM #tmp
DROP TABLE #tmp
select @home = 'aaa'
select A.home,B.home from team A,team B where A.home != B.home and (A.home = @home or B.home = @home)
order by case A.home when @home then '1' else A.home end
轮次 主队 客队
1 aaa--bbb --第一轮aaa是主场
2 aaa--ccc
3 aaa--ddd
4 bbb--aaa
5 ccc--aaa
6 ddd--aaa
如果是bbb这个队调用的话结果如下:
轮次 主队 客队
1 bbb--aaa --第一轮aaa是客场
2 bbb--ccc
3 bbb--ddd
4 aaa--bbb
5 ccc--bbb
6 ddd--bbb
你需要加一个赛程表,才能自圆其说
-------------------------------------------------- --------------------------------------------------
aaa bbb
aaa ccc
aaa ddd
bbb aaa
ccc aaa
ddd aaa(6 個資料列受到影響)
CREATE TABLE [dbo].[team](
[id] [int] IDENTITY(1,1) NOT NULL,
[home] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) insert into team select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
declare @home nvarchar(50)
set @home = 'aaa'SELECT IDENTITY(int,1,1) as Row,a.home as Home,b.home as C
INTO #tmp
FROM team a,team b
WHERE a.id<>b.id and (a.home=@home or b.home=@home)
ORDER BY a.id
SELECT 轮次 =ROW, 主队 =HOME,客队=C FROM #tmpDROP TABLE [team]
DROP TABLE #tmp
排序有点问题.
DECLARE @TeamID INT
SET @TeamID=1CREATE TABLE #Result(Row INT IDENTITY(1,1),Home VARCHAR(10),C VARCHAR(10))INSERT INTO #Result(Home,C)
SELECT a.home as Home,b.home as C
FROM team a,team b
WHERE a.id<>b.id and (a.id=@TeamID or b.id=@TeamID)
ORDER BY CASE WHEN a.id=@TeamID THEN 0 ELSE 1 END,a.IDSELECT 轮次 =ROW, 主队 =HOME,客队=C FROM #Resultdrop table #Result
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
[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
[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<>@zdselect 轮次=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,#
[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 行受影响)
*/
如果是aaa这个队调用的话结果如下:
轮次 主队 客队
1 aaa--bbb
2 ccc--aaa
3 ddd--aaa
4 bbb--aaa
5 aaa--ccc
6 aaa--ddd
如果是bbb这个队调用的话结果如下:
轮次 主队 客队
1 aaa--bbb
2 ddd--bbb
3 bbb--ccc
4 bbb--aaa
5 bbb--ddd
6 ccc--bbb
如果是ccc这个队调用的话结果如下:
轮次 主队 客队
1 ccc--ddd
2 ccc--aaa
3 bbb--ccc
4 ddd--ccc
5 aaa--ccc
6 ccc--bbb
如果是ddd这个队调用的话结果如下:
轮次 主队 客队
1 ccc--ddd
2 ddd--bbb
3 ddd--aaa
4 ddd--ccc
5 bbb--ddd
6 aaa--ddd
SET @TeamID=1CREATE TABLE #Result(Row INT IDENTITY(1,1),Home VARCHAR(10),C VARCHAR(10))INSERT INTO #Result(Home,C)
SELECT a.home as Home,b.home as C
FROM team a,team b
WHERE a.id<>b.id and (a.id=@TeamID or b.id=@TeamID)
ORDER BY CASE WHEN a.id=@TeamID THEN 0 ELSE 1 END,a.IDSELECT 轮次 =ROW, 主队 =HOME,客队=C FROM #Resultdrop table #Result