1.数据库sqlserver2012
2.问题：结果集创表左括号一直报错
3.具体描述：
--创建表s
create table s(
name varchar(20),
score varchar(20)
)
.--插入数据
insert into s values('A','胜')
insert into s values('A','胜')
insert into s values('A','负')
insert into s values('A','负')
insert into s values('B','胜')
insert into s values('B','胜')
insert into s values('B','胜')
insert into s values('C','负')
--显示一张表为
A 2 2
B 3 0
C 0 1
--去重子查询结果为括号部分能正常查询，加入创表则括号报错 select * into sc from ()也报错
create table sc as (select distinct top 100 percent a.name,(select count(*) from s where name=a.name and score='胜') [胜],
(select count(*) from s where name=a.name and score='负') [负] from s a group by score,a.name)
问题出在语句上，没有了解清楚sqlserver的结果集插入还没有创建的表
select * into 表名（新表） from 表名（旧表）
查询语句
select * into sc from s
select distinct top 100 percent name,
(select count(*) from s where name=a.name and score='胜') [胜],
(select count(*) from s where name=a.name and score='负') [负]
from s a group by score,name order by name
该回答引用ChatGPT
在创建表 sc 时，括号中的 SQL 语句需要加上表别名 a，否则会出现语法错误。
修改后的代码如下：
create table sc as (
select distinct top 100 percent a.name,
(select count(*) from s where name=a.name and score='胜') as [胜],
(select count(*) from s where name=a.name and score='负') as [负]
from s a
group by a.name
)
这样，就可以成功创建名为 sc 的表了。
select distinct top 100 percent a.name,(select count(*) from s where name=a.name and score='胜') [胜],
(select count(*) from s where name=a.name and score='负') [负] into sc from s a group by score,a.name