iT邦幫忙

1

SQL指令關於統計如何寫?

XYZ 2020-03-08 22:47:342565 瀏覽

請問圖一變圖二,下面的SQL指令要改什麼才正確?--->SQL改好了,謝謝大家

https://ithelp.ithome.com.tw/upload/images/20200309/20108157ic7Q17KRYu.jpg


https://ithelp.ithome.com.tw/upload/images/20200308/20108157FkY0HAU0Fs.jpg

https://ithelp.ithome.com.tw/upload/images/20200308/20108157z96UD4km4j.jpg

1
player
iT邦大師 1 級 ‧ 2020-03-09 00:19:11

應該別想一次select搞定
你得先拆開來寫
1.先取部門的集合
2.再取你要統計的項目的集合 (這邊留下 source, name, 與這兩個串起來的項目名稱)
3.用1與2當group的查詢條件做count統計
最後再把這3種select 組合起來

不知道這樣你看得懂嗎?

3
一級屠豬士
iT邦大師 1 級 ‧ 2020-03-09 10:30:12
這種問題本身沒有特別的難度.
但是卻也無法優雅的解決.
因為那欄位的順序,是看人高興的.
沒有正規化把name與source另外有table,並指定排序順序.
我們只能手動去湊.

create table it200309 (
  id smallint generated always as identity
, idept text not null
, iname text not null
, isource text not null
);

insert into it200309 (idept, iname, isource) values
('a', '電腦', '學校'),
('a', '電腦', '公司'),
('b', '電腦', '政府機關'),
('c', '電腦', '國外'),
('d', '滑鼠', '公司'),
('d', '書桌', '政府機關'),
('e', '滑鼠', '學校'),
('e', '滑鼠', '學校'),
('e', '滑鼠', '國外'),
('e', '書桌', '學校');

-- 
產生組合
select isource || iname
  from (select distinct iname
          from it200309) a
  cross join 
       (select distinct isource
          from it200309) b;

+--------------+
|   ?column?   |
+--------------+
| 政府機關書桌 |
| 國外書桌     |
| 公司書桌     |
| 學校書桌     |
| 政府機關電腦 |
| 國外電腦     |
| 公司電腦     |
| 學校電腦     |
| 政府機關滑鼠 |
| 國外滑鼠     |
| 公司滑鼠     |
| 學校滑鼠     |
+--------------+
(12 rows)
-- 
依據上面的組合,建立輔助表
create table it200309_aux (
  asso text not null
, c1 smallint not null
, c2 smallint not null
, c3 smallint not null
, c4 smallint not null
, c5 smallint not null
, c6 smallint not null
, c7 smallint not null
, c8 smallint not null
, c9 smallint not null
, c10 smallint not null
, c11 smallint not null
, c12 smallint not null
);

insert into it200309_aux values
('學校電腦'    , 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
('公司電腦'    , 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
('政府機關電腦' , 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
('國外電腦'    , 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0),
('學校滑鼠'    , 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
('公司滑鼠'    , 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
('政府機關滑鼠' , 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
('國外滑鼠'    , 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0),
('學校書桌'    , 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0),
('公司書桌'    , 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0),
('政府機關書桌' , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0),
('國外書桌'    , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1);
--
--依據組合複製貼上當title

select i.idept
     , sum(i.cnt * j.c1) as "學校電腦"  
     , sum(i.cnt * j.c2) as "公司電腦"
     , sum(i.cnt * j.c3) as "政府機關電腦"
     , sum(i.cnt * j.c4) as "國外電腦" 
     , sum(i.cnt * j.c5) as "學校滑鼠"  
     , sum(i.cnt * j.c6) as "公司滑鼠"
     , sum(i.cnt * j.c7) as "政府機關滑鼠"
     , sum(i.cnt * j.c8) as "國外滑鼠" 
     , sum(i.cnt * j.c9) as "學校書桌" 
     , sum(i.cnt * j.c10) as "公司書桌" 
     , sum(i.cnt * j.c11) as "政府機關書桌" 
     , sum(i.cnt * j.c12) as "國外書桌"
  from (select idept
             , isource || iname as asso
             , count(*) as cnt
          from it200309
         group by idept, isource, iname) i
     , it200309_aux j
 where i.asso = j.asso
 group by i.idept
 order by i.idept;

https://ithelp.ithome.com.tw/upload/images/20200309/200506472XUCQmDebk.png

我要發表回答

立即登入回答