本文介绍了如何编写基于Set的查询并避免使用游标?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何编写基于集合的查询?我有一个groupSets表和字段

setId,idField,datasource,nameField,prefix,active

数据:

1,someIDfield,someTable, someField,pre1,1

2,someotherIDfield,someTable,someotherField,pre2,1

3,somethirdIDfield,someTable,somethirdField,pre3,1

4,somefourthIDfield,someotherTable,somethourthField,pre4,1

我需要通过从组集合中的

数据构建查询来生成另一个表中的记录。我需要为查询的每个不同结果插入一条记录。

示例:

SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL


然后我需要为上述查询的每个结果插入一个插入


INSERT INTO groups(setId,groupName,active,groupingEntityID)

VALUES(1,前缀+ nameField,1,result1)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(1,前缀+ nameField,1,result2)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(1,前缀+ nameField,1,result3)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(1,前缀+ nameField,1,结果4)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(1,前缀+ nameField,1,resultN)


- 来自groupSets的下一个reord

SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS

NOT NULL

INSERT INTO groups(setId,groupName,active,groupingEntityID)

VALUES(2,前缀+ nameField,1,result1)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(2,前缀+ nameField,1,result2 )


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(2,前缀+ nameField,1,result3)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(2,前缀+ nameField,1,result4)


INSERT INTO组(setId,groupName,active,groupingEntityID)

VALUES(2,前缀+ nameField,1,resultN)

我基本上想做同样的操作groupSets中的每条记录

表。如何在没有光标的情况下完成此操作?有什么想法吗?

感谢您的帮助,

~ck

How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

I need to generate records in another table by constructing queries from the
data in groups sets. I need to insert a record for each distinct result of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL
INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)
I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,
~ck

推荐答案



不!这不是磁带或穿孔卡。我们编写了一个VIEW - 一个

的虚拟表,在调用时总是正确的。 VIEW还要

需要一个好的桌面名称 - 而不是群组,它们不会告诉我们什么样的东西是什么样的东西。/ br
的东西在那个集合中。 />

NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
virtual table that is always correct when invoked. The VIEW also
needs a good table name -- not "Groups", which don''t tell us what kind
of things is in that set.



我试图猜测这个操作,但决定你应该是描述它的
。没有钥匙,这是不可用的非

代码。

I played with trying to guess at this operation, but decided that you
should be the one describing it. Without keys, this is not usable non-
code.




这闻起来很腥。 groupSets应该可以用视图替换,

但构建该视图的正确方法取决于具体内容

所需(四个虚拟条目不多) 。

This smells fishy. groupSets should probably be replaced with a view,
but the proper way to build that view depends on the specific contents
desired (four dummy entries is not much to go on).



您的通用名称过于通用。如果你必须匿名化,但

可以区分不同的级别。


发布以下内容:

* SQL语句定义输入和输出表

*使用示例输入数据填充输入表的SQL语句

*您希望最终获得哪些特定输出数据,给出

这个输入数据

Your generic names are way too generic. Anonymize if you must, but
make it possible to distinguish between different levels.

Post the following:
* SQL statements to define input and output tables
* SQL statements to populate input tables with sample input data
* What specific output data you would want to end up with, given
this input data




不!这不是磁带或穿孔卡。我们编写了一个VIEW - 一个

的虚拟表,在调用时总是正确的。 VIEW还要

需要一个好的桌面名称 - 而不是群组,它们不会告诉我们什么样的东西是什么样的东西。/ br
的东西在那个集合中。 />


NO! This is not magnetic tapes or punch cards. We write a VIEW -- a
virtual table that is always correct when invoked. The VIEW also
needs a good table name -- not "Groups", which don''t tell us what kind
of things is in that set.



我试图猜测这个操作,但决定你应该是描述它的
。没有密钥,这是不可用的非

代码。


I played with trying to guess at this operation, but decided that you
should be the one describing it. Without keys, this is not usable non-
code.



这篇关于如何编写基于Set的查询并避免使用游标?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-21 10:42