时间戳

首页 > 解决方案 > 如何选择有两组的公司

sql - 如何选择有两组的公司

问题描述

我仍然尝试选择两组中的所有客户。客户重复是正常的，因为选择来自发票，但我需要知道上半年有一个组并在下半年跳转到另一个的客户。

例子：

SELECT
  f.eankod as kod,                                              --(groups)
  ad.kod as firma,                                              --(markComp)
  f.nazfirmy as nazev,                                          --(nameComp)
  COUNT(ad.kod),
  sum(f.sumZklZakl + f.sumZklSniz + f.sumOsv) as cena_bez_dph   --(Price)
FROM
  ddoklfak as f
LEFT OUTER JOIN aadresar ad ON ad.idfirmy = f.idfirmy 
WHERE
  f.datvyst >= '2017-01-01'
  and f.datvyst <= '2017-12-31'
  and f.modul like 'FAV'
GROUP BY
  f.eankod,
  ad.kod,
  f.nazfirmy
HAVING COUNT (ad.kod) > 1
order by
  ad.kod

结果：

 GROUP   markcomp  nameComp    price
| D002 | B5846 | Cosmopolis | price ... |
| D003 | B6987 | Tismotis   | price ... |
| D009 | B8974 | Teramis    | price ... |
| D006 | B8876 | Kesmethis  | price ... | I need this, same company but diferent group, because this 
| D008 | B8876 | Kesmethis  | price ... | company jumped. I need know only jumped company. (last two rows from examples)

谢谢帮助。

标签： sqlselectcountwhere-clausehaving

解决方案

您可以使用 CTE 找出哪些nameComp出现多次，并只保留那些。例如：

with
x as (
  -- your query
)
select * from x where nameComp in (
  select nameComp from x group by nameComp having count(*) > 1
)

推荐阅读