oracle12c、sql、分组依据

qybjjes1  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(344)

原始sql是:

  1. select c.col1,a.col2,count(1) from table_1 a,table_2 b.table_3 c where a.key =b.key and b.no = c.no group by c.col1,a.col2 having count(a.col2) >1;

输出:

  1. c.col1 a.col2 count(1)
  2. aa1 bb1 2
  3. aa1 bb2 3
  4. aa1 bb3 5
  5. aa2 bb8 1
  6. aa2 bb1 4

我试着得到输出集,就像

  1. c.col1 count(1)
  2. aa1 10
  3. aa2 5

如何编写sql?

hgb9j2n6

hgb9j2n61#

我相信只要你把 col2 从您的选择和分组方式。因为 col2 将不再返回,还应删除having语句。我觉得应该是这样的:

  1. select
  2. c.col1,
  3. count(1)
  4. from
  5. table_1 a,
  6. table_2 b,
  7. table_3 c
  8. where
  9. a.key = b.key
  10. and b.no = c.no
  11. group by
  12. c.col1;

我希望这有帮助。

but5z9lq

but5z9lq2#

对col1使用sum()和group by

  1. select c.col1, sum(a.col2) as total
  2. from table_1 a,table_2 b.table_3 c
  3. where a.key =b.key and b.no = c.no
  4. group by c.col1;

输出---

  1. c.col1 total
  2. aa1 10
  3. aa2 5
dy1byipe

dy1byipe3#

一个“简单”的选项是使用当前查询(重写为使用 JOIN s、 作为内联视图,这是目前连接表的首选方法:

  1. SELECT col1, SUM (cnt)
  2. FROM ( SELECT c.col1, a.col2, COUNT (*) cnt --> your current query begins here
  3. FROM table_1 a
  4. JOIN table_2 b ON a.key = b.key
  5. JOIN table_3 c ON c.no = b.no
  6. GROUP BY c.col1, a.col2
  7. HAVING COUNT (a.col2) > 1) --> and ends here
  8. GROUP BY col1;

或者,移除 a.col2select :

  1. SELECT c.col1, COUNT (*) cnt
  2. FROM table_1 a
  3. JOIN table_2 b ON a.key = b.key
  4. JOIN table_3 c ON c.no = b.no
  5. GROUP BY c.col1, a.col2
  6. HAVING COUNT (a.col2) > 1;
展开查看全部

相关问题