一个有点难度的SQL
我有一个table:COMPUTER_PRICE，格式如下：goods price dates
HP电脑 20000 5.21
HP电脑 20050 5.23
NEC电脑 31200 5.3
NEC电脑 32000 5.5
查询结果要求：要查出每种电脑的最新价格；
上面表的结果为：
goods price dates
HP电脑 20050 5.23
NEC电脑 32000 5.5
我的SQL
select goods,price,dates
from COMPUTER_PRICE x
where dates in (select max(dates)
from computer_price
where goods = x.goods);
问题:可不可以查询出最新一条记录的同时,查询统计出该记录所在组的统计,如count(hp电脑)
可以的话SQL怎么写? 谢谢!! select x.goods,x.price,x.dates,y.counts
from COMPUTER_PRICE x ,
(select goods ,count(*) counts from COMPUTER_PRICE) y
where x.goods=y.goods
and dates in (select max(dates)
from computer_price
where goods = x.goods); 小小修改
select x.goods,x.price,x.dates,y.counts
from COMPUTER_PRICE x ,
(select goods ,count(*) counts from COMPUTER_PRICE group by goods) y
where x.goods=y.goods
and dates in (select max(dates)
from computer_price
where goods = x.goods); select
a.goods,a.price,a.dates,
b.count com_count
from
(
select goods,price,max(dates) dates
from table
group by goods
) a
inner join
(select goods,count(1) com_count
from table
group by goods
) b
on a.goods=b.goods select p.goods,p.price,p.dates,c.num from COMPUTER_PRICE p
left join (select goods,count(goods) num from COMPUTER_PRICE group by goods) c
on p.goods=c.goods where p.dates in (select max(dates) from COMPUTER_PRICE group by goods) select c.goods,c.price,c.dates,d.counts
from computer_price c,
(select a.goods,count(*) counts
from computer_price a
group by a.goods) d
where c.goods=d.goods
and c.dates in (
select max(b.dates)
from computer_price b
where c.goods=b.goods); select goods,price,dates
from COMPUTER_PRICE
where dates in (select max(dates)
from computer_price
group by goods); 这样应该就行了吧 with compute_table as(
select 'hp' goods,20000 price,5.21 dates from dual
union all
select 'hp',20050,5.23 from dual
union all
select 'nec',31200,5.3 from dual
union all
select 'nec',32000,5.5 from dual
)
select b.goods,b.prices,b.datess,b.good
from (select a.goods,
max(a.price) prices,
max(a.dates) datess,
count(a.goods) good,
row_number() over(partition by a.goods order by max(a.dates) desc) rn
from compute_table a
group by a.goods) b
where rn < 2 select a.* from test a,
(select goods,
max(dates)mx_dates
from test group by goods) b
where a.goods=b.goods
and a.dates=b.mx_dates
/ with tab as
(
select 'HP電腦' goods,20000 price,5.21 dates from dual
union
select 'HP電腦' goods,20050 price,5.24 dates from dual
union
select 'NEC電腦' goods,31200 price,5.3 dates from dual
union
select 'NEC電腦' goods,32000 price,5.20 dates from dual
)
---方法1
select * from
(
select goods,price,dates,count(*) over(partition by goods order by price desc ) num
from tab
) where num=1
---方法2
select tab.goods,tab.price,tab.dates
from tab,(select goods,max(price) price from tab group by goods) t
where tab.goods = t.goods
and tab.price = t.price ---方法1
SELECT A.GOODS, A.PRICE, A.DATES, B.IS_COUNT
FROM COMPUTER_PRICE A,
(SELECT GOODS, COUNT(*) IS_COUNT FROM COMPUTER_PRICE GROUP BY GOODS) B
WHERE A.GOODS = B.GOODS
AND A.DATES IN
(SELECT MAX(DATES) FROM COMPUTER_PRICE C WHERE C.GOODS = A.GOODS)
---方法2
SELECT M.*
FROM COMPUTER_PRICE M,
(SELECT DISTINCT A.GOODS,
COUNT(A.GOODS) OVER(PARTITION BY A.GOODS),
MAX(A.DATES) OVER(PARTITION BY A.GOODS) AS DATES
FROM COMPUTER_PRICE A) B
WHERE M.GOODS = B.GOODS
AND M.DATES = B.DATES ---方法2
SELECT M.*,B.IS_COUNT
FROM COMPUTER_PRICE M,
(SELECT DISTINCT A.GOODS,
COUNT(A.GOODS) OVER(PARTITION BY A.GOODS) IS_COUNT,
MAX(A.DATES) OVER(PARTITION BY A.GOODS) AS DATES
FROM COMPUTER_PRICE A) B
WHERE M.GOODS = B.GOODS
AND M.DATES = B.DATES select deptno,ename,hiredate,
count(*) over(partition by deptno
order by hiredate nulls first
range 100 preceding ) cnt_range,
count(*) over (partition by deptno
order by hiredate nulls first
rows 2 preceding ) cnt_rows
from emp
where deptno in (10,20)
order by deptno,hiredate
/
页:
[1]