如 操作1
SELECT aaa.labor_id, b.labor_no, b.name_ch, b.sex, b.dept_name, b.dorm_room,
b.dorm_building
,aaa.create_date
FROM lbm.qrylabor_info b,
(SELECT c.labor_id "LABOR_ID", MAX (c.create_date) "create_date"
FROM lbm.labor_station c
WHERE NVL (c.station_id, NULL) IN (48, 37, 46, 33)
AND c.create_date >
TO_DATE ('2005-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND c.create_date < =
TO_DATE ('2005-11-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY c.labor_id) aaa
WHERE aaa.labor_id = b.labor_id
AND b.labor_no LIKE '1%'
AND b.dorm_building <> 'H'
AND b.dorm_room IS NOT NULL
其中
操作2
SELECT c.labor_id "LABOR_ID", MAX (c.create_date) "create_date"
FROM lbm.labor_station c
WHERE NVL (c.station_id, NULL) IN (48, 37, 46, 33)
AND c.create_date >
TO_DATE ('2005-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND c.create_date < =
TO_DATE ('2005-11-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY c.labor_id
操作2運行沒問題,但是操作1報告aaa.create_date 欄位無效,去掉這個欄位就OK了,
求助高手解決,很急得.為什麼阿
SELECT aaa.labor_id, b.labor_no, b.name_ch, b.sex, b.dept_name, b.dorm_room,
b.dorm_building
,aaa.create_date
FROM lbm.qrylabor_info b,
(SELECT c.labor_id "LABOR_ID", MAX (c.create_date) "create_date"
FROM lbm.labor_station c
WHERE NVL (c.station_id, NULL) IN (48, 37, 46, 33)
AND c.create_date >
TO_DATE ('2005-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND c.create_date < =
TO_DATE ('2005-11-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY c.labor_id) aaa
WHERE aaa.labor_id = b.labor_id
AND b.labor_no LIKE '1%'
AND b.dorm_building <> 'H'
AND b.dorm_room IS NOT NULL
其中
操作2
SELECT c.labor_id "LABOR_ID", MAX (c.create_date) "create_date"
FROM lbm.labor_station c
WHERE NVL (c.station_id, NULL) IN (48, 37, 46, 33)
AND c.create_date >
TO_DATE ('2005-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND c.create_date < =
TO_DATE ('2005-11-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY c.labor_id
操作2運行沒問題,但是操作1報告aaa.create_date 欄位無效,去掉這個欄位就OK了,
求助高手解決,很急得.為什麼阿
这里 "create_date"是别名
不是查询列名呀,你那样写当然不行
建立个临时表,把你
SELECT c.labor_id "LABOR_ID", MAX (c.create_date) "create_date"
FROM lbm.labor_station c
WHERE NVL (c.station_id, NULL) IN (48, 37, 46, 33)
AND c.create_date >
TO_DATE ('2005-11-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND c.create_date < =
TO_DATE ('2005-11-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY c.labor_id
放到临时表中,然后处理临时表
SELECT b.labor_no, b.name_ch, b.sex, b.dept_name, b.dorm_room,
b.dorm_building
FROM lbm.qrylabor_info b,是否有问题??--SQL这种写法,没什么问题,Oracle 很久没用了。不知道是否支持!
(select c1,max(c2) n1 from t3 group by c1) t2 where t2.n1 > 1