iT邦幫忙

0

MS SQL or Oracle 的SQL語法一問

請問在不使用T-SQL 和 PL SQL之下
單純使用SQL語法
有辦法算出D欄位的值嘛?
(C只是我想要的算法) 不再TABLE內
可以用什麼方式,謝謝

https://ithelp.ithome.com.tw/upload/images/20191216/20082456q378BDgz9q.jpg

2 個回答

5
一級屠豬士
iT邦高手 1 級 ‧ 2019-12-16 12:33:24
最佳解答
使用 Postgresql , 裡面使用到的 lag() , coalesce(). SQL Server, Oracle,
跟 Postgresql 都是相同的, MySQL 8 也是有 lag().

create table it191216 (
  id smallint generated always as identity
, cola int not null
);

insert into it191216 (cola)
select n
  from generate_series(8, 64, 8) as g(n);

insert into it191216 (cola) values (70);

select id
     , cola
     , cola - coalesce(lag(cola) over(order by id), 0) as cold
  from it191216 ;

+----+------+------+
| id | cola | cold |
+----+------+------+
|  1 |    8 |    8 |
|  2 |   16 |    8 |
|  3 |   24 |    8 |
|  4 |   32 |    8 |
|  5 |   40 |    8 |
|  6 |   48 |    8 |
|  7 |   56 |    8 |
|  8 |   64 |    8 |
|  9 |   70 |    6 |
+----+------+------+
(9 rows)

-----
-- 重點在於 要有排序的依據.
-- 沒有 lag() 時的作法. 因為我建立資料時已經有順序性的id了,
-- 若沒有,就要看是否有其他欄位,如時間等,依據來做排序取得排序序號.

select a.id
     , a.cola
     , a.cola - coalesce(b.cola, 0) as cold
  from it191216 a
  left join it191216 b
    on (a.id = b.id+1);

+----+------+------+
| id | cola | cold |
+----+------+------+
|  1 |    8 |    8 |
|  2 |   16 |    8 |
|  3 |   24 |    8 |
|  4 |   32 |    8 |
|  5 |   40 |    8 |
|  6 |   48 |    8 |
|  7 |   56 |    8 |
|  8 |   64 |    8 |
|  9 |   70 |    6 |
+----+------+------+
(9 rows)


Lag 函數要MSSQL2012以上支援

石頭 iT邦研究生 2 級 ‧ 2019-12-16 13:58:09 檢舉

如果沒有支援lag window function 可以使用subquery.

一級屠豬士
可以用了,謝謝
想在請問一下
例如有20個產品
要八個為一組
1~8要SHOW 8
9~16要SHOW 8
16~20 要SHOW 4 (尾數箱)
不能用where mod,因為需要帶出sn序號
用where會把中間序號略過
可以用什麼方法來做?

https://ithelp.ithome.com.tw/upload/images/20191216/20082456ystR8vrIbG.jpg

0
keeplearning
iT邦新手 5 級 ‧ 2019-12-16 18:01:00
create table t_test16(
 id int primary key
 ,c1 int 
 ,c2 int
);
insert into t_test16 values(1 ,8 ,500) ,(2 ,16 ,500) ,(3 ,24 ,500) ,(4 ,32 ,500) ,(5 ,40 ,500) ,(6 ,48 ,500) ,(7 ,56 ,500) ,(8 ,64 ,500) ,(9 ,70 ,500);

select x.c1 - (case when y.c1 is null then 0 else y.c1 end ) as result
from t_test16 x left join t_test16 y
 on x.id - 1 = y.id;

後來看到,加入我的想法(我是每三個一組)

select x.* ,case when y.t + 1 = 3 then 3 when y.g = 0 then 3 else y.t end as gg
from t_test16 x join (
	select truncate(id/3 ,0) as g ,max(truncate(id/3 ,0)) as t
	from t_test16
	group by truncate(id/3 ,0)
	) y
 on truncate(x.id/3 ,0) = y.g
create table t_test16(
 id int primary key
 ,c1 int 
 ,c2 int
);
insert into t_test16 values(1 ,8 ,500) ,(2 ,16 ,500) ,(3 ,24 ,500) ,(4 ,32 ,500) ,(5 ,40 ,500) ,(6 ,48 ,500) ,(7 ,56 ,500) ,(8 ,64 ,500) ,(9 ,70 ,500);

select x.c1 - (case when y.c1 is null then 0 else y.c1 end ) as result
from t_test16 x left join t_test16 y
 on x.id - 1 = y.id;

我一開始是用where mod取出8的倍數
但我的序號1~最後一位都要顯示
所以我的方法是不行的

但你的部分因為利用了C1 8的倍數來運算
所以算得出來
如果沒有C1欄位只利用ID 有辦法算出來嗎?
謝謝

完成了
最後用case when完成
先在cte1內
新增cus_num(依序號 自訂編號)
新增inbox 算出在第幾盒
新增outbox (算出在第幾箱)(後面8是每8pcs裝一盒,*2是每一箱裝兩盒)

,ROW_NUMBER() OVER (ORDER BY sn欄位) as cus_num
,ceil(ROW_NUMBER() OVER (ORDER BY sn欄位)) as inbox
,ceil(ROW_NUMBER() OVER (ORDER BY BB)/(8*2)) as outbox
,totalQTY

之後再cte2處理上面cte1新增的資料
新增totalBox 重寫最大值(總箱數)
新增maxInbox 重寫最大值(外盒)

,max(cte1.outbox) over ()  as totalBox
,max(ceil(tc_sfb.cus_num / 8)) over ()  as maxInbox 

在最外面用cte1和cte2的資料

totalBox = outbox
and
inbox = maxInBox
符合則取totalQTY/8之餘數

select ...
    ,  case when
           cte2.totalBox = cte1.outbox 
           and 
           cte1.inbox = cte2.maxinBox then mod (cte1.totalQTY,8)      
        else  8 
    end as QTD

from cte1,cte2 where .....

可能不是最好,但可以用,也很好理解
供給有需要的人參考

我要發表回答

立即登入回答