表1 company 表2 gbsp09 表3 gbspi09
现在需要给张三分配一定数量的客户
update company set username='zhangsan' and fullname='张三' where company.name=gbsp09.companyname and company.name=gbspi09.companyname and username is null and fullname is null and resflag2='2'
用语句如何实现在语句后再加限制数量,比如TOP 100 *
现在需要给张三分配一定数量的客户
update company set username='zhangsan' and fullname='张三' where company.name=gbsp09.companyname and company.name=gbspi09.companyname and username is null and fullname is null and resflag2='2'
用语句如何实现在语句后再加限制数量,比如TOP 100 *
set username='zhangsan' , fullname='张三'
from (select top 30 * from gbsp09) a
where company.name=a.companyname
and company.name=a.companyname
and username is null and fullname is null and resflag2='2'
update company
set username='zhangsan' ,fullname='张三'
from (select top 100 a.id from username a ,gbsp09 b,gbspi09 c
where a.name=b.companyname
and a.name=c.companyname
and username is null
and fullname is null and resflag2='2') tb
where tb.id=a.id
a.id代表什么
username a ?username a不存在这个表我where 语句后面是三表查询的结果集
上述语句实现三表查询的结果集,已经测试成功可以查询出我想要的数据纪录
现在想实现的是在三表查询的结果集中随机选择100条分配给张三
求详细的SQL语句,高手帮忙~!
update company
set username='zhangsan' ,fullname='张三'
from
(select distinct top 100 id,name,gbsp09.companyname,gbspi09.companyname,tel,username,fullname
from company,gbsp09,gbspi09 where company.name=gbsp09.companyname
and company.name=gbspi09.companyname
and username is null and fullname is null and resflag2='2'
order by id asc) tb
where company.id=tb.id
tb是要自己定义一个?还是说就代表gbsp09,gbspi09??
服务器: 消息 8156,级别 16,状态 1,行 1
多次为 'tb' 指定了列 'companyname'。
top 100 distinct id,name,gbsp09.companyname,gbspi09.companyname,tel,username,fullname from company,gbsp09,gbspi09
where
company.name=gbsp09.companyname
and
company.name=gbspi09.companyname
and
username is null
and
fullname is null
and
resflag2='2'
order by
id asc,newid()
distinct top 100 id,name,gbsp09.companyname,gbspi09.companyname,tel,username,fullname from company,gbsp09,gbspi09
where
company.name=gbsp09.companyname
and
company.name=gbspi09.companyname
and
username is null
and
fullname is null
and
resflag2='2'
order by
id asc,newid()
update company
set username='zhangsan' ,fullname='张三'
from
(select distinct top 100 id,name,gbsp09.companyname,tel,username,fullname
from company,gbsp09,gbspi09 where company.name=gbsp09.companyname
and company.name=gbspi09.companyname
and username is null and fullname is null and resflag2='2'
order by id asc) tb
where company.id=tb.id