先上图。
图里的关系。我已经好了。。
表:m_data数据是:
m_id,m_uid,m_specialid
241522 2 19
241516 2 19
241517 2 19
241519 2 19表:m_my_special数据是:
m_id,m_uid,m_videonum
17 1 2
18 2 1
19 2 4现在要进行更新m_my_special表
得的结果要如下:
m_id,m_uid,m_videonum
17 1 2
18 2 0
19 2 4两表的关系是:m_uid对应、
m_data表里的m_specialid对应m_my_special里面的m_id
这是昨天求职。一个坛友发的:
update m_my_special set m_videonum=isnull(b.rownum,0)
from m_my_special as a inner join (select m_uid,m_specialid,isnull(count(0),0) as rownum from m_data group by m_uid,m_specialid) as b on a.m_uid=b.m_uid and b.m_specialid=a.m_id And a.m_uid = 2现在出现个问题。
当m_data表里面m_specialid字段没有和m_my_special表里面的m_id有对应的时候。m_my_special表里面的m_videonum字段的值。
无法更新为0,如果有。则可以更新。怎么改上面的SQL语句啊。。
如果这样,则可以更新:
from m_my_special as a left join (select m_uid,m_specialid,isnull(count(0),0) as rownum from m_data group by m_uid,m_specialid) as b on a.m_uid=b.m_uid and b.m_specialid=a.m_id And a.m_uid = 2
无法更新为0,如果有。则可以更新。
left join 就可以了,楼主可否试帖。
SET m_videonum=(SELECT COUNT(1) FROM m_data WHERE m_uid=a.m_uid AND m_specialid=a.m_id)
FROM m_my_special AS a
WHERE EXISTS(SELECT 1 FROM m_data AS x
WHERE x.m_uid=a.m_uid
)