比如有一个Item表,表中有个price字段现在要搜价钱<=500,>=200的所有数据 然后 再加上一个价钱<500但却是最接近500的数据
呵呵,表达得有点不太通顺,希望见谅,实在是没有办法,本人SQL水平着实不行,呵呵
(如果有可能的话,最好用hibernate的HQL,嘿嘿,麻烦大家了)
呵呵,表达得有点不太通顺,希望见谅,实在是没有办法,本人SQL水平着实不行,呵呵
(如果有可能的话,最好用hibernate的HQL,嘿嘿,麻烦大家了)
union
(select * from Item where price = (select max(price) from Item where price >=200 and price <=500))
再调试一下
select * from item where price=(select max(price) from item where price>=200 and price<=500);
SQL> SELECT * FROM ITEM; ITEMID PRICE
---------- ----------
1 470
2 510
3 300
4 250
5 480
6 3006 rows selectedSQL> SELECT PRICE,
2 PRICE + MAX(PRICE) OVER() NEW_PRICE,
3 SUM(PRICE) OVER() + MAX(PRICE) OVER() SUM_PRICE
4 FROM ITEM TT
5 WHERE PRICE <= 500
6 AND PRICE >= 200
7 ; PRICE NEW_PRICE SUM_PRICE
---------- ---------- ----------
470 950 2280
300 780 2280
250 730 2280
480 960 2280
300 780 2280
----------
200
340
129
345
489
4326 rows selectedSQL>
SQL> select a.price, b.price
2 from (select price from item where price between 200 and 500) a
3 left join (select max(price) price
4 from (select price
5 from item
6 where price between 200 and 500
7 order by price desc)) b on a.price = b.price
8 ; PRICE PRICE
---------- ----------
489 489
432
200
340
345
FROM ITEM
WHERE PRICE >= 200
AND PRICE <= 500
UNION ALL
SELECT *
FROM ITEM
WHERE PRICE = (SELECT MIN(PRICE) FROM ITEM WHERE PRICE > 500)
FROM ITEM
WHERE PRICE >= 200
AND PRICE <= 500
UNION ALL
SELECT *
FROM ITEM
WHERE PRICE = (SELECT MIN(PRICE) FROM ITEM WHERE PRICE > 500)
FROM ITEM
WHERE PRICE >= 200
AND PRICE <= 500
UNION ALL
SELECT *
FROM ITEM
WHERE PRICE = (SELECT MIN(PRICE) FROM ITEM WHERE PRICE > 500)