我如何改进我的工会声明 [英] How do I improve my union statement

问题描述

如果用户按某些标准搜索，我正在编写一个sql脚本来返回数据

*供应商

*审批人

* Capturer



我有2张桌子已加入

1位用户/供应商



用户属于确定他们是批准者还是捕获者的群组

群组描述

1审批者

2- Capturer


现在我遇到的问题是在尝试过滤数据时



所以如果用户想要搜索供应商和批准者，但限制捕获者我有一个轻微的问题。我的脚本是我在下面尝试过的。



我怎样才能改进我的剧本



我尝试了什么：

I am currently writing a sql script to return data if a user searches by certain criteria
*Supplier
*Approver
*Capturer

I have 2 tables which are joined
1 users/supplier

the users belong to a group which would determine if they are approver or capturer
Groupid Description
1 approver
2- Capturer

now the issue i am having is when trying filter data

so if the user wants to search for a supplier and an approver but limit the capturer i have a slight issue.my script is in the what have i tried below.

How could i improve my script

What I have tried:

Select Supplier.id,Supplier.SupplierDescription,User.Username Approver,''capturer
from supplier,user
where user.groupid = 1 --Approver
Union all 
Select Supplier.id,Supplier.SupplierDescription,''Approver,user.Username Capturer
from supplier,user
where user.groupid = 2 --Capturer

现在如果用户决定他们会喜欢搜索所有供应商和1个批准者和所有捕获者



这是我目前拥有的脚本

Now if the user decided they would like to search for all suppliers and 1 approver and all capturers

this is the script i currently have

Select Supplier.id,Supplier.SupplierDescription,User.Username Approver,''capturer
from supplier,user
where user.groupid = 1 --Approver
and user.Userkey = 150
Union all 
Select Supplier.id,Supplier.SupplierDescription,''Approver,user.Username Capturer
from supplier,user
where user.groupid = 2 --Capturer
and supplier.id in(Select Supplier.id
from supplier,user
where user.groupid = 1 --Approver
and user.Userkey = 150)

推荐答案

创建一个更快的视图。

这篇关于我如何改进我的工会声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！