I have a mysql table with these columns:
- ID (auto-increment)
- ID_BOOK (int)
- PRICE (double)
- DATA (date)
I know two ID_BOOK values, example, 1 and 2.
QUERY:
I have to extract all the PRICE (of the ID_BOOK=1 and ID_BOOK=2) where DATA is the same!
Table example:
1 1 10.00 2010-05-16
2 1 11.00 2010-05-15
3 1 12.00 2010-05-14
4 2 18.开发者_运维知识库00 2010-05-16
5 2 11.50 2010-05-15
Result example:
1 1 10.00 2010-05-16
4 2 18.00 2010-05-16
2 1 11.00 2010-05-15
5 2 11.50 2010-05-15
ID_BOOK=2 hasn't 2010-05-14
so i jump it.
Thank you so much!
I've taken a guess at how you want hte results odered and you'll need to replace myTable
with the actual name of your table.
SELECT *
FROM myTable
WHERE DATA IN
(
SELECT
DATA
FROM myTable
GROUP BY
DATA
HAVING COUNT(*) > 1
)
ORDER BY
DATA DESC,
ID_BOOK,
ID
select PURCHASE_DATE, PRICE from table group by PURCHASE_DATE
This will give you a slightly different format for the results, but it might be useful:
Select
t1.id as t1_id, t1.id_book as t1_id_book, t1.price as t1_price, t1.data as t1_data,
t2.id as t2_id, t2.id_book as t2_id_book, t2.price as t2_price, t2.data as t2_data
from table t1
inner join table t2 on t1.data = t2.data
and t1.id_book = 1
and t2.id_book = 2
You would get :
1 1 10.00 2010-05-16 4 2 18.00 2010-05-16
2 1 11.00 2010-05-15 5 2 11.50 2010-05-15
精彩评论