有两张表 t 和 tt t表
id line_id code
1 1 235
2 1 265
3 1 265
4 2 389
5 2 356
6 2 389
7 3 456
8 3 456
9 3 489tt 表
id line_id station_order CODE
1 1 1 235
2 1 2 256
3 1 3 265
4 2 1 325
5 2 2 356
6 2 3 389
7 2 4 389
8 3 1 456
9 3 2 489
10 3 3 456
怎么查出t表中的code值不等于tt表中station_order最大值所对应的code值,当line_id相等时
得到的结果:
id line_id code
1 1 235
5 2 356
9 3 489
id line_id code
1 1 235
2 1 265
3 1 265
4 2 389
5 2 356
6 2 389
7 3 456
8 3 456
9 3 489tt 表
id line_id station_order CODE
1 1 1 235
2 1 2 256
3 1 3 265
4 2 1 325
5 2 2 356
6 2 3 389
7 2 4 389
8 3 1 456
9 3 2 489
10 3 3 456
怎么查出t表中的code值不等于tt表中station_order最大值所对应的code值,当line_id相等时
得到的结果:
id line_id code
1 1 235
5 2 356
9 3 489
where (lineid,code) not exit
(select lineid,max(code) as code from tt group by lineid)
where (lineid,code) not exit
(
select lineid,code
from tt ,
(select lineid, max(station_order) station_order from tt group by by lineid) a
where tt.lineid=a.lineid
group by tt.lineid
having tt.station_order = a.station_order )
where t.code <>
(select max(code) from tt
where tt.line_id=t.line_id
group by tt.line_id);
ID LINE_ID CODE
1 1 235
2 1 265
3 1 265
4 2 389
5 2 356
6 2 389
7 3 456
8 3 456
9 3 489select * from tt
ID LINE_ID STATION_ORDER CODE
1 1 1 235
2 1 2 256
3 1 3 265
4 2 1 325
5 2 2 356
6 2 3 389
7 2 4 389
8 3 1 456
9 3 2 489
10 3 3 456
select * from t
where (line_id,code) not in
(
select tt.line_id,max(tt.code)
from tt ,
(select line_id, max(station_order) station_order from tt group by line_id) a
where tt.line_id=a.line_id
and tt.station_order = a.station_order
group by tt.line_id
)ID LINE_ID CODE
1 1 235
5 2 356
9 3 489
select * from (
select id,line_id,station_order,code,row_number() over(partition by line_id order by station_order desc) rn from tt
) where rn = 1
) a,t where a.line_id = t.line_id and a.code != t.code
from t
where code!=(select max(code) from tt where line_id=t.line_id)
from t a
where not exists
(
select 1
from
(select line_id,max(stationid)over(partition by line_id),code from tt) b
where a.line_id=b.line_id and a.code<>b.code
);
where t.line_id = tt.line_id and t.code <> tt.code;
FROM tbla b,
(SELECT * FROM tblb WHERE ROWID IN (SELECT Max(rowid)over(partition BY f2 ORDER BY f3 desc) FROM tblb)) c
WHERE b.f2=c.f2 AND b.f3<>c.f4;
SELECT 1 AS ID, 1 AS LINE_ID, 235 AS CODE
FROM DUAL
UNION
SELECT 2 AS ID, 1 AS LINE_ID, 265 AS CODE
FROM DUAL
UNION
SELECT 3 AS ID, 1 AS LINE_ID, 265 AS CODE
FROM DUAL
UNION
SELECT 4 AS ID, 2 AS LINE_ID, 389 AS CODE
FROM DUAL
UNION
SELECT 5 AS ID, 2 AS LINE_ID, 356 AS CODE
FROM DUAL
UNION
SELECT 6 AS ID, 2 AS LINE_ID, 389 AS CODE
FROM DUAL
UNION
SELECT 7 AS ID, 3 AS LINE_ID, 456 AS CODE
FROM DUAL
UNION
SELECT 8 AS ID, 3 AS LINE_ID, 456 AS CODE
FROM DUAL
UNION
SELECT 9 AS ID, 3 AS LINE_ID, 489 AS CODE FROM DUAL
),
tt AS (
SELECT 1 AS ID, 1 AS LINE_ID, 1 AS STATION_ORDER, 235 AS CODE
FROM DUAL
UNION
SELECT 2 AS ID, 1 AS LINE_ID, 2 AS STATION_ORDER, 256 AS CODE
FROM DUAL
UNION
SELECT 3 AS ID, 1 AS LINE_ID, 3 AS STATION_ORDER, 265 AS CODE
FROM DUAL
UNION
SELECT 4 AS ID, 2 AS LINE_ID, 1 AS STATION_ORDER, 325 AS CODE
FROM DUAL
UNION
SELECT 5 AS ID, 2 AS LINE_ID, 2 AS STATION_ORDER, 356 AS CODE
FROM DUAL
UNION
SELECT 6 AS ID, 2 AS LINE_ID, 3 AS STATION_ORDER, 389 AS CODE
FROM DUAL
UNION
SELECT 7 AS ID, 2 AS LINE_ID, 4 AS STATION_ORDER, 389 AS CODE
FROM DUAL
UNION
SELECT 8 AS ID, 3 AS LINE_ID, 1 AS STATION_ORDER, 456 AS CODE
FROM DUAL
UNION
SELECT 9 AS ID, 3 AS LINE_ID, 2 AS STATION_ORDER, 489 AS CODE
FROM DUAL
UNION
SELECT 10 AS ID, 3 AS LINE_ID, 3 AS STATION_ORDER, 456 AS CODE FROM DUAL
)
SELECT T.ID, T.LINE_ID, T.CODE
FROM (SELECT TMP_TT.ID, TMP_TT.LINE_ID, TMP_TT.CODE
FROM (SELECT TT.ID,
TT.LINE_ID,
TT.CODE,
ROW_NUMBER() OVER(PARTITION BY TT.LINE_ID ORDER BY TT.STATION_ORDER DESC) AS RN
FROM TT) TMP_TT
WHERE TMP_TT.RN = 1) TMP_TABLE,
T
WHERE T.CODE ^= TMP_TABLE.CODE
AND T.LINE_ID = TMP_TABLE.LINE_ID