oracle 在select语句中有条件地执行SQL

c7rzv4ha 于 2023-06-22 发布在 Oracle

关注(0)|答案(2)|浏览(172)

我有一个要求如下。我有一个主表作为主人和其他三个表如下，商人，客户，设备
我想根据条件从这些表中进行选择，并从商家、客户或设备中进行选择，

SELECT count(chkitems.alert_id) AS alert_cnt, chkitems.blacklist_id, 
sum(case when fm.alert_id is NOT null then 1 else 0 end) as fraud_cnt
FROM 
Condition 1: 
***Merchant mid INNER JOIN CHECK_BLACKLIST_ITEMS chkitems ON mid.Marchant_id = chkitems.blacklist_id***
Condition 2:
***Customer cust INNER JOIN CHECK_BLACKLIST_ITEMS chkitems ON cust.Customer_id = chkitems.blacklist_id***
Condition 3:
***Device dev INNER JOIN CHECK_BLACKLIST_ITEMS chkitems ON dev.Device_id = chkitems.blacklist_id***
LEFT JOIN FRAUDMARKING_VIEW fm ON (chkitems.alert_id = fm.alert_id)
GROUP BY chkitems.blacklist_id;

注意上面提到的条件1、条件2、条件3。有一些报表，我不知道该填什么。或者根本不可能？
数据库是Oracle。我想把它插入到一个碧玉报表中，所以根据我们目前的设计，需要使用这种select is查询
很抱歉在问题中表现出的无知，但请理解。

oracle

来源：https://stackoverflow.com/questions/76471355/conditionally-execure-sql-within-a-select-statement

2条答案

按热度按时间

b4lqfgs41#

您可以使用UNION ALL连接所有可选表（因为您只需要每个表的ID列），并使用WHERE子句过滤它们：

SELECT count(chkitems.alert_id) AS alert_cnt,
       chkitems.blacklist_id, 
       sum(case when fm.alert_id is NOT null then 1 else 0 end) as fraud_cnt
FROM   ( SELECT marchant_id AS id FROM Merchant WHERE some_condition = 'TRUE' UNION ALL
         SELECT Customer_id       FROM Customer WHERE some_condition = 'TRUE' UNION ALL
         SELECT device_id         FROM Device   WHERE some_condition = 'TRUE' ) i
       INNER JOIN CHECK_BLACKLIST_ITEMS chkitems
       ON i.id = chkitems.blacklist_id
       LEFT OUTER JOIN FRAUDMARKING_VIEW fm
       ON (chkitems.alert_id = fm.alert_id)
GROUP BY chkitems.blacklist_id;

也可以使用EXISTS：

SELECT count(c.alert_id) AS alert_cnt,
       c.blacklist_id, 
       sum(case when fm.alert_id is NOT null then 1 else 0 end) as fraud_cnt
FROM   CHECK_BLACKLIST_ITEMS c
       LEFT OUTER JOIN FRAUDMARKING_VIEW fm
       ON (c.alert_id = fm.alert_id)
WHERE  EXISTS (
         SELECT 1
         FROM   Merchant m
         WHERE  c.blacklist_id = m.marchant_id
         AND    some_condition = 'TRUE'
       )
OR     EXISTS (
         SELECT 1
         FROM   Customer x
         WHERE  c.blacklist_id = x.customer_id
         AND    some_condition = 'TRUE'
       )
OR     EXISTS (
         SELECT 1
         FROM   Device d
         WHERE  c.blacklist_id = d.device_id
         AND    some_condition = 'TRUE'
       )
GROUP BY chkitems.blacklist_id;

赞(0）回复(0）举报 2023-06-22

b4wnujal2#

如果没有一些样本数据，很难说出它的全部内容，但我们可以尝试猜测上下文。以下是涉及的表格，因为我认为它们可能是相似的：

WITH
    check_blacklist_items (ID, ALERT_ID, BLACKLIST_ID) AS
        (   Select 1, 'A01', 132 From Dual Union All
            Select 2, 'A07', 140 From Dual Union All
            Select 3, 'A23', 140 From Dual Union All
            Select 4, 'A44', 132 From Dual Union All
            Select 5, 'A45', 188 From Dual 
        ),

| ID|警报ID| BLACKLIST_ID|
| - -----|- -----|- -----|
| 1| A01|一百三十二|
| 2|公司简介|一百四十|
| 3| A23|一百四十|
| 4| A44|一百三十二|
| 5个|A45|一百八十八|

merchant (MERCHANT_ID, NOTE) AS
        (   Select 112, 'Merchant 112' From Dual Union All
            Select 129, 'Merchant 129' From Dual Union All
            Select 140, 'Merchant 140' From Dual Union All
            Select 156, 'Merchant 156' From Dual 
        ),

| 商家ID|注意事项|
| - -----|- -----|
| 一百一十二|商家112|
| 一百二十九|商人129|
| 一百四十|商家140|
| 一百五十六|商人156|

customer (CUSTOMER_ID, NOTE) AS
        (   Select 132, 'Customer 132' From Dual Union All
            Select 133, 'Customer 133' From Dual Union All
            Select 134, 'Customer 134' From Dual  
        ),

| 客户ID|注意事项|
| - -----|- -----|
| 一百三十二|客户132|
| 一百三十三|客户133|
| 一百三十四|客户134|

device (DEVICE_ID, NOTE) AS
        (   Select 180, 'Device 180' From Dual Union All
            Select 188, 'Device 188' From Dual Union All
            Select 190, 'Device 190' From Dual  
        ),

| 器械ID|注意事项|
| - -----|- -----|
| 一百八十|装置180|
| 一百八十八|设备188|
| 一百九十|装置190|
假设fraudmarking_view用于数据比较，作为示例，我创建了如下：

fraudmarking_view (ALERT_ID, OTHER_COLS) AS
        (   Select 'A01', null From Dual Union All
            Select 'A07', null From Dual Union All
            Select 'A44', null From Dual Union All
            Select 'A45', null From Dual 
        ),

| 警报ID|其他_COLS|
| - -----|- -----|
| A01||
| 公司简介||
| A44||
| A45||
现在，我们可以将上述所有内容组合成一个连接的数据集（将其命名为Grid）

grid AS
        (   Select      chkitems.BLACKLIST_ID, chkitems.ALERT_ID, fm.ALERT_ID "FM_ALERT_ID",
                        Case When mid.MERCHANT_ID Is Not Null Then mid.MERCHANT_ID End "MERCHANT_ID",
                        Case When custid.CUSTOMER_ID Is Not Null Then custid.CUSTOMER_ID End "CUSTOMER_ID",
                        Case When devid.DEVICE_ID Is Not Null Then devid.DEVICE_ID End "DEVICE_ID"
            From        check_blacklist_items chkitems
            Left Join   merchant mid ON(mid.MERCHANT_ID = chkitems.BLACKLIST_ID) 
            Left Join   customer custid ON(custid.CUSTOMER_ID = chkitems.BLACKLIST_ID) 
            Left Join   device devid ON(devid.DEVICE_ID = chkitems.BLACKLIST_ID) 
            Left Join   fraudmarking_view fm ON(fm.ALERT_ID = chkitems.ALERT_ID)
        )

。。。看起来像是。。
| BLACKLIST_ID|警报ID| FM_ALERT_ID|商家ID|客户ID|器械ID|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 一百三十二|A01| A01||一百三十二||
| 一百三十二|A44| A44||一百三十二||
| 一百四十|A23||一百四十|||
| 一百四十|公司简介|公司简介|一百四十|||
| 一百八十八|A45| A45|||一百八十八|
...如果我们在按BLACKLIST_ID分组的网格数据集上使用启动select命令...

SELECT    BLACKLIST_ID, Count(ALERT_ID) "ALERT_CNT",
          Sum(Case When FM_ALERT_ID Is Not Null Then 1 Else 0 END) "FRAUD_CNT"
FROM      grid
GROUP BY  BLACKLIST_ID

......结果应该像下面这样。
| BLACKLIST_ID|警报_CNT|欺诈_CNT|
| - -----|- -----|- -----|
| 一百四十|2| 1|
| 一百八十八|1| 1|
| 一百三十二|2| 2|
添加
如果您想知道数据是从哪里提取的，并且在三个黑名单表中是否有相同的ID，您应该向网格添加另一列（PICKED_FROM）

grid AS
        (   Select      chkitems.BLACKLIST_ID, chkitems.ALERT_ID, fm.ALERT_ID "FM_ALERT_ID",
                        Case When mid.MERCHANT_ID Is Not Null Then mid.MERCHANT_ID End "MERCHANT_ID",
                        Case When custid.CUSTOMER_ID Is Not Null Then custid.CUSTOMER_ID End "CUSTOMER_ID",
                        Case When devid.DEVICE_ID Is Not Null Then devid.DEVICE_ID End "DEVICE_ID",
            Case When mid.MERCHANT_ID Is Not Null And
                      custid.CUSTOMER_ID Is Not Null And 
                      devid.DEVICE_ID Is Not Null Then 'MERCHANT, CUSTOMER, DEVICE'
                When mid.MERCHANT_ID Is Not Null And
                     custid.CUSTOMER_ID Is Not Null Then 'MERCHANT, CUSTOMER'
                When mid.MERCHANT_ID Is Not Null And
                     devid.DEVICE_ID Is Not Null Then 'MERCHANT, DEVICE'
                When custid.CUSTOMER_ID Is Not Null And
                     devid.DEVICE_ID Is Not Null Then 'CUSTOMER, DEVICE'
                When mid.MERCHANT_ID Is Not Null Then 'MERCHANT'
                When custid.CUSTOMER_ID Is Not Null Then 'CUSTOMER'
                When devid.DEVICE_ID Is Not Null Then 'DEVICE'
            End "PICKED_FROM"
            From        check_blacklist_items chkitems
            Left Join   merchant mid ON(mid.MERCHANT_ID = chkitems.BLACKLIST_ID) 
            Left Join   customer custid ON(custid.CUSTOMER_ID = chkitems.BLACKLIST_ID) 
            Left Join   device devid ON(devid.DEVICE_ID = chkitems.BLACKLIST_ID) 
            Left Join   fraudmarking_view fm ON(fm.ALERT_ID = chkitems.ALERT_ID)
      Order By    chkitems.BLACKLIST_ID

网格数据集现在是这样的：
| BLACKLIST_ID|警报ID| FM_ALERT_ID|商家ID|客户ID|器械ID|选择_从|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 一百三十二|A01| A01||一百三十二||客户服务|
| 一百三十二|A44| A44||一百三十二||客户服务|
| 一百四十|A23||一百四十|||商人|
| 一百四十|公司简介|公司简介|一百四十|||商人|
| 一百八十八|A45| A45|||一百八十八|设备|
现在您可以将该列包含在您的选择和分组依据中...

SELECT    BLACKLIST_ID, Count(ALERT_ID) "ALERT_CNT",
          Sum(Case When FM_ALERT_ID Is Not Null Then 1 Else 0 END) "FRAUD_CNT",
          PICKED_FROM
FROM      grid
GROUP BY  BLACKLIST_ID, PICKED_FROM
ORDER BY  BLACKLIST_ID

...结果如下：
| BLACKLIST_ID|警报_CNT|欺诈_CNT|选择_从|
| - -----|- -----|- -----|- -----|
| 一百三十二|2| 2|客户服务|
| 一百四十|2| 1|商人|
| 一百八十八|1| 1|设备|

赞(0）回复(0）举报 2023-06-22

我来回答

oracle 在select语句中有条件地执行SQL

2条答案

相关问题

热门标签

最新问答