iT邦幫忙

0

關於SQL的一些語法問題

sql
  • 分享至 

  • xImage

請問一下各位大大目前我資料庫中有兩個TABLE
DEP(DEP_ID DEP_NAME)
EMP(EMP_ID EMP_NAME DEP_ID)
我需要查詢 抓出每個部門(DEP)前五人(EMP)(按照員工編號EMP_ID 由小至大 抓前面五人)
有大大知道這語法要怎麼寫嗎

hwhsinng iT邦新手 5 級 ‧ 2016-08-12 17:45:15 檢舉
SELECT TOP 5 B.* FROM DEP A, EMP B
WHERE A.DEP_ID=B.DEP_ID
ORDER BY EMP_ID ASC
這樣就可以了, 栯浤網
lingyilun iT邦新手 5 級 ‧ 2016-08-15 09:07:54 檢舉
你這方法無法喔ˊˇˋ~~他只會列出總資料的5筆而不是每個部門5筆
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
4
一級屠豬士
iT邦大師 1 級 ‧ 2016-08-12 12:11:02
最佳解答
create table ithelp160812_dep (
  dep_id smallserial primary key
, dep_name text
);

create table ithelp160812_emp (
  emp_id smallint not null
, emp_name text
, dep_id smallint not null references ithelp160812_dep (dep_id)
);


insert into ithelp160812_dep values
(default, '殺雞部'),
(default, '拔毛部'),
(default, '裹粉部'),
(default, '油炸部');

select * from ithelp160812_dep;
+--------+----------+
| dep_id | dep_name |
+--------+----------+
|      1 | 殺雞部   |
|      2 | 拔毛部   |
|      3 | 裹粉部   |
|      4 | 油炸部   |
+--------+----------+
(4 rows)

insert into ithelp160812_emp values
(234, '大飛', 1),
(157, '號南', 1),
(283, '號北', 1),
(356, '號西', 1),
(765, '號東', 1),
(642, '號中', 1),
(577, '腥哥', 2),
(567, '細龜', 2),
(109, '馬仔', 2),
(255, '如花', 3),
(254, '如夢', 3),
(256, '如風', 3),
(258, '如如', 3),
(257, '如鈺', 3),
(755, '烽火', 4),
(754, '風火', 4),
(756, '陽炎', 4),
(758, '雪風', 4),
(757, '島風', 4);

--------------------------
使用 lateral join

select d.dep_id
     , d.dep_name
     , e.emp_id
     , e.emp_name
  from ithelp160812_dep d
  join lateral (
       select emp_id
            , emp_name
         from ithelp160812_emp e2
        where e2.dep_id = d.dep_id
        order by e2.emp_id
        limit 5
       ) e
    on true
 order by d.dep_id;


+--------+----------+--------+----------+
| dep_id | dep_name | emp_id | emp_name |
+--------+----------+--------+----------+
|      1 | 殺雞部   |    157 | 號南     |
|      1 | 殺雞部   |    234 | 大飛     |
|      1 | 殺雞部   |    283 | 號北     |
|      1 | 殺雞部   |    356 | 號西     |
|      1 | 殺雞部   |    642 | 號中     |
|      2 | 拔毛部   |    109 | 馬仔     |
|      2 | 拔毛部   |    567 | 細龜     |
|      2 | 拔毛部   |    577 | 腥哥     |
|      3 | 裹粉部   |    254 | 如夢     |
|      3 | 裹粉部   |    255 | 如花     |
|      3 | 裹粉部   |    256 | 如風     |
|      3 | 裹粉部   |    257 | 如鈺     |
|      3 | 裹粉部   |    258 | 如如     |
|      4 | 油炸部   |    754 | 風火     |
|      4 | 油炸部   |    755 | 烽火     |
|      4 | 油炸部   |    756 | 陽炎     |
|      4 | 油炸部   |    757 | 島風     |
|      4 | 油炸部   |    758 | 雪風     |
+--------+----------+--------+----------+
(18 rows)

--------
使用 row_number

select emp_id
     , emp_name
     , row_number() over (partition by dep_id order by emp_id)
  from ithelp160812_emp;

+--------+----------+------------+
| emp_id | emp_name | row_number |
+--------+----------+------------+
|    157 | 號南     |          1 |
|    234 | 大飛     |          2 |
|    283 | 號北     |          3 |
|    356 | 號西     |          4 |
|    642 | 號中     |          5 |
|    765 | 號東     |          6 |
|    109 | 馬仔     |          1 |
|    567 | 細龜     |          2 |
|    577 | 腥哥     |          3 |
|    254 | 如夢     |          1 |
|    255 | 如花     |          2 |
|    256 | 如風     |          3 |
|    257 | 如鈺     |          4 |
|    258 | 如如     |          5 |
|    754 | 風火     |          1 |
|    755 | 烽火     |          2 |
|    756 | 陽炎     |          3 |
|    757 | 島風     |          4 |
|    758 | 雪風     |          5 |
+--------+----------+------------+
(19 rows)

包起來取前五

select d.dep_id
     , d.dep_name
     , e.emp_id
     , e.emp_name
  from (select emp_id
             , emp_name
             , dep_id
             , row_number() over (partition by dep_id order by emp_id) as rn
          from ithelp160812_emp
       ) e
     , ithelp160812_dep d
 where d.dep_id = e.dep_id
   and e.rn <= 5
 order by d.dep_id, e.rn;
 
+--------+----------+--------+----------+
| dep_id | dep_name | emp_id | emp_name |
+--------+----------+--------+----------+
|      1 | 殺雞部   |    157 | 號南     |
|      1 | 殺雞部   |    234 | 大飛     |
|      1 | 殺雞部   |    283 | 號北     |
|      1 | 殺雞部   |    356 | 號西     |
|      1 | 殺雞部   |    642 | 號中     |
|      2 | 拔毛部   |    109 | 馬仔     |
|      2 | 拔毛部   |    567 | 細龜     |
|      2 | 拔毛部   |    577 | 腥哥     |
|      3 | 裹粉部   |    254 | 如夢     |
|      3 | 裹粉部   |    255 | 如花     |
|      3 | 裹粉部   |    256 | 如風     |
|      3 | 裹粉部   |    257 | 如鈺     |
|      3 | 裹粉部   |    258 | 如如     |
|      4 | 油炸部   |    754 | 風火     |
|      4 | 油炸部   |    755 | 烽火     |
|      4 | 油炸部   |    756 | 陽炎     |
|      4 | 油炸部   |    757 | 島風     |
|      4 | 油炸部   |    758 | 雪風     |
+--------+----------+--------+----------+
(18 rows)

一般是考Oracle Window Functions 會不會用,才出這題目.
Lateral Join 是比較新版的Oracle / PostgreSQL 才有的.
你去面試的公司,版本較舊的,就無法跑出來了.

找不到修改.....

create table ithelp160812_dep (
  dep_id smallserial primary key
, dep_name text
);

create table ithelp160812_emp (
  emp_id smallint not null
, emp_name text
, dep_id smallint not null references ithelp160812_dep (dep_id)
);


insert into ithelp160812_dep values
(default, '殺雞部'),
(default, '拔毛部'),
(default, '裹粉部'),
(default, '油炸部');

select * from ithelp160812_dep;
+--------+----------+
| dep_id | dep_name |
+--------+----------+
|      1 | 殺雞部   |
|      2 | 拔毛部   |
|      3 | 裹粉部   |
|      4 | 油炸部   |
+--------+----------+
(4 rows)

insert into ithelp160812_emp values
(234, '大飛', 1),
(157, '號南', 1),
(283, '號北', 1),
(356, '號西', 1),
(765, '號東', 1),
(642, '號中', 1),
(577, '腥哥', 2),
(567, '細龜', 2),
(109, '馬仔', 2),
(255, '如花', 3),
(254, '如夢', 3),
(256, '如風', 3),
(258, '如如', 3),
(257, '如鈺', 3),
(755, '烽火', 4),
(754, '風火', 4),
(756, '陽炎', 4),
(758, '雪風', 4),
(757, '島風', 4);

--------------------------
使用 lateral join

select d.dep_id
     , d.dep_name
     , e.emp_id
     , e.emp_name
  from ithelp160812_dep d
  join lateral (
       select emp_id
            , emp_name
         from ithelp160812_emp e2
        where e2.dep_id = d.dep_id
        order by e2.emp_id
        limit 5
       ) e
    on true
 order by d.dep_id;


+--------+----------+--------+----------+
| dep_id | dep_name | emp_id | emp_name |
+--------+----------+--------+----------+
|      1 | 殺雞部   |    157 | 號南     |
|      1 | 殺雞部   |    234 | 大飛     |
|      1 | 殺雞部   |    283 | 號北     |
|      1 | 殺雞部   |    356 | 號西     |
|      1 | 殺雞部   |    642 | 號中     |
|      2 | 拔毛部   |    109 | 馬仔     |
|      2 | 拔毛部   |    567 | 細龜     |
|      2 | 拔毛部   |    577 | 腥哥     |
|      3 | 裹粉部   |    254 | 如夢     |
|      3 | 裹粉部   |    255 | 如花     |
|      3 | 裹粉部   |    256 | 如風     |
|      3 | 裹粉部   |    257 | 如鈺     |
|      3 | 裹粉部   |    258 | 如如     |
|      4 | 油炸部   |    754 | 風火     |
|      4 | 油炸部   |    755 | 烽火     |
|      4 | 油炸部   |    756 | 陽炎     |
|      4 | 油炸部   |    757 | 島風     |
|      4 | 油炸部   |    758 | 雪風     |
+--------+----------+--------+----------+
(18 rows)

--------
使用 row_number

select emp_id
     , emp_name
     , row_number() over (partition by dep_id order by emp_id)
  from ithelp160812_emp;

+--------+----------+------------+
| emp_id | emp_name | row_number |
+--------+----------+------------+
|    157 | 號南     |          1 |
|    234 | 大飛     |          2 |
|    283 | 號北     |          3 |
|    356 | 號西     |          4 |
|    642 | 號中     |          5 |
|    765 | 號東     |          6 |
|    109 | 馬仔     |          1 |
|    567 | 細龜     |          2 |
|    577 | 腥哥     |          3 |
|    254 | 如夢     |          1 |
|    255 | 如花     |          2 |
|    256 | 如風     |          3 |
|    257 | 如鈺     |          4 |
|    258 | 如如     |          5 |
|    754 | 風火     |          1 |
|    755 | 烽火     |          2 |
|    756 | 陽炎     |          3 |
|    757 | 島風     |          4 |
|    758 | 雪風     |          5 |
+--------+----------+------------+
(19 rows)

包起來取前五

select d.dep_id
     , d.dep_name
     , e.emp_id
     , e.emp_name
  from (select emp_id
             , emp_name
             , dep_id
             , row_number() over (partition by dep_id order by emp_id) as rn
          from ithelp160812_emp
       ) e
     , ithelp160812_dep d
 where d.dep_id = e.dep_id
   and e.rn <= 5
 order by d.dep_id, e.rn;
 
+--------+----------+--------+----------+
| dep_id | dep_name | emp_id | emp_name |
+--------+----------+--------+----------+
|      1 | 殺雞部   |    157 | 號南     |
|      1 | 殺雞部   |    234 | 大飛     |
|      1 | 殺雞部   |    283 | 號北     |
|      1 | 殺雞部   |    356 | 號西     |
|      1 | 殺雞部   |    642 | 號中     |
|      2 | 拔毛部   |    109 | 馬仔     |
|      2 | 拔毛部   |    567 | 細龜     |
|      2 | 拔毛部   |    577 | 腥哥     |
|      3 | 裹粉部   |    254 | 如夢     |
|      3 | 裹粉部   |    255 | 如花     |
|      3 | 裹粉部   |    256 | 如風     |
|      3 | 裹粉部   |    257 | 如鈺     |
|      3 | 裹粉部   |    258 | 如如     |
|      4 | 油炸部   |    754 | 風火     |
|      4 | 油炸部   |    755 | 烽火     |
|      4 | 油炸部   |    756 | 陽炎     |
|      4 | 油炸部   |    757 | 島風     |
|      4 | 油炸部   |    758 | 雪風     |
+--------+----------+--------+----------+
(18 rows)
 

一般是考Oracle Window Functions 會不會用,才出這題目. Lateral Join 是比較新版的Oracle / PostgreSQL 才有的. 你去面試的公司,版本較舊的,就無法跑出來了.

3
做工仔人!
iT邦大師 1 級 ‧ 2016-08-12 17:36:02

在MS-SQL 中好像沒有 join lateral 這種JOIN方式(SQL-2008 以前的版本沒有)

個人認為比較好的方式 : 就是跑 WHILE 迴圈.

要用二層迴圈 : 第一層:部門 , 第二層:員工 先 INSERT 到 TEMP TABLE 後再 SELECT TEMP TABLE.

用while迴圈比較好???

0
st33chen
iT邦新手 5 級 ‧ 2016-08-20 14:14:00

用 partition by 應該可以
請 google 下 sql partiton by 可以找到不少sample

我要發表回答

立即登入回答