--T_A1
CREATE TABLE T_A1(
NO VARCHAR(2) NULL,
ID VARCHAR(4) NULL )INSERT T_A1 values (11, 5589)
INSERT T_A1 values (12, 3241)
--T_A2
CREATE TABLE T_A2 (
NO VARCHAR(2) NULL,
CD VARCHAR(2) ,
AD VARCHAR(20) NULL)INSERT T_A2 values (11, 'DO','DD51PSE_32')
INSERT T_A2 values (15, 'CO','DD81PSE_21')
INSERT T_A2 values (15, 'EO','DD81PSE_29')--SELECT
SELECT NO,ID,CD
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO *= Y.NO) Z
WHERE CD = 'DO'---------------------------------------------------------------
NO ID CD
---- ---- ----
11 5589 DO
12 3241 XO(所影响的行数为 2 行)
用了外连接, 结果为两条, 我想只得到一条条件为'DO'的, 请问应如何写
CREATE TABLE T_A1(
NO VARCHAR(2) NULL,
ID VARCHAR(4) NULL )INSERT T_A1 values (11, 5589)
INSERT T_A1 values (12, 3241)
--T_A2
CREATE TABLE T_A2 (
NO VARCHAR(2) NULL,
CD VARCHAR(2) ,
AD VARCHAR(20) NULL)INSERT T_A2 values (11, 'DO','DD51PSE_32')
INSERT T_A2 values (15, 'CO','DD81PSE_21')
INSERT T_A2 values (15, 'EO','DD81PSE_29')--SELECT
SELECT NO,ID,CD
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'
这样只有一条记录啊:
NO ID CD
---- ---- ----
11 5589 DO是不是你这里错了:WHERE X.NO *= Y.NO 多了个‘*’
SELECT NO,ID,CD
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'
CREATE TABLE T_A1(
PK VARCHAR(2) NOT NULL,
NO VARCHAR(2) NULL,
ID VARCHAR(4) NULL )INSERT T_A1 values ('AA', 11, 5589)
INSERT T_A1 values ('AB', 12, 3241)
INSERT T_A1 values ('AC', NULL, 3241)
哦, 这样的,改一下TA_1,TA_1 中的 NO 有可能为 NULL再
SELECT NO,ID,CD, AD
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD, Y.AD
FROM T_A1 X, T_A2 Y
WHERE X.NO *= Y.NO) Z
WHERE CD = 'DO'
NO ID CD AD
---- ---- ---- --------------------
11 5589 DO DD51PSE_32
12 3241 XO NULL
NULL 3241 XO NULL(所影响的行数为 3 行)得到了3行,我只想要第一行
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'
PK VARCHAR(2) NOT NULL,
NO VARCHAR(2) NULL,
ID VARCHAR(4) NULL )INSERT T_A1 values ('AA', 11, 5589)
INSERT T_A1 values ('AB', 12, 3241)
INSERT T_A1 values ('AC', NULL, 3241)CREATE TABLE T_A2 (
NO VARCHAR(2) NULL,
CD VARCHAR(2) ,
AD VARCHAR(20) NULL)INSERT T_A2 values (11, 'DO','DD51PSE_32')
INSERT T_A2 values (15, 'CO','DD81PSE_21')
INSERT T_A2 values (15, 'EO','DD81PSE_29')SELECT NO,ID,CD,AD
FROM (
SELECT X.NO, X.ID,Y.AD,ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'
NO ID CD AD
---- ---- ---- --------------------
11 5589 DO DD51PSE_32(1 行受影响)
SELECT NO,ID,CD
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'
select a.NO,a.ID,b.CD
from T_A1 a
inner join T_A2 b on a.NO=b.NO
where isnull(b.CD,'')='DO'
FROM (
SELECT X.NO, X.ID, ISNULL(Y.CD, 'XO') AS CD
FROM T_A1 X, T_A2 Y
WHERE X.NO = Y.NO) Z
WHERE CD = 'DO'