The commands to get it working are:
yarn add vue@next
yarn add @vue/compiler-sfc -D
Note the -D to add it to the devDependencies rather than the dependencies.
Then to remove the old dependency:
yarn remove vue-template-compiler
Note that vue@next currently points at the latest release of Vue 3 but that will likely change in future.
I posted a longer explanation on the Vue forums:
https://forum.vuejs.org/t/after-upgrading-to-vue-3-cannot-find-module-vue-compiler-sfc-package-json/103424
Update:
For anyone using npm rather than yarn, the same 3 commands should work if you change the first word from yarn to npm.