sql 把两张结构相似的表拼接为一个视图，以下为我写的sql
dbo.View_CheckSelfNote.UserId as UserId,
dbo.View_CheckSelfNote.UserName as UserName
dbo.View_CheckSelfNote.UpdateTime as UpdateTime,
dbo.View_CheckSelfNote.ClassHour as ClassHour,
dbo.View_CheckSelfNote.SelfPlanName as SelfPlanName,
dbo.View_CheckSelfNote.[year] as [year],
dbo.View_CheckSelfNote.Score as Score,
dbo.View_CheckSelfNote.Type as Type,
dbo.View_CheckSelfNote.IsFinish as IsClassHourGet,
CASE dbo.View_CheckSelfNote.IsFinish WHEN 0 THEN 0 WHEN 1 THEN dbo.View_CheckSelfNote.ClassHour END AS ClassHourGet
FROM
dbo.View_CheckSelfNote
UNION
SELECT
dbo.BM_Staff_Tbl.StaffId as UserId,
dbo.BM_Staff_Tbl.UserName as UserName,
dbo.ClassHourEx_Tbl.TestTime as UpdateTime,
dbo.ClassHourEx_Tbl.ClassHour as ClassHour,
dbo.ClassHourEx_Tbl.CourseName as SelfPlanName,
YEAR (
dbo.ClassHourEx_Tbl.TestTime
) AS [year],
dbo.ClassHourEx_Tbl.Score as Score,
dbo.ClassHourEx_Tbl.Type as Type,
dbo.ClassHourEx_Tbl.IsFinish as IsClassHourGet,
CASE dbo.ClassHourEx_Tbl.IsFinish WHEN 0 THEN 0 WHEN 1 THEN dbo.ClassHourEx_Tbl.ClassHour END AS ClassHourGet
FROM
dbo.ClassHourEx_Tbl,
dbo.BM_Staff_Tbl
INNER JOIN dbo.BM_Staff_Tbl ON dbo.ClassHourEx_Tbl.UserId = dbo.BM_Staff_Tbl.UserName
报错，[Err] 42000 - [SQL Server]'dbo' 附近有语法错误。 展开
问题在第三行
SELECT
dbo.View_CheckSelfNote.UserId as UserId,
dbo.View_CheckSelfNote.UserName as UserName--缺少逗号
dbo.View_CheckSelfNote.UpdateTime as UpdateTime,
dbo.View_CheckSelfNote.ClassHour as ClassHour,
dbo.View_CheckSelfNote.SelfPlanName as SelfPlanName,
dbo.View_CheckSelfNote.[year] as [year],
dbo.View_CheckSelfNote.Score as Score,
dbo.View_CheckSelfNote.Type as Type,
dbo.View_CheckSelfNote.IsFinish as IsClassHourGet,
CASE dbo.View_CheckSelfNote.IsFinish WHEN 0 THEN 0 WHEN 1 THEN dbo.View_CheckSelfNote.ClassHour END AS ClassHourGet
FROM
dbo.View_CheckSelfNote
UNION
SELECT
dbo.BM_Staff_Tbl.StaffId as UserId,
dbo.BM_Staff_Tbl.UserName as UserName,
dbo.ClassHourEx_Tbl.TestTime as UpdateTime,
dbo.ClassHourEx_Tbl.ClassHour as ClassHour,
dbo.ClassHourEx_Tbl.CourseName as SelfPlanName,
YEAR (
dbo.ClassHourEx_Tbl.TestTime
) AS [year],
dbo.ClassHourEx_Tbl.Score as Score,
dbo.ClassHourEx_Tbl.Type as Type,
dbo.ClassHourEx_Tbl.IsFinish as IsClassHourGet,
CASE dbo.ClassHourEx_Tbl.IsFinish WHEN 0 THEN 0 WHEN 1 THEN dbo.ClassHourEx_Tbl.ClassHour END AS ClassHourGet
FROM
dbo.ClassHourEx_Tbl,
dbo.BM_Staff_Tbl
INNER JOIN dbo.BM_Staff_Tbl ON dbo.ClassHourEx_Tbl.UserId = dbo.BM_Staff_Tbl.UserName
同时第二个查询多写了个dbo.BM_Staff_Tbl 用inner Join时候不需要在from後给多个表
建议给查询表加别名，这样看起来清爽直观些
写成
SELECT
cs.UserId as UserId,
cs.UserName as UserName,
cs.UpdateTime as UpdateTime,
cs.ClassHour as ClassHour,
cs.SelfPlanName as SelfPlanName,
cs.[year] as [year],
cs.Score as Score,
cs.Type as Type,
cs.IsFinish as IsClassHourGet,
CASE cs.IsFinish WHEN 0 THEN 0 WHEN 1 THEN cs.ClassHour END AS ClassHourGet
FROM
dbo.View_CheckSelfNote as cs
UNION
SELECT
st.StaffId as UserId,
st.UserName as UserName,
he.TestTime as UpdateTime,
he.ClassHour as ClassHour,
he.CourseName as SelfPlanName,
YEAR ( he.TestTime) AS [year],
he.Score as Score,
he.Type as Type,
he.IsFinish as IsClassHourGet,
CASE he.IsFinish WHEN 0 THEN 0 WHEN 1 THEN he.ClassHour END AS ClassHourGet
FROM
dbo.ClassHourEx_Tbl as he
INNER JOIN dbo.BM_Staff_Tbl as st ON he.UserId = st.UserName
dbo.ClassHourEx_Tbl,
dbo.BM_Staff_Tbl
INNER JOIN dbo.BM_Staff_Tbl ON dbo.ClassHourEx_Tbl.UserId = dbo.BM_Staff_Tbl.UserName
这里出错了，应该这样：
FROM
dbo.ClassHourEx_Tbl
INNER JOIN dbo.BM_Staff_Tbl ON dbo.ClassHourEx_Tbl.UserId = dbo.BM_Staff_Tbl.UserName
