SQL动态处理下拉框信息以及利用游标合并指定条件的信息
1. 部门下拉框选择,数据库动态处理
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('tmp_y_and_r_depart_select') AND xtype='U')
DROP TABLE tmp_y_and_r_depart_select
DECLARE @x TINYINT,@y VARCHAR(1000)
CREATE TABLE tmp_y_and_r_depart_select(Depart VARCHAR(20))
SET @y='abcde aaaaaabbbbbb'
SET @x=0
WHILE @x<3
BEGIN
INSERT INTO tmp_y_and_r_depart_select
VALUES(SUBSTRING(@y,@x * 6 + 1,6))
SET @x = @x + 1
END
SELECT Depart FROM tmp_y_and_r_depart_select(NOLOCK) WHERE 1=1
2. 有单头档(tb_head),记录信息为ID,bID;单身档(tb_Detail),记录有ID,Line,Desc,isFlag 2.1. 判断‘isFlag'状态
判断isFlag
--"col1"表示板单号对应的bID条数,"col2"表示板单号isFlag的条数和;如果(col1-col2)=0表示该板单号isFlag=True,否则有欠的信息
SELECT a.banid
,COUNT(a.bID) AS col1
,SUM(a.isFlag) AS col2
,CAST((CASE WHEN COUNT(a.bID)-SUM(a.isFlag)=0 THEN 1 ELSE 0 END) AS BIT) AS isYes
FROM
(
SELECT a.bID
,CAST(ISNULL(b.isFlag,0) AS INT) isYes
FROM tb_head(NOLOCK) a LEFT JOIN
tb_detail(NOLOCK) b ON b.ID=a.ID
WHERE 1=1
)a
GROUP BY a.bID
2.2. 如果相同bID有欠'Desc'信息,则合并其信息
--统计欠Desc信息
SELECT DISTINCT a.bID,b.Desc
FROM tb_head(NOLOCK) a LEFT JOIN
tb_detail(NOLOCK) b ON b.ID=a.ID
WHERE ISNULL(b.isFlag,0)=0 AND ISNULL(a.bID,'')<>''
--利用游标合并信息
--创建记录合并相同板单号的物料名称描述信息表
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('tb_combine_desc') AND xtype='U')
DROP TABLE tb_combine_desc
GO
CREATE TABLE tb_combine_desc
(
bid VARCHAR(20) PRIMARY KEY NOT NULL,
desc VARCHAR(1000) NULL
)
GO
INSERT INTO tb_combine_desc
(
bid,
desc
)
VALUES
(
'aaaa',
'test'
)
DECLARE @bid VARCHAR(20),@desc VARCHAR(2000)
DECLARE cur_combine_desc CURSOR --定义合并相同bID的desc名称描述的游标
FOR
SELECT DISTINCT a.bID,b.Desc
FROM tb_head(NOLOCK) a LEFT JOIN
tb_detail(NOLOCK) b ON b.ID=a.ID
WHERE ISNULL(b.isFlag,0)=0 AND ISNULL(a.bID,'')<>''
OPEN cur_combine_desc --打开游标
FETCH NEXT FROM cur_combine_desc INTO @bid,@desc
WHILE @@FETCH_STATUS=0 --使用游标
BEGIN
--检查插入的记录是否有重复,如果重复,则修改desc描述信息
IF EXISTS(SELECT 1 FROM tb_combine_desc WHERE bid=@bid)
UPDATE tb_combine_desc SET
desc =a.desc+ ' ; '+@desc
FROM tb_combine_desc a
WHERE bid=@bid
ELSE
INSERT INTO tb_combine_desc
VALUES(@bid,@desc)
FETCH NEXT FROM cur_combine_desc INTO @bid,@desc
END
CLOSE cur_combine_desc --关闭游标
DEALLOCATE cur_combine_desc --释放游标
GO