GROUP BY 使用不在 SELECT 列表中的列 - 這什麼時候實用、優雅或強大？

TL;DR - 我想要使用列表中GROUP BY未列出的列的範例（範例如下）SELECT可用於解決 SQL 挑戰

$$ practical | elegant | powerful $$方式。我的意思是籠統地講——下面展示的範例很有趣，因為它展示了原理（但它不起作用！）。我想要一些可以使用這種技術來實現“重要”的工作範例。 按照這個問題，這需要計算從 point_x 到 point_y 的路線，反之亦然，採取相同點之間的路線是等效的 - 即A -> B與計數目的相同B -> A。那裡給出了該問題的通用解決方案。

但是，一個海報（SQLRaptor）展示了一個解決方案，該解決方案解決了問題中顯示的（可能）數據子集的問題，但該海報還說他們的解決方案並不通用，並詢問 OP 他們能明白為什麼嗎？SQLRaptor 還說這是一個使用不在SELECT列表中的列的 GROUP BY 範例，這可能是一些 SQL 挑戰的優雅（強大）解決方案！

SQLRaptor 的解決方案（使用我自己的答案符號）是：

SELECT  MIN(origin) AS point_1, 
       MAX(destination) AS point_2,
       COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY point_1, point_2 

我認為非普遍性可能與乘法（重複）有關，但沒有具體證據。事實就是如此 - 請參閱我的證明here和here。

不幸的是，這個GROUP BY使用不在SELECT列表中的列的例子效果不佳——我想要的是這種技術在哪裡可以很好地工作的例子？

我使用了這種技術（在 SELECT 列表中未使用的列上進行分組）將有序的值列表（有間隙）組合成連續值的範圍。

首先，我對有序的值集執行 rownum() 。然後我們區分 rownum() 和值。通過對差異進行分組，我們得到了範圍。

CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP 
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 6 UNION 
SELECT 8 UNION 
SELECT 9 UNION 
SELECT 10 UNION 
SELECT 15 UNION 
SELECT 16 UNION 
SELECT 17 UNION 
SELECT 18 UNION 
SELECT 19 UNION 
SELECT 20



WITH RN AS 
(
   SELECT 
       ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID 
   FROM 
       #TMP
),
SRC AS 
(
   SELECT 
       RN, 
       ID, 
       ID-RN DIFF 
   FROM 
       RN
)
SELECT 
   MIN(ID) RANGE_START, 
   MAX(ID) RANGE_END, 
   COUNT(*) CNT_VALUES_IN_RANGE 
FROM 
   SRC 
GROUP BY 
   DIFF

查詢失敗的簡單範例：

CREATE TABLE Routes
( ID INT NOT NULL,
  ORIGIN VARCHAR(2) NOT NULL,
  DESTINATION VARCHAR(2) NOT NULL
);

 INSERT INTO Routes
   ( ID, ORIGIN, DESTINATION )
 VALUES 
   ( 1, 'A', 'T' ),
   ( 2, 'F', 'N' ) ;
GO

2 行受影響

SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;
GO

點_1 | 點_2 | 旅程計數
:------ | :------ | ------------:
一個 | T | 2

db<>在這裡擺弄

---

失敗的解釋：

字母 ASCII 碼數學

A 65 = (5 * 13)

T 84 = (6 * 14)

F 70 = (5 * 14)

N 78 = (6 * 13)

AT –> 65 * 84 = (513) * (614) = (514) * (613) = 70 * 84 <– FN

引用自：https://dba.stackexchange.com/questions/208703