急救，sql分组查询

Question

select a.* from tb a,
(select citycode,convert(varchar(10),dtime,120) as dtime , max(price) as price from tb group by citycode,convert(varchar(10),dtime,120)) b
where a.citycode = b.citycode and convert(varchar(10),a.dtime,120) = b.dtime

Answer 1

select 表.citycode,t.dtime,表.cityname,t.price
from 表
inner join (select dtime,max(price) as price
from 表
group by dtime) t on t.dtime=表.dtime and t.price=表.price
order by 表.citycode,t.dtime

Answer 2

大乌龟的正确。
应该还要按citycode分组

Answer 3

上面错了,不好意思,少了个条件.if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(
citycode char(3),
dtime datetime,
cityname varchar(10),
price int
)insert into tb(citycode,dtime,cityname,price) values('010','2006-02-01 00:00:00.000','chaoyang',30)
insert into tb(citycode,dtime,cityname,price) values('010','2006-02-01 00:00:00.000','haidian' ,23)
insert into tb(citycode,dtime,cityname,price) values('010','2006-02-02 00:00:00.000','haidian' ,32)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-01 00:00:00.000','pudong'  ,44)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-01 00:00:00.000','puxi'    ,33)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-03 00:00:00.000','pudong'  ,44)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-03 00:00:00.000','puxi'    ,55)select a.* from tb a,
(select citycode,convert(varchar(10),dtime,120) as dtime , max(price) as price from tb group by citycode,convert(varchar(10),dtime,120)) b
where a.citycode = b.citycode and a.dtime = b.dtime and a.price = b.pricedrop table tb
--
citycode dtime                   cityname   price       
-------- ----------------------- ---------- ----------- 
010      2006-02-01 00:00:00.000 chaoyang   30
010      2006-02-02 00:00:00.000 haidian    32
021      2006-02-01 00:00:00.000 pudong     44
021      2006-02-03 00:00:00.000 puxi       55（所影响的行数为 4 行）

Answer 4

gahade(沙果) 兄弟,如果dtime带时间的话,你的方法就不灵了.另:还没休息?上面还要改一下.if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(
citycode char(3),
dtime datetime,
cityname varchar(10),
price int
)insert into tb(citycode,dtime,cityname,price) values('010','2006-02-01 00:00:00.000','chaoyang',30)
insert into tb(citycode,dtime,cityname,price) values('010','2006-02-01 00:00:00.000','haidian' ,23)
insert into tb(citycode,dtime,cityname,price) values('010','2006-02-02 00:00:00.000','haidian' ,32)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-01 00:00:00.000','pudong'  ,44)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-01 00:00:00.000','puxi'    ,33)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-03 00:00:00.000','pudong'  ,44)
insert into tb(citycode,dtime,cityname,price) values('021','2006-02-03 00:00:00.000','puxi'    ,55)select a.* from tb a,
(select citycode,convert(varchar(10),dtime,120) as dtime , max(price) as price from tb group by citycode,convert(varchar(10),dtime,120)) b
where a.citycode = b.citycode and convert(varchar(10),a.dtime,120) = b.dtime and a.price = b.pricedrop table tb--结果
citycode dtime                   cityname   price       
-------- ----------------------- ---------- ----------- 
010      2006-02-01 00:00:00.000 chaoyang   30
010      2006-02-02 00:00:00.000 haidian    32
021      2006-02-01 00:00:00.000 pudong     44
021      2006-02-03 00:00:00.000 puxi       55（所影响的行数为 4 行）

Answer 5

select t1.citycode,t1.dtime,t1.cityname,t1.price from Test5 as t1,(select citycode,dtime,max(price) as price from Test5 group by dtime,citycode ) as t2 where t1.citycode=t2.citycode and t1.dtime=t2.dtime and t1.price=t2.price order by t1.citycode,t1.dtime--SQL Server 2000下测试通过！因为要按dtime和citycode分组，这样既不包含在聚合函数中也不包含在 GROUP BY 子句中的列（cityname）就无法选出，所以做了一个自连接！还有更好的方法吗？等待学习！

Answer 6

citycode   dtime                                                  cityname   price       
---------- ------------------------------------------------------ ---------- ----------- 
010        2006-02-01 00:00:00.000                                chaoyang   30
010        2006-02-01 00:00:00.000                                haidian    23
010        2006-02-02 00:00:00.000                                haidian    32
021        2006-02-01 00:00:00.000                                pudong     44
021        2006-02-01 00:00:00.000                                puxi       33
021        2006-02-03 00:00:00.000                                pudong     44
021        2006-02-03 00:00:00.000                                puxi       55表结构如上，现在想要得到如下结果：
citycode   dtime                                                  cityname   price       
---------- ------------------------------------------------------ ---------- ----------- 
010        2006-02-01 00:00:00.000                                chaoyang   30
010        2006-02-02 00:00:00.000                                haidian    32
021        2006-02-01 00:00:00.000                                pudong     44
021        2006-02-03 00:00:00.000                                puxi       55就是想得到每一天的最高价格，按照citycode,dtime来排序。请问如何实现？？？？？？/*解答*/
/*建表*/
create table #tb
   (citycode char(3),
    dtime datetime,
    cityname varchar(10),
    price int)
/*插入数据*/
insert into #tb(citycode,dtime,cityname,price) 
        values('010','2006-02-01 00:00:00.000','chaoyang',30)
insert into #tb(citycode,dtime,cityname,price) 
        values('010','2006-02-01 00:00:00.000','haidian' ,23)
insert into #tb(citycode,dtime,cityname,price) 
        values('010','2006-02-02 00:00:00.000','haidian' ,32)
insert into #tb(citycode,dtime,cityname,price) 
        values('021','2006-02-01 00:00:00.000','pudong'  ,44)
insert into #tb(citycode,dtime,cityname,price) 
        values('021','2006-02-01 00:00:00.000','puxi'    ,33)
insert into #tb(citycode,dtime,cityname,price) 
        values('021','2006-02-03 00:00:00.000','pudong'  ,44)
insert into #tb(citycode,dtime,cityname,price) 
        values('021','2006-02-03 00:00:00.000','puxi'    ,55)
/*分组查询*/
select citycode,dtime,max(price)as price from #tb group by citycode,dtime order by citycode

Answer 7

select tablename.citycode,Table.dtime,tablename.cityname,Table.price
from tablename inner join 
(
select dtime,max(price) as price 
from tablename
group by dtime
) Table 
on Table.dtime=tablename.dtime and Table.price=tablename.price
order by Table.dtime