MS SQL or Oracle 的SQL語法一問

ms sql oracle

阿海 2019-12-16 12:09:18 ‧ 1909 瀏覽

分享至

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

一級屠豬士

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)

回應 3
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2019-12-16 13:10:40 檢舉

Lag 函數要MSSQL2012以上支援

石頭 iT邦高手 1 級 ‧ 2019-12-16 13:58:09 檢舉

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

阿海 iT邦新手 2 級 ‧ 2019-12-16 16:23:48 檢舉

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

登入發表回應

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

回應 2
分享
檢舉

阿海 iT邦新手 2 級 ‧ 2019-12-20 13:43:03 檢舉

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 有辦法算出來嗎?
謝謝

阿海 iT邦新手 2 級 ‧ 2019-12-23 15:24:04 檢舉

完成了
最後用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 .....

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

登入發表回應