如何创建表然后提交然后插入然后提交和删除表?

问题描述

我在提交数据库时遇到问题.我是Firebird SQL的新手.但是我有一个查询，该查询创建一个表，然后提交它，然后将其插入，提交它，查看数据，删除该表，然后再次提交.我正在尝试动态地执行此操作，在此我可以单击一次执行按钮，并且它会一直执行下去.有没有办法做到这一点?这是查询.

Hi I am having a problem committing to a database. I am very new to Firebird SQL. But I have a query that creates a table, then commits it, then inserts it, commits it, view the data, drop the table and then commit it again. I am trying to do this dynamically where I can hit the execute button once and it just follows through. Is there a way to do that? This is the query.

create table bp_customCollections(
       part_number varchar(255)
       ,part_description varchar(255)
       ,guided_reading_level varchar(255)
       ,lexile_level varchar(255)
       ,nonfiction_fiction varchar(255)
       ,on_hand varchar(255)
       ,default_vendor varchar(255)
       ,language varchar(255)
       ,product_price varchar(255)
       )

commit

insert into bp_customcollections(part_number, part_description,guided_reading_level,lexile_level,nonfiction_fiction, on_hand, default_vendor,language,product_price)
SELECT DISTINCT
part.num
,part.description
,COALESCE(grlmin.info,'Undefined')
,getLexile.lexile_level
,COALESCE(fnf.info,'')
,SUM(tag.qty)
,vendor.name
,customvarcharlong.info
,cast(product.price as decimal(10,2))

FROM PART

LEFT JOIN customset AS grlmin ON grlmin.recordid = part.id AND grlmin.customfieldid = 51 -- guided reading level minimum
LEFT JOIN getLexile on getLexile.lexile_partid = part.id and getLexile.lexile_partid = 61 -- lexile level
LEFT JOIN vendorparts on vendorparts.partid = part.id
LEFT JOIN vendor on vendor.id = vendorparts.vendorid
LEFT JOIN customset AS fnf on fnf.recordid = part.id AND fnf.customfieldid = 47  -- fiction/nofiction
LEFT JOIN tag on part.id = tag.partid
LEFT JOIN customvarcharlong on customvarcharlong.recordid = part.id and customvarcharlong.customfieldid = 56 -- language
LEFT JOIN product on product.partid = part.id
LEFT JOIN customset as resourceType on resourcetype.recordid = part.id
where vendorparts.defaultflag = 1
and resourcetype.info = 'Book'
GROUP BY part.num,part.description, COALESCE(grlmin.info,'Undefined'), COALESCE(fnf.info,''),getLexile.lexile_level, vendor.name, customvarcharlong.info,product.price
HAVING SUM(tag.qty) > 0

commit


select * from bp_customcollections
order by part_number

drop table bp_customCollections
commit

推荐答案

创建视图，然后在需要时从视图中进行选择会不会更容易?

Would it not be easier to create a view, and then select from it whenever you need to?

create view vw_customCollections as
SELECT DISTINCT
part.num
,part.description
,COALESCE(grlmin.info,'Undefined')
,getLexile.lexile_level
,COALESCE(fnf.info,'')
,SUM(tag.qty)
,vendor.name
,customvarcharlong.info
,cast(product.price as decimal(10,2))
FROM PART
LEFT JOIN customset AS grlmin ON grlmin.recordid = part.id AND grlmin.customfieldid = 51 -- guided reading level minimum
LEFT JOIN getLexile on getLexile.lexile_partid = part.id and getLexile.lexile_partid = 61 -- lexile level
LEFT JOIN vendorparts on vendorparts.partid = part.id
LEFT JOIN vendor on vendor.id = vendorparts.vendorid
LEFT JOIN customset AS fnf on fnf.recordid = part.id AND fnf.customfieldid = 47  -- fiction/nofiction
LEFT JOIN tag on part.id = tag.partid
LEFT JOIN customvarcharlong on customvarcharlong.recordid = part.id and customvarcharlong.customfieldid = 56 -- language
LEFT JOIN product on product.partid = part.id
LEFT JOIN customset as resourceType on resourcetype.recordid = part.id
where vendorparts.defaultflag = 1
and resourcetype.info = 'Book'
GROUP BY part.num,part.description, COALESCE(grlmin.info,'Undefined'), COALESCE(fnf.info,''),getLexile.lexile_level, vendor.name, customvarcharlong.info,product.price
HAVING SUM(tag.qty) > 0

然后:

select *
from   vw_customCollections
order by  part_number

这篇关于如何创建表然后提交然后插入然后提交和删除表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！