SQL 查询;如何仅在一行中的条目数大于另一行中的条目数时才显示信息?

Question

超级简化代码：

SELECT lots_of_stuff.
, A.more_stuff
, B.stuff
, C.things
FROM
table A,
table B,
table C,
where (B.more_stuff = A.stuff)
and things and stuff
and lots more things
and this query has so much crap believe me
and finally
and count(select c.things from table C where c.things like 'CRED') 
> count(select c.things from table C where c.things like 'PUR')
;

所以问题是最后一点不起作用(我确定我完全做错了，这只是对如何做的一个猜测。我想知道是否有人可以给我一些建议。

我正在尝试做的是仅在特定字段中包含"CRED"的行数大于特定字段中包含"PUR"的行数的情况下返回所需的字段。(相同的字段，如果这可以简化事情。我希望它们被退回，无论"CRED"或"PUR"是较长单词(信用/购买)的一部分还是独立的。不过，它们将永远都是大写的。

编辑：

我正在寻找的只是我指定的那些列

| More_Stuff | Stuff | Things |
|   dshsdh   |  dfh  |  tjra  |
|   ddh      |  ash  |  ytra  |
|   shsdh    |  fgh  |  sayh  |
|   hsdh     |  gnh  |  tshn  |

但只有信用代码多于购买计划的客户的行。因此，如果他们在"c.things"中有 3 个不同的条目，例如"电话信用"或"抵消信用"，而在"c.things"中有 2 个不同的条目，例如"12 M 购买计划"或"促销购买 36"，我希望他们的信息显示出来。因此，当具有任何信用代码的行数大于具有任何购买计划的行数时。 我当前的非简化查询已经设置为对所有客户进行排序，我只需要根据此过滤器指定哪些客户。

Answer 1

这可以使用 Oracle 中的 WITH 子句来实现。以下代码 可能接近您要找的 -

with ds1 as
(
SELECT 
lots_of_stuff
, A.more_stuff
, B.stuff
, C.things,
count(c.things) AS COUNT_CRED
FROM
table A,
table B,
table C,
where 
(B.more_stuff = A.stuff)
and things and stuff
and lots more things
and this query has so much crap believe me
and finally
and c.things like 'CRED%'
group by 
lots_of_stuff.
, A.more_stuff
, B.stuff
, C.things
),
ds2 as
(
SELECT 
lots_of_stuff.
, A.more_stuff
, B.stuff
, C.things,
count(c.things) AS COUNT_PUR
FROM
table A,
table B,
table C,
where 
(B.more_stuff = A.stuff)
and things and stuff
and lots more things
and this query has so much crap believe me
and finally
and c.things like 'PUR%'
group by 
lots_of_stuff.
, A.more_stuff
, B.stuff
, C.things
)
SELECT DS1.*, ds2.*
from ds1, ds2
where count_cred > COUNT_PUR
;

Answer 2

我想你想要这样的东西

WITH cred_count AS
(
SELECT index_field, SUM(CASE WHEN field='CRED' THEN 1 ELSE 0 END) AS cred_count
FROM some_table
GROUP BY index_field
), pur_count AS
(
SELECT index_field, SUM(CASE WHEN field='PUR' THEN 1 ELSE 0 END) AS pur_count
FROM some_table
GROUP BY index_field
)
SELECT somestuff
FROM some_table
LEFT JOIN cred_count ON some_table.index_field = cred_count.index_field
LEFT JOIN pur_count ON some_table.index_field = pur_count.index_field
WHERE COALESCE(cred_count.cred_count,0) > COALESCE(pur_count.pur_count,0)

注意：您可以将 WHEN 部分更改为您想要计数的任何内容(例如WHEN field like '%PUR%'将计算包含字符串 PUR 的行

此外，我假设没有条目计为 0 - 在这种情况下，您的业务规则可能有所不同。

Answer 3

要在较长的单词中过滤 CRED 和 PUR，请在查询中使用 % 等通配符。

like '%CRED%'  -- if CRED can be anywhere in the string
like 'CRED%'  -- if CRED is always at the beginning of the string

请注意，如果它始终位于字符串的开头，则可以在列上使用索引以使其运行得更快。

你不能在 where 子句中使用像 count() 这样的聚合(除非 Oracle 支持它？？)

您可以对行进行分组并使用 HAVE，但在您的情况下，在子查询中移动 count() 实际上更容易。

SELECT lots_of_stuff.
, A.more_stuff
, B.stuff
, C.things
FROM
table A,
table B,
table C,
where (B.more_stuff = A.stuff)
and things and stuff
and lots more things
and this query has so much crap believe me
and finally
and (select count(c.things) from table C where c.things like '%CRED%') 
> (select count(c.things) from table C where c.things like '%PUR%')
;