单一查询以选择数据 [英] Single query for Data selection

问题描述

我有两个表Jobs(name，job1，job2)和Users(name)，而我正在使用where集群获取此记录

1.

名称job1 job2
A 5 88
B 8 4
C 6 10
其中Dep =''Op''
2.

名称job1 job2
A 1 20
B 3 25
C 5 11
其中Dep =''SA''
但是在将两个条件都应用于
后，我想要如下所示的单个可提取数据
名称job1 job2 job1 job2
A 5 88 1 20
B 8 4 3 25
C 6 10 5 11


任何建议或帮助.

Hi,

I have two table Jobs(name,job1,job2) and Users(name), while i m using where cluase getting this records

1.

Name job1 job2
A 5 88
B 8 4
C 6 10
Where Dep=''Op''
2.

Name job1 job2
A 1 20
B 3 25
C 5 11
Where Dep=''SA''
But i want single fetchable data like below after applying both where Conditions

Name job1 job2 job1 job2
A 5 88 1 20
B 8 4 3 25
C 6 10 5 11


Any suggestion or Help.

推荐答案

从问题中可以看出，给出了两个表，
Jobs(name,job1,job2) and Users(name).但是在问题显示的结果中，给出了Where Dep=''Op''，从中看来，Jobs 表中还有一列Dep.此外，Users表不用于显示的结果.

如果Jobs表中有一列Dep ，则可以使用以下sql语句

As seen from the question two tables are given,
Jobs(name,job1,job2) and Users(name). But in the results shown in the question, the Where Dep=''Op'' is given, from which it appears that there is one more column Dep in the Jobs table. Further the Users table is not used for the results shown.

If there is a column Dep in the Jobs table then the following sql statement can be used

SELECT Jobs1.Name, Jobs1.Job1, Jobs1.Job2, Jobs2.Job1, Jobs2.Job2
FROM
    (SELECT Name, Job1, Job2 
     FROM Jobs
     WHERE Dep = 'Op')  Jobs1
JOIN
    (SELECT Name, Job1, Job2
     FROM Jobs
     WHERE Dep = 'SA') Jobs2
ON Jobs1.Name = Jobs2.Name

从对解决方案1的注释中可以看出，用户表中有Dep.相应地，查询被修改[/Edit]

From the comment given to the Solution 1, it is seen that Dep is available in Users Table. Accordingly the Query is modified [/Edit]

SELECT Jobs1.Name, Jobs1.Job1, Jobs1.Job2, Jobs2.Job1, Jobs2.Job2
FROM
    (SELECT Jobs.Name, Job1, Job2
     FROM Jobs
     JOIN Users ON Jobs.Name = Users.Name
     WHERE Users.Dep = 'Op')  Jobs1
JOIN
    (SELECT Jobs.Name, Job1, Job2
     FROM Jobs
     JOIN Users ON Jobs.Name = Users.Name
     WHERE Users.Dep = 'SA') Jobs2
ON Jobs1.Name = Jobs2.Name

select a.name ,a.job1,a.job2,b.job1,b.job from OP  a join SA b where a.Name=b.Name

使用以下查询.......

Use the following query.......

select a.name ,a.job1,a.job2,b.job1,b.job2 from Jobs  a join users b where a.Name=b.Name where a.Dep In('OP','SA') OR b.Dep in ('OP','SA')

祝你好运........

希望能有所帮助.....

Best of luck........

Hope it helps.....

这篇关于单一查询以选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！