尝试在Microsoft SQL Server中创建临时表，但总是遇到错误

kqlmhetl 于 2022-12-10 发布在 SQL Server

关注(0)|答案(1)|浏览(253)

create table #PercentofPopulationVaccinated
(
    continent nvarchar(255),
    location nvarchar(255),
    date datetime,
    Population numeric,
    people_fully_vaccinated numeric, 
    [%_of_pop_vaxxxed] numeric,
    rn int
)

insert into #PercentofPopulationVaccinated
    select 
        cd.continent, cd.location, cd.date, cd.population, 
        vac.people_fully_vaccinated, 
        (cast(vac.people_fully_vaccinated as int) / cd.population) * 100 as [%_of_pop_vaxxxed], 
        rn = row_number() over (partition by cd.Location order by (vac.people_fully_vaccinated / cd.population) * 100 desc, cd.Date) 
    from 
        coviddeaths as cd 
    join 
        covidvaccinations vac on cd.location = vac.location  
                              and cd.date = vac.date
    where 
        cd.continent is not null

select *
from #PercentofPopulationVaccinated

Error
Column name or number of supplied values does not match table definition
This error is odd; I'm sure it has to do with row number

sql-server

来源：https://stackoverflow.com/questions/70736126/trying-to-create-a-temp-table-in-microsoft-sql-server-but-keep-getting-hit-with

1条答案

按热度按时间

lsmepo6l1#

First if that is your temporary table, you should first check if that table exist and drop it.

if object_id('tempdb..#PercentofPopulationVaccinated') is not null drop table #PercentofPopulationVaccinated

After that table definition should be:

create table #PercentofPopulationVaccinated
(
    continent nvarchar(20),
    data_location nvarchar(255),
    data_date datetime,
    total_population float,
    people_fully_vaccinated float, 
    [%_of_pop_vaxxxed] decimal(8,4),
    rn
)

and your select:

insert into #PercentofPopulationVaccinated (
    continent,
    data_location,
    data_date,
    total_population,
    people_fully_vaccinated, 
    [%_of_pop_vaxxxed],
    rn int
    )
select 
    cd.continent, 
    cd.location, cd.date, cd.population, 
    vac.people_fully_vaccinated, 
    (vac.people_fully_vaccinated / cd.population) * 100 as [%_of_pop_vaxxxed], 
    rn = row_number() over (partition by cd.Location order by 
    (vac.people_fully_vaccinated / cd.population) * 100 desc, cd.Date) 
from 
    coviddeaths as cd 
join 
    covidvaccinations vac on cd.location = vac.location  
                              and cd.date = vac.date
where 
    cd.continent is not null

select *
from #PercentofPopulationVaccinated

赞(0）回复(0）举报 2022-12-10

我来回答

尝试在Microsoft SQL Server中创建临时表，但总是遇到错误

1条答案

相关问题

热门标签

最新问答