嗨，我有下表
司機
| Id | vehicleId | Power |
| -------- | --------------|-------------- |
| 123 | 2221 |112 |
| 124 | 2222 |111 |
車輛
vehicleId| VehicleAvl|
| -------- | -------------- |
| 2221| Yes|
| 2222| Yes|
車輛國家
Id| vehicleId| VehicleField| VehicleValue |
| -------| -------- | -------------- | -------------- |
| 1 | 2221| Residence Country| USA|
| 2| 2221| Registered Country| USA|
| 3 | 2221| Available Country| CAN|
| 4| 2222| Residence Country| USA|
從上面我需要撰寫一個簡單的選擇查詢，該查詢將獲取車輛居住國是否屬于美國
我不需要回傳任何列而不是簡單的 Select 1
我只有驅動程式表 ID 作為輸入，所以我如何撰寫需求的查詢
SELECT 1
FROM Driver IES, Vehicle d1,
where
IES.id= :GID and d1.vehicleId= IES.vehicleId
And (SELECT vehiclevalue AS RESIDENCE_COUNTRY
FROM VehicleCountry t2
where t2.vehicleId=d1.vehicleId
AND t2.vehiclefield= 'RESIDENCE COUNTRY'
) DR
AND (IES.POWER IS NOT NULL
OR DR.RESIDENCE_COUNTRY != 'USA'
OR DR.RESIDENCE_COUNTRY IS NULL)
uj5u.com熱心網友回復：
像這樣的東西？注意
- 您發布的示例資料格式不正確，所以我不太明白哪個值屬于
vehiclecountry表中的哪一列 - 由于在您的示例中，兩個司機的居住國都是美國，因此我將最后一個值修改為英國，只是為了有所不同
- 第 1 - 15 行的樣本資料；查詢從第 17 行開始
SQL> with
2 driver (id, vehicleid) as
3 (select 123, 2221 from dual union all
4 select 124, 2222 from dual
5 ),
6 vehicle (vehicleid, vehicleavl) as
7 (select 2221, 'yes' from dual union all
8 select 2222, 'yes' from dual
9 ),
10 vehiclecountry (id, vehicleid, vehiclefield, vehiclevalue) as
11 (select 1, 2221, 'Residence Country' , 'USA' from dual union all
12 select 2, 2221, 'Registered Country', 'USA' from dual union all
13 select 3, 2221, 'Available Country' , 'USA' from dual union all
14 select 4, 2222, 'Residence Country' , 'UK' from dual
15 )
16 --
17 select nvl(max(1), 0) result
18 from driver d join vehicle v on v.vehicleid = d.vehicleid
19 join vehiclecountry c on c.vehicleid = v.vehicleid
20 where c.vehiclefield = 'Residence Country'
21 and c.vehiclevalue = 'USA'
22 --
23 and d.id = &par_driver_id;
Enter value for par_driver_id: 123
RESULT
----------
1
SQL> /
Enter value for par_driver_id: 124
RESULT
----------
0
SQL>
uj5u.com熱心網友回復：
要顯示給定駕駛員的車輛居住國家/地區：
select d.vehicleId, v.vehicleValue as residence_country
from driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId
and vc.vehicleField = 'Residence Country'
where d.id = $id
相同，但僅限居住國為美國的車輛（因此無需顯示國家/地區）：
select d.vehicleId
from driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId
and vc.vehicleField = 'Residence Country'
and vc.vehicleValue = 'USA'
where d.id = $id
uj5u.com熱心網友回復：
SELECT
CASE vc.VehicleValue = 'USA' THEN 1 ELSE 0 END
FROM driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/330512.html
下一篇：嘗試生成一些活動來生成歸檔日志