数据库是oracle。要求查出的数据是第一个sql的 brand_cd ,brand_name 和 第二个sql语句查出的数据的个数car_num(以第一个sql查出的brand_cd的值为条件)。
第一个sql:
SELECT DISTINCT
A.BRAND_CD,
A.BRAND_NAME,
A.COUNTRY_CD,
A.P_BRAND_SORT
FROM
BRAND_MASTER A,
V_STOCK_LIST B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
B.CLIENT_ID = 10001
AND
A.DEL_FLG != 1
AND
B.LATEST_MEDIA_FLG = 1
ORDER BY
A.P_BRAND_SORT第二个sql:
SELECT DISTINCT
B.CAR_CD,
B.CAR_NAME
FROM
V_STOCK_LIST A,
CAR_MASTER B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
A.CAR_CD = B.CAR_CD
AND
A.LATEST_MEDIA_FLG = 1
AND
A.BRAND_CD = #
AND
A.CLIENT_ID = 10001
ORDER BY
B.CAR_NAME
-------------------------
我根据这个写了一个sql,但是不知道为什么查出的数据不正确,是不是哪里错了?
SELECT DISTINCT
A.BRAND_CD,
A.BRAND_NAME,
A.COUNTRY_CD,
A.P_BRAND_SORT,
case
when A.BRAND_CD != 0 then
(
select
count(c.car_cd)
from
CAR_MASTER c
where
b.BRAND_CD = c.BRAND_CD and
b.car_cd=c.car_cd
)
end car_num
FROM
BRAND_MASTER A,
V_STOCK_LIST B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
B.CLIENT_ID = 0100001
AND
A.DEL_FLG != 1
AND
B.LATEST_MEDIA_FLG = 1
ORDER BY
A.P_BRAND_SORT
第一个sql:
SELECT DISTINCT
A.BRAND_CD,
A.BRAND_NAME,
A.COUNTRY_CD,
A.P_BRAND_SORT
FROM
BRAND_MASTER A,
V_STOCK_LIST B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
B.CLIENT_ID = 10001
AND
A.DEL_FLG != 1
AND
B.LATEST_MEDIA_FLG = 1
ORDER BY
A.P_BRAND_SORT第二个sql:
SELECT DISTINCT
B.CAR_CD,
B.CAR_NAME
FROM
V_STOCK_LIST A,
CAR_MASTER B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
A.CAR_CD = B.CAR_CD
AND
A.LATEST_MEDIA_FLG = 1
AND
A.BRAND_CD = #
AND
A.CLIENT_ID = 10001
ORDER BY
B.CAR_NAME
-------------------------
我根据这个写了一个sql,但是不知道为什么查出的数据不正确,是不是哪里错了?
SELECT DISTINCT
A.BRAND_CD,
A.BRAND_NAME,
A.COUNTRY_CD,
A.P_BRAND_SORT,
case
when A.BRAND_CD != 0 then
(
select
count(c.car_cd)
from
CAR_MASTER c
where
b.BRAND_CD = c.BRAND_CD and
b.car_cd=c.car_cd
)
end car_num
FROM
BRAND_MASTER A,
V_STOCK_LIST B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
B.CLIENT_ID = 0100001
AND
A.DEL_FLG != 1
AND
B.LATEST_MEDIA_FLG = 1
ORDER BY
A.P_BRAND_SORT
when A.BRAND_CD != 0 then
(
select
count(c.car_cd)
from
CAR_MASTER c
where
b.BRAND_CD = c.BRAND_CD and
b.car_cd=c.car_cd
)
end car_num 可以这样?
A.BRAND_CD,
A.BRAND_NAME,
A.COUNTRY_CD,
A.P_BRAND_SORT,
decode(A.BRAND_CD,0,0,
(select
count(c.car_cd)
from
CAR_MASTER c
where
A.BRAND_CD = c.BRAND_CD and
B.car_cd = c.car_cd
group by A.CAR_NAME
)
) car_num
FROM
BRAND_MASTER A,
V_STOCK_LIST B
WHERE
A.BRAND_CD = B.BRAND_CD
AND
B.CLIENT_ID = 0100001
AND
A.DEL_FLG != 1
AND
B.LATEST_MEDIA_FLG = 1
ORDER BY
A.P_BRAND_SORT
A.BRAND_CD =0 时car_num我给你赋0了.