用到两个表,teacher和student。
teacher的字段和值如下：id(bigint),name(nvarchar),status(smallint)
id name status
1 王老师 0
2 李老师 1
3 余老师 1
4 张老师 2
student的字段和值如下：id(bigint),name(nvarchar),teacherId(bigint)
id name teacherId
1 张三 1
2 李四 1
3 王五 2
4 宋六 1
5 郑七 2
我用这个SQL语句时出现converting data type nvarchar to bigint.怎么解决？
- SQL code
delete from student where teacherId in (select stuff(select ','+cast(id as varchar) from teacher where status=1),1,1,''))
------解决方案--------------------
- SQL code
--不过没看明白什么意思，呵呵delete from student where teacherId in (select stuff((--少个括号select ','+cast(id as varchar) from teacher where status=1),1,1,''))
------解决方案--------------------
- SQL code
--按你的想法呢，其实你可以这样写，没必要整这么复杂delete student where exists (select 1 from teacher where teacher.id=student.teacherId and status=1);--ordelete student where teacherId in (select id from teacher where status=1);
------解决方案--------------------
你哪个要拼接成动态的才可以。
------解决方案--------------------
- SQL code
delete from student where teacherId in (select id from teacher where status=1);