限制列值的插入 [英] Limit insert of column value

问题描述

我在下面的查询中正确插入了它的编写方式。但是，我需要修改为只插入列'ufid'设置次数的值。例如，该值应仅在表中40次。



例如，如果ufid ='K7'。我需要限制'K7'可以插入的次数。如果'K7'已经插入数据库40次，那么应该根据where子句标准选择下一个ufid。想法？请

I have the following query below that is inserting correctly the way it is written. However, I need to modify to only insert the value of column 'ufid' set number of times. For example, the value should only be in the table 40 times.

For example if the ufid = 'K7'. I need to limit the number of times the 'K7' can be inserted. If 'K7' has already been inserted into the database 40 times, then the next ufid should be selected based on the where clause criteria. Ideas?? Please

<pre>WITH Match_NomineesWithReviewers AS
(
    SELECT 
        [AppID], 
        RTRIM(Major) AS Major,
        COUNT(1) AS rowcnt
    FROM  
        #mult_nuf
    GROUP BY
        [AppID], 
        RTRIM(Major)
)
, rownum_matches AS (
SELECT 
    m.[AppID], 
    r.ufid,
    m.rowcnt,
    ROW_NUMBER() OVER (PARTITION BY m.[AppID] ORDER BY newid()) AS rownum
FROM 
    Match_NomineesWithReviewers m
JOIN
    #temp_rUF t ON t.sh_Plan != m.Major
 )
INSERT into #TempNTable
SELECT [AppID], ufid FROM rownum_matches rm WHERE rownum <= rowcnt

我的尝试：



我宁愿使用不同的光标替代品，但我正在画一个空白。我没试过这个游标（更新查询被注释掉，因为它写得不正确）：



DECLARE @ReviewerCount CURSOR

DECLARE @New_ufID varchar（8）

SET @ReviewerCount = CURSOR FAST_FORWARD FOR

从#TempNTable中选择ufid

group by ufid

有计数（ufid）> 40;



打开@ReviewerCount



FETCH NEXT来自@ReviewerCount INTO @New_ufID

设置@RevCursorStatus = @@ FETCH_STATUS



WHILE @RevCursorStatus = 0开始





声明@OverageReviewers int

set @OverageReviewers =（SELECT COUNT（A.ufid）FROM

（SELECT COUNT（ufid）AS uf_id FROM #TempNTable

GROUP BY ufid

HAVING COUNT（ufid）> 40）as a

GROUP BY ufid）





while（@OverageReviewers> 0）BEGIN





- -UPDATE #TempNTable

--SET UFID = @New_ufID

- from #temp_rUF t

- ON t.short_plan！= major ）

--SET @OverageReviewers = @OverageReviewers - 1

--set @RevCursorStatus = -1



结束

END

What I have tried:

I would rather use a different alternative to a cursor but I am drawing a blank. I have tried this cursor with no luck (THE Update query is commented out because it is written incorrectly):

DECLARE @ReviewerCount CURSOR
DECLARE @New_ufID varchar(8)
SET @ReviewerCount = CURSOR FAST_FORWARD FOR
SELECT ufid from #TempNTable
group by ufid
having count(ufid) > 40;

open @ReviewerCount

FETCH NEXT FROM @ReviewerCount INTO @New_ufID
set @RevCursorStatus = @@FETCH_STATUS

WHILE @RevCursorStatus = 0 begin


declare @OverageReviewers int
set @OverageReviewers = (SELECT COUNT (A.ufid) FROM
(SELECT COUNT(ufid) AS uf_id FROM #TempNTable
GROUP BY ufid
HAVING COUNT(ufid) > 40) as A
GROUP BY ufid)


while (@OverageReviewers > 0) BEGIN


--UPDATE #TempNTable
--SET UFID = @New_ufID
--from #temp_rUF t
-- ON t.short_plan != major)
--SET @OverageReviewers = @OverageReviewers - 1
--set @RevCursorStatus = -1

end
END

推荐答案

我可能会建议使用用户定义的函数，用于确定用于ufid的值。

据我所知，用户定义函数不需要任何参数，可以写成直接的Select语句来返回标量值。

请查看以下链接，了解有关UDF的信息

[ MSDN - 用户自定义函数]



亲切问候

I would probably recommend using a User Defined Function to determine the value to use for your ufid.
From what I understand the User Defined Function would not need any parameters and could be written as a straight Select statement to return a scalar value.
Take a look at the below link for information on UDF's
[MSDN - User Defined Functions]

Kind Regards

你可以符合SQL中某些条件的 SELECT 中的 INSERT 。试试这个：

You can INSERT from a SELECT that meets certain conditions in SQL. Try this:

INSERT INTO tablename(ufid, otherfields) 
SELECT ufid, otherfields From tablename where ufid='k7' GROUP BY ufid, otherfields HAVING COUNT(ufid) < 40

这篇关于限制列值的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！