谢谢大家帮忙,现在把需求重新写一下表1 create table service ( serviceID int, serviceName varchar(50) )表2 create table orderservice ( id int, servicesID varchar(50), statu int )表1里面的记录 serviceID serviceName 1 服务1 2 服务2 3 服务3 现在有一条记录 id servicesID statu 1 1,2,5,8 ,9 1 客户想关闭其中几个服务,假设是5和9 我希望得到这样的结果 id servicesID statu 1 1,2,8 1(表示开) 2(自增的) 5,9 0(表示关)请大家帮帮忙,这个存储过程怎么写
扩充了几个字段,以下环节纯属虚构create table service ( serviceID int, serviceName varchar(50) ) create table orderservice ( id int IDENTITY(1,1), servicesID varchar(50), statu int, runDate datetime ) insert into service SELECT 1,'服务1' UNION ALL SELECT 2,'服务2' UNION ALL SELECT 3,'服务3' UNION ALL SELECT 4,'服务4' UNION ALL SELECT 5,'服务5' UNION ALL SELECT 6,'服务6' UNION ALL SELECT 7,'服务7' UNION ALL SELECT 8,'服务8' UNION ALL SELECT 9,'服务9' INSERT INTO orderservice SELECT '1,2,5,8,9',1,'2005-08-13' UNION ALL SELECT '2,3,5,7,9',1,'2005-09-14' UNION ALL SELECT '6,7,8,9',1,'2005-11-14' GO--创建合并函数, CREATE FUNCTION F_UNION( @in VARCHAR(50), -- 输入的servicesID @out VARCHAR(50),-- 关闭的servicesID @status INT -- 要得到最终状态的servicesID ) RETURNS VARCHAR(50) AS BEGIN DECLARE @re VARCHAR(200) SET @re = '' DECLARE @t TABLE ( id INT IDENTITY(1,1), m BIT) INSERT INTO @t SELECT TOP 50 0 FROM sysobjects IF @status = 1 SELECT @re = @re + ',' + S FROM ( SELECT S=SUBSTRING(@in, id, CHARINDEX(',',@in+',',id)-id) FROM @t WHERE CHARINDEX(',',','+@in,id) = id AND id <= LEN(@in) ) A WHERE S NOT IN ( SELECT S=SUBSTRING(@out, id, CHARINDEX(',',@out+',',id)-id) FROM @t WHERE CHARINDEX(',',','+@out,id) = id AND id <= LEN(@out) ) ELSE SELECT @re = @re + ',' + S FROM ( SELECT S=SUBSTRING(@in, id, CHARINDEX(',',@in+',',id)-id) FROM @t WHERE CHARINDEX(',',','+@in,id) = id AND id <= LEN(@in) ) A WHERE S IN ( SELECT S=SUBSTRING(@out, id, CHARINDEX(',',@out+',',id)-id) FROM @t WHERE CHARINDEX(',',','+@out,id) = id AND id <= LEN(@out) ) RETURN STUFF(@re,1,1,'') END GO-- 更新语句,你可以用一个存储过程来包括 INSERT INTO orderservice SELECT DBO.F_UNION(servicesID,'5,9',0),0,runDate FROM orderservice UPDATE orderservice SET servicesID = DBO.F_UNION(servicesID,'5,9',1) WHERE statu = 1 --显示结果 SELECT * FROM orderservice
--删除环境 DROP FUNCTION F_UNION DROP TABLE service,orderservice id servicesID statu runDate ----------- -------------------------------------------------- ----------- ------------------------------------------------------ 1 1,2,8 1 2005-08-13 00:00:00.000 2 2,3,7 1 2005-09-14 00:00:00.000 3 6,7,8 1 2005-11-14 00:00:00.000 4 5,9 0 2005-08-13 00:00:00.000 5 5,9 0 2005-09-14 00:00:00.000 6 9 0 2005-11-14 00:00:00.000(所影响的行数为 6 行)
create table tb (servicesID int, statue int)
insert into tb values(1,1)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,1)
goinsert into tb select top 2 servicesid , statue = 0 from tb order by newid()select * from tbdrop table tb/*
servicesID statue
----------- -----------
1 1
2 1
3 1
4 1
4 0
1 0(所影响的行数为 6 行)
*/
这是什么意思
create table service
( serviceID int,
serviceName varchar(50)
)表2
create table orderservice
(
id int,
servicesID varchar(50),
statu int
)表1里面的记录
serviceID serviceName
1 服务1
2 服务2
3 服务3
现在有一条记录
id servicesID statu
1 1,2,5,8 ,9 1
客户想关闭其中几个服务,假设是5和9
我希望得到这样的结果
id servicesID statu
1 1,2,8 1(表示开)
2(自增的) 5,9 0(表示关)请大家帮帮忙,这个存储过程怎么写
扩充了几个字段,以下环节纯属虚构create table service
( serviceID int,
serviceName varchar(50)
)
create table orderservice
(
id int IDENTITY(1,1),
servicesID varchar(50),
statu int,
runDate datetime
)
insert into service
SELECT 1,'服务1' UNION ALL
SELECT 2,'服务2' UNION ALL
SELECT 3,'服务3' UNION ALL
SELECT 4,'服务4' UNION ALL
SELECT 5,'服务5' UNION ALL
SELECT 6,'服务6' UNION ALL
SELECT 7,'服务7' UNION ALL
SELECT 8,'服务8' UNION ALL
SELECT 9,'服务9' INSERT INTO orderservice
SELECT '1,2,5,8,9',1,'2005-08-13' UNION ALL
SELECT '2,3,5,7,9',1,'2005-09-14' UNION ALL
SELECT '6,7,8,9',1,'2005-11-14' GO--创建合并函数,
CREATE FUNCTION F_UNION(
@in VARCHAR(50), -- 输入的servicesID
@out VARCHAR(50),-- 关闭的servicesID
@status INT -- 要得到最终状态的servicesID
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @re VARCHAR(200)
SET @re = ''
DECLARE @t TABLE ( id INT IDENTITY(1,1), m BIT)
INSERT INTO @t SELECT TOP 50 0 FROM sysobjects IF @status = 1
SELECT @re = @re + ',' + S FROM
(
SELECT S=SUBSTRING(@in, id, CHARINDEX(',',@in+',',id)-id)
FROM @t WHERE CHARINDEX(',',','+@in,id) = id AND id <= LEN(@in)
) A
WHERE S NOT IN
(
SELECT S=SUBSTRING(@out, id, CHARINDEX(',',@out+',',id)-id)
FROM @t WHERE CHARINDEX(',',','+@out,id) = id AND id <= LEN(@out)
)
ELSE
SELECT @re = @re + ',' + S FROM
(
SELECT S=SUBSTRING(@in, id, CHARINDEX(',',@in+',',id)-id)
FROM @t WHERE CHARINDEX(',',','+@in,id) = id AND id <= LEN(@in)
) A
WHERE S IN
(
SELECT S=SUBSTRING(@out, id, CHARINDEX(',',@out+',',id)-id)
FROM @t WHERE CHARINDEX(',',','+@out,id) = id AND id <= LEN(@out)
) RETURN STUFF(@re,1,1,'')
END
GO-- 更新语句,你可以用一个存储过程来包括
INSERT INTO orderservice
SELECT DBO.F_UNION(servicesID,'5,9',0),0,runDate FROM orderservice
UPDATE orderservice SET servicesID = DBO.F_UNION(servicesID,'5,9',1) WHERE statu = 1
--显示结果
SELECT * FROM orderservice
--删除环境
DROP FUNCTION F_UNION
DROP TABLE service,orderservice id servicesID statu runDate
----------- -------------------------------------------------- ----------- ------------------------------------------------------
1 1,2,8 1 2005-08-13 00:00:00.000
2 2,3,7 1 2005-09-14 00:00:00.000
3 6,7,8 1 2005-11-14 00:00:00.000
4 5,9 0 2005-08-13 00:00:00.000
5 5,9 0 2005-09-14 00:00:00.000
6 9 0 2005-11-14 00:00:00.000(所影响的行数为 6 行)