选择被分组的两个记录，然后将它们放在旁边的两列中

In a table we have schools' data:

在一张表格中，我们有学校的数据:

 ID | Name | City
------------------  
  1    A     X          
  2    B     X    
  3    C     Z
  4    D     Z

I want to have a list of each two schools that are in the same city:

我想要一份同一城市的两所学校的名单:

Name1 | Name2
--------------  
  A      B          
  C      D

I chose schools in the same city with this:

我选择了同一城市的学校:

 SELECT Name FROM Schools
    Group by City
    Having City = City

Is it correct? How to bring 2 matched schools on a new table along side?

是正确的吗?如何将两所匹配的学校放在一个新桌子旁?

Thanks

谢谢

3 个解决方案

#1

UPDATE Another way to do it

更新另一种方法

One way to do that if you insist on grouping

如果你坚持分组，有一种方法

SELECT City,
       MIN(Name) Name1,
       MAX(Name) Name2
  FROM Schools
 GROUP BY City
-- HAVING COUNT(*) > 1

Another way

另一种方式

SELECT City,
       MIN(CASE WHEN rnum = 1 THEN Name END) Name1,
       MIN(CASE WHEN rnum = 2 THEN Name END) Name2
  FROM
(
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) rnum
    FROM Schools s
) q
 GROUP BY City

Sample output:

样例输出:

| CITY | NAME1 | NAME2 |
------------------------
|    X |     A |     B |
|    Z |     C |     D |

Here is SQLFiddle demo

这是SQLFiddle演示

#2

SELECT a.Name, b.Name
FROM Schools a JOIN Schools b ON a.City = b.City 
          AND a.ID != b.ID 
          AND a.Name < b.Name;

This returns all the pairs of schools in the same city, not only a pair for each city.

这将返回同一城市的所有对学校，而不是每个城市的一对。

#3

If a city contains more than 2 schools then the following query gives no guarantees as to which 2 schools will be returned:

如果一个城市包含2所以上的学校，那么下面的查询不能保证哪2所学校将被归还:

SELECT      S1.Name,
            S2.Name
FROM        (
                SELECT      MIN(ID) ID,
                            City
                FROM        @Schools
                GROUP BY    City
            ) S
INNER JOIN  @Schools S1 ON S.ID = S1.ID
INNER JOIN  @Schools S2 ON ( S.City = S2.City AND S.ID != S2.ID )

#1