组合许多选择查询的结果集 [英] combining resultset of many select queries

问题描述

我对四个不同的表有四个 Select 查询，每个查询只提取一条记录.例如:

I have four Select queries for four different tables, each extracting only one record. For example:

Select * from table where col1 = 'something'

给出一行有 3 列.

第二个选择查询也给出了一个具有两列(字段)的记录.第三个和第四个选择查询相同.

The second select query also gives one record having two columns(fields). Same for third and fourth select query.

我想将所有四个结果集合并为一行.怎么可能?

I want to combine all four result sets into one having one row. How is it possible?

我会为您编写查询.

第一个:

Select Top 1 column1, column2
from table 1
where column 1 = 'something'
  and col1 = (Select max(col1) where column 1 = 'something')

第二个查询:

Select Top 1 column1, column3
from table 2
where column 1 = 'something'
  and column3 = (Select max(column3) where column 1 = 'something')   

第三个查询使用从查询 2 获得的结果:

3rd query uses the result obtained from query 2:

Select column4
from table 3
where column3 = (obtained from 2nd query)  (there is only one row)

第四:

Select column5
from table 4
where column3 = (obtained from 2nd query) (there is only one row)

这意味着我必须加入第 2、3、4 个查询，然后结果集在第 1 个.

This means I have to join 2nd, 3rd, 4th query, then resulting set in 1st.

我不能使用联合，因为列不同.

I can't use union since columns are different.

所以唯一的问题是加入结果集.

So only problem is with joining the result set.

推荐答案

您可以使用 CROSS JOIN 来完成此操作.

You can use CROSS JOINs to accomplish this.

CREATE TABLE table1 (id int, column1 varchar(5), column2 varchar(15));

CREATE TABLE table2 (column3 varchar(5), column4 varchar(15));

CREATE TABLE table3 (id int, column5 varchar(5), column6 varchar(15));

INSERT INTO table1 VALUES (1, 'aaa', 'row1')
INSERT INTO table2 VALUES ('bbb', 'table2')
INSERT INTO table3 VALUES (1, 'ccc', 'table3')
INSERT INTO table1 VALUES (1, 'ddd', 'table1')

SELECT * FROM (SELECT * FROM table1) a
           CROSS JOIN (SELECT * FROM table2) b 
           CROSS JOIN (SELECT * FROM table3) c

结果:

id    column1    column2   column3    column4    id    column5    column6
1     aaa        row1      bbb        table2     1     ccc        table3
1     ddd        table1    bbb        table2     1     ccc        table3

<小时>

澄清后更新:

---

Update after clarification:

CREATE TABLE table1 
(
        id int IDENTITY(1,1) 
        , searchstring nvarchar(25)
);

CREATE TABLE table2 
(
        id2 int IDENTITY(10, 10) 
        , searchstring2 nvarchar(25)
        , newsearchstring nvarchar(50)
);

CREATE TABLE table3
(
        id3 int IDENTITY(100, 100) 
        , id2 int
        , table3srow nvarchar(25)
)

INSERT INTO table1 VALUES ('something');
INSERT INTO table1 VALUES ('something else');
INSERT INTO table1 VALUES ('something'); -- ID = 3, this row will be selected by 1st query

INSERT INTO table2 VALUES ('something', 'newvalue1');
INSERT INTO table2 VALUES ('something else', 'this will not be shown');
INSERT INTO table2 VALUES ('something', 'this will be returned by query 2'); -- ID = 30, this row will be selected by 2nd  query

INSERT INTO table3 VALUES (10, 'not relevant');
INSERT INTO table3 VALUES (20, 'not relevant');
INSERT INTO table3 VALUES (30, 'This is from table 3'); -- This  row will be returned by 3rd query


SELECT * FROM 
    (SELECT TOP 1 id, searchstring FROM table1 WHERE searchstring = 'something'  and id =     (SELECT MAX(id) FROM table1 WHERE searchstring = 'something')) AS  query1,
    (SELECT TOP 1 id2, newsearchstring FROM table2 WHERE searchstring2 = 'something'  and id2 = (SELECT MAX(id2) FROM table2 WHERE searchstring2 = 'something')) AS  query2,
    (SELECT id2, table3srow FROM table3) as query3 
WHERE query3.id2 = query2.id2

对 table4 使用与 table3 相同的方法.

Use the same approach for table4 as indicated for table3.

这篇关于组合许多选择查询的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！