SQL 怎么实现类For循环功能-MSSQL教程-爱易网页

SQL 怎么实现类For循环功能

日期：2014-05-18　浏览次数：20566 次

SQL 如何实现类For循环功能
已知三张表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 a
cross apply(select top percent 40 * 
      from Test_Services where U

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

SQL 怎么实现类For循环功能

相关资料更多>

推荐阅读更多>