PL/SQl问题，oracle 9i

发布于 2024-11-28 13:24:09 字数 432 浏览 4 评论 0原文

我们这里有一个场景，如果下面的条件不成立，我们需要归还汽车，并且如果为真，则不返回任何内容（或 null），

table-> from car_list 
condition where color = red and color_tinge = light and car = ?

现在可以有多个颜色值=红色和颜色色调=任何东西......我们只需要满足这两种情况的。这是表的示例数据：

car   color   color_tinge
a1    red     light
a2    red     dark
a1    green   light
a3    blue    dark

如果条件不为真，则输出数据应为 car#（我们正在传递条件的车号），如果条件为真，则为空或无

任何帮助，不胜感激！

原文

We have a scenario here where we need to return the car if the below condition is not true and not return anything (or null)if its true

table-> from car_list 
condition where color = red and color_tinge = light and car = ?

now there can be multiple values for color = red and color tinge = anything... and we need only the ones which satisfy both cases.
Here is sample data of the table:

car   color   color_tinge
a1    red     light
a2    red     dark
a1    green   light
a3    blue    dark

the output data should be car# if the condition is not true( we are passing
the car number to the condition) and null or nothing if the condition is true

any help is appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遗失的美好 2024-12-05 13:24:09

选择汽车
来自汽车列表
在哪里
不是（颜色 = '红色' 且 color_tingle = '浅色'）
和汽车 = car_id;

回复收藏 0 原文

二手情话 2024-12-05 13:24:09

DECLARE
myCar VARCHAR(20);

然后……

SELECT
   car
FROM
   table
WHERE
   color != 'red' AND tinge != 'light' AND car = myCar

或者……

SELECT
   DECODE (color, 'red', DECODE (tinge, 'light', 'FAIL', 'PASS'), 'PASS')
FROM
   table
WHERE
   car = myCar

DECLARE
myCar VARCHAR(20);

and then...

SELECT
   car
FROM
   table
WHERE
   color != 'red' AND tinge != 'light' AND car = myCar

Or...

SELECT
   DECODE (color, 'red', DECODE (tinge, 'light', 'FAIL', 'PASS'), 'PASS')
FROM
   table
WHERE
   car = myCar

回复收藏 0 原文

白首有我共你 2024-12-05 13:24:09

初始化...

创建您的表（
汽车VARCHAR2（20字节），
颜色VARCHAR2（20字节），
TINGE VARCHAR2(20 字节));插入您的表值
('a1','红色','浅色');
插入您的值（'a2'、'red'、'dark'）；
插入您的表值（'a1'、'green'、'light'）；
插入您的值（'a3'、'blue'、'dark'）；
提交；
从您的表中选择*；

然后...

SET serveroutput ON
DECLARE
  ret VARCHAR2(10);
  CURSOR c_cur (p1 VARCHAR2)
  IS
    SELECT COUNT(1)
    FROM YOURTABLE
    WHERE color = 'red'
    AND tinge   = 'light'
    AND CAR     = p1;
BEGIN
  OPEN c_cur('a2');
  FETCH c_cur INTO ret;
  IF ret = 1 THEN
    dbms_output.put_line('pass');
  ELSE
    dbms_output.put_line('fail');
  END IF;
END;

Initialize...

CREATE TABLE YOURTABLE (
CAR VARCHAR2(20 BYTE),
COLOR VARCHAR2(20 BYTE),
TINGE VARCHAR2(20 BYTE) ); INSERT INTO YOURTABLE VALUES
('a1','red','light');
INSERT INTO YOURTABLE VALUES ('a2','red','dark');
INSERT INTO YOURTABLE VALUES ('a1','green','light');
INSERT INTO YOURTABLE VALUES ('a3','blue','dark');
COMMIT;
SELECT * FROM YOURTABLE;

Then...

SET serveroutput ON
DECLARE
  ret VARCHAR2(10);
  CURSOR c_cur (p1 VARCHAR2)
  IS
    SELECT COUNT(1)
    FROM YOURTABLE
    WHERE color = 'red'
    AND tinge   = 'light'
    AND CAR     = p1;
BEGIN
  OPEN c_cur('a2');
  FETCH c_cur INTO ret;
  IF ret = 1 THEN
    dbms_output.put_line('pass');
  ELSE
    dbms_output.put_line('fail');
  END IF;
END;

回复收藏 0 原文

小梨窩很甜 2024-12-05 13:24:09

这将返回 car#ID 或 NULL。不确定我是否正确理解你想要什么。

SELECT
    CASE WHEN EXISTS
        ( SELECT *
          FROM car_list 
          WHERE color = red 
            AND color_tinge = light
            AND car = car#ID
        ) 
      THEN NULL
      ELSE car#ID
    END AS Result
FROM dual

这行得通吗？

SELECT Result
FROM
  ( SELECT
        CASE WHEN EXISTS
            ( SELECT *
              FROM car_list 
              WHERE color = red 
                AND color_tinge = light
                AND car = car#ID
            ) 
          THEN NULL
          ELSE car#ID
        END AS Result
    FROM dual
  ) tmp
WHERE Result IS NOT NULL

This will return either car#ID or NULL. Not sure if I understand correctly what you want.

SELECT
    CASE WHEN EXISTS
        ( SELECT *
          FROM car_list 
          WHERE color = red 
            AND color_tinge = light
            AND car = car#ID
        ) 
      THEN NULL
      ELSE car#ID
    END AS Result
FROM dual

Would this work?

SELECT Result
FROM
  ( SELECT
        CASE WHEN EXISTS
            ( SELECT *
              FROM car_list 
              WHERE color = red 
                AND color_tinge = light
                AND car = car#ID
            ) 
          THEN NULL
          ELSE car#ID
        END AS Result
    FROM dual
  ) tmp
WHERE Result IS NOT NULL

回复收藏 0 原文

~没有更多了~