22,210
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from sysobjects where id = OBJECT_ID('[Table_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Table_1]
CREATE TABLE [Table_1] (
[id] [int] NULL,
[主场] [nchar] (10) NULL,
[客场] [nchar] (10) NULL)
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 1,N'1001',N'1003')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 2,N'1001',N'1008')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 3,N'1001',N'1005')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 4,N'1001',N'1006')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 5,N'1001',N'1002')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 6,N'1002',N'1003')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 7,N'1002',N'1008')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 8,N'1002',N'1005')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 9,N'1002',N'1006')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 10,N'1003',N'1008')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 11,N'1003',N'1005')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 12,N'1003',N'1006')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 13,N'1005',N'1008')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 14,N'1005',N'1006')
INSERT [Table_1] ([id],[主场],[客场]) VALUES ( 15,N'1005',N'1008')
go
with cte_z as
(
select 主场,COUNT (主场)as 次数 from table_1 group by 主场
),
cte_k as
(
select 客场,COUNT (客场)as 次数 from table_1 group by 客场
)
select 主场,SUM (次数) as 总次数 from (select * from cte_z union all select * from cte_k) t group by t .主场 order by 总次数 desc
--可以考虑优先拍总次数多的
--1005 6
--1001 5
--1002 5
--1003 5
--1008 5
--1006 4