问题描述
如何编写基于集合的查询?我有一个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的查询并避免使用游标?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!