SQL Server中UNPIVOT的妙用

事情的起因

力扣传送门

题解

这个题目就两个点，1.人数大于100 2.id是连续的，且连续3次或者以上，所以难点就是你如何去判断出连续的3个id数据

我的解

WITH tre AS(
SELECT a.id AS aid,b.id AS bid,c.id AS cid FROM Stadium a LEFT JOIN Stadium b ON a.id+1=b.id
LEFT JOIN Stadium c ON b.id+1=c.id
WHERE a.id!=b.id and b.id!=c.id AND a.people>=100 AND b.people>=100 AND c.people>=100
)

SELECT * FROM Stadium WHERE id IN(
SELECT DISTINCT ccc.orders FROM tre
UNPIVOT(
	orders for emp IN(aid,bid,cid)
)AS ccc
)
ORDER BY visit_date

我的解注解

写的比较粗糙，命名啥的也不是很规范哈，大家别太扣细节，基本上就跟做草稿一样，我的思路是先联表2次，这样可以保障得到3个连续的id，比如1，2，3和2，3，4都是合理的，然后再去重把这些id拿出来，这样就可以得到连续的id了，这个过程唯一的问题点就是行转列，然后我第一时间就想到了UNPIVOT，把三个表的id修改成列，这样就可以得到所有的id了

最优解

SELECT *
FROM Stadium
WHERE id IN
(
    SELECT id
    FROM
    (
        SELECT id, COUNT(*) OVER (PARTITION BY Diff) AS Diff
        FROM
        (
            SELECT id, visit_date, people, id - RowNum AS Diff
            FROM
            (
                SELECT *
                    ,ROW_NUMBER() OVER (ORDER BY id) AS RowNum
                FROM Stadium
                WHERE people >= 100
            ) X
        ) X                                                                                                                                                                                                                                                                                                                                             
    ) X
    WHERE Diff >= 3
)
ORDER BY visit_date

最优解注解

咋一看好像挺麻烦的，循环包循环的感觉，但是他这个思路其实很明确，就是利用连续的id，制造一个差值的排序，如果他们是连续的，那么连续的id减去差值就会等于同一个值，以后大家去判断连续的id的时候，都可以利用这种方法，这就是一种套路，跟算法思维很像，很推崇这种思维，算法思维肯定是找相似性，我做sql算法，主要是强化sql语法，所以我会侧重去用语法去解决问题，而不是带着算法思维，去找问题的相似性，这个大家要区分一下，但是如果有算法思维，那会更好，在遇到一些共性问题的时候，可以用相同的解决方案

图解