<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ??一站式輕松地調用各大LLM模型接口,支持GPT4、智譜、豆包、星火、月之暗面及文生圖、文生視頻 廣告
                **1. 查找所有至少連續出現三次的數字** 練習用表: ```sql select * from logs; +----------+-----------+--+ | logs.id | logs.num | +----------+-----------+--+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----------+-----------+--+ ``` 方法一:借助id有序,通過子關聯進行查詢 ```sql select distinct a.num as ConsecutiveNums from logs a join logs b join logs c on a.num=b.num and b.num=c.num where a.id=b.id-1 and b.id=c.id-1; -- 或者改寫如下 select distinct a.num ConsecutiveNums from logs a join logs b on a.num=b.num and a.id=b.id-1 join logs c on b.num=c.num and b.id=c.id-1; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` 方法二:使用窗口函數rank() ```sql select distinct(b.ConsecutiveNums) as ConsecutiveNums from( select a.rn, a.num as ConsecutiveNums from( select num, (rank() over(order by id)-rank() over(partition by num order by id)) as rn from logs ) a group by a.rn, a.num having count(1)>2 )b; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` 方法三:使用窗口函數 lead(),當然也可以使用 lag() ```sql select distinct num ConsecutiveNums from( select num, lead(num, 1) over() as num1, lead(num, 2) over() as num2 from logs ) as c where c.num=num1 and c.num1=c.num2; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` **2. 換座位** 小美是一所中學的信息科技老師,她有一張 seat 座位表,平時用來儲存學生名字和與他們相對應的座位 id。 (1)其中縱列的 id 是連續遞增的 (2)小美想改變相鄰倆學生的座位。 你能不能幫她寫一個 SQL query 來輸出小美想要的結果呢? <br/> 練習用表: ```sql select * from seat; +----------+---------------+--+ | seat.id | seat.student | +----------+---------------+--+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----------+---------------+--+ ``` 方法一: ```sql select id, case when id%2=1 then lead(student, 1, student) over(order by id) when id%2=0 then lag(student, 1) over(order by id) end as student from seat; +-----+----------+--+ | id | student | +-----+----------+--+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +-----+----------+--+ ``` 方法二: ```sql -- ^ 異或運算,偶數與1異或則+1,奇數則-1 select rank() over(order by (id-1)^1) as id, student from seat; +-----+----------+--+ | id | student | +-----+----------+--+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +-----+----------+--+ ```
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看