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