已知三张表Test_user(员工表), Test_Services(服务表) 和Test_Visit(回访表)
现要随机抽取一段时间内每位员工服务量数据的40%插入到回访表,以进行回访。 求SQL语句
问：SQL 可否实现类For循环的函数？ 还是要用游标~ 望不吝赐教，多谢
执行以下SQL 构建测试数据：
- SQL code
--用户表CREATE TABLE Test_user (UserID varchar(50) NOT NULL,UserName varchar(50) NOT NULL,PRIMARY KEY (UserID))--服务表CREATE TABLE Test_Services(billno INT NOT NULL IDENTITY (1,1),UserID VARCHAR(50) NOT NULL,CreateTM VARCHAR(50),PRIMARY KEY (billno))-- 回访表CREATE TABLE Test_Visit(billno INT NOT NULL IDENTITY (1,1),UserID VARCHAR(50) NOT NULL,CreateTM VARCHAR(50),PRIMARY KEY (billno))-- 插入回访数据INSERT INTO Test_user (UserID,UserName) VALUES('XiaoE','小二')INSERT INTO Test_user (UserID,UserName) VALUES('ZhangS','张三')INSERT INTO Test_user (UserID,UserName) VALUES('Lis','李四')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-21 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-22 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-23 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-24 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-25 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-26 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-27 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-21 16:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-22 15:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-23 15:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-24 15:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-25 15:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('XiaoE','2012-03-26 15:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-21 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-21 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-21 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-21 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-23 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-22 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-23 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-22 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('ZhangS','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-24 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-25 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-26 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-27 10:04:57')INSERT INTO Test_Services (UserID,CreateTM) VALUES('Lis','2012-03-28 10:04:57')------解决方案--------------------
while可以实现for循环
------解决方案--------------------
- SQL code
insert into Test_Visit select distinct b.* from Test_user across apply(select top percent 40 * from Test_Services where UserID=a.UserID and CreateTM between @bengintime and @endtime order by newid()) b