22,199
社区成员
发帖
与我相关
我的任务
分享
select a.id,c.company,a.title,isnull(b.price,0),a.uptime,c.phone
from tb1 a left join tb2 b on a.id=b.pid
left join tb3 c on c.user=a.user
where a.title like '%123%'
and not exists(select 1 from tb1 t1 left join tb2 t2 on t1.id=t2.pid where t1.user=a.user and t2.price>b.price and t1.uptime>a.uptime)
order by a.uptime desc,a.id desc
select a.id,a.company,a.title,a.price,a.uptime,a.pone
from tb1 a left join tb2 b on a.id=b.pid
left join tb3 c on c.user=a.user
where a.title like '%123%'
and not exists(select 1 from tb1 where user=a.user and price>a.price and uptime>a.uptime)
select a.id,c.company,a.title,b.price,a.uptime,c.phone
from tb1 a join tb2 b on a.id = b.pid
left join tb3 c on a.[user] = c.[user]
and a.title like '%123%'
and a.id = (select top 1 t.id from tb1 t join tb2 e on t.id = e.pid
where t.[user] = a.[user]
order by e.price desc,e.[time],t.uptime desc)
select tb1.id,tb3.company,tb1.title,
isnull(tb2.price,0) 'price',tb1.uptime,tb3.phone
from tb1
left join tb2 on tb1.id=tb2.pid
inner join tb3 on tb1.user=tb3.user
inner join
(select
tb1.user,
max(isnull(tb2.price,0)) maxprice
from tb1
left join tb2 on tb1.id=tb2.pid
inner join tb3 on tb1.user=tb3.user
where tb1.title like '%123%'
group by tb1.user) tb4
on tb1.user=tb4.user and tb2.price=tb4.maxprice
where tb1.title like '%123%'
order by tb1.uptime desc,tb1.id desc
select tb1.id,tb3.company,tb1.title,tb2.price,tb1.uptime,tb3.phone
from tb1
inner join tb2 on tb1.id=tb2.pid
inner join tb3 on tb1.user=tb3.user
inner join
(select
tb1.user,
max(tb2.price) maxprice
from tb1
inner join tb2 on tb1.id=tb2.pid
inner join tb3 on tb1.user=tb3.user
where tb1.title like '%123%'
group by tb1.user) tb4
on tb1.user=tb4.user and tb2.price=tb4.maxprice
where tb1.title like '%123%'
order by tb1.uptime desc,tb1.id desc
select tb1.id,company,title,price,uptime,phone from
tb1,tb2,tb3
where tb1.id=tb2.pid and tb1.user=tb3.user
and tb1.id=(select min(id) from tb1 where tb1.user = user)
order by uptime desc,tb1.id desc
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name,a.val
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七,如果整行数据有重复,所有的列都相同。
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决,生成一个自增列,先对val取最大或最小,然后再通过自增列来取数据。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数,不需要使用临时表。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
select t1.id,company,title,price,uptime,phone
from tb1 t1 , tb2 t2 , tb3 t3
where t1.id = t2.pid and t1.user = t3.user
and t1.title like '%123%' and
t1.uptime = (select max(uptime) from tb1 where user = t1.user)
order by ... --你自己加上排序
select t1.id,company,title,price,uptime,phone
from tb1 t1 , tb2 t2 , tb3 t3
where t1.id = t2.pid and t1.user = t3.user
and t1.title like '%123%' and
not exists (select 1 from tb1 where user = t1.user and uptime > t1.uptime)
order by ... --你自己加上排序
--5楼写错
select a.id,c.company,a.title,b.price,a.uptime,c.phone
from tb1 a join tb2 b on a.id = b.pid
join tb3 c on a.[user] = c.[user]
where a.title like '%123%'
and a.id = (select top 1 t.id from tb1 t join tb2 e on t.id = e.pid
where t.[user] = a.[user]
order by e.price desc,e.[time],t.uptime desc)
--try !
select a.id,c.company,a.title,b.price,a.uptime,c.phone
from tb1 a join tb2 b on a.id = b.pid
join tb3 c on a.[user] = c.[user]
where a.title like '%123%'
and a.id = (select top 1 t.id from tb1 t join tb2 e on t.id = e.pid order by e.price desc,e.[time],t.uptime desc)
;with ach as
(
select a.id,c.company,a.title,b.price,a.uptime,c.phone,
rid=row_number() over (partition by a.[user] order by b.price desc,b.[time],a.uptime desc)
from tb1 a join tb2 b on a.id = b.pid
join tb3 c on a.[user] = c.[user]
where a.title like '%123%'
)
select *
from ach
where rid = 1
order by uptime desc,id desc
--试试 tb1 的id 上有索引吧?
select tb1.id,company,title,price,uptime,phone from
tb1,tb2,tb3
where tb1.id=tb2.pid and tb1.user=tb3.user
order by uptime desc,tb1.id desc
and tb1.id=(select min(id) from tb1 where tb1.user = user)
select
a.id,c.company ,a.title,b.price,a.uptime,c.phone
from
tb1 a,tb2 b,tb3 c
where
a.id=b.pid
and
a.user=c.user
and
a.title=(select top 1 t1.title from tb1 t1,tb2 t2 where t1.id=t2.pid and t1.user=a.user and t1.title like '123%' order by b.price desc,b.time,a.uptime desc)
order by
a.uptime desc,a.id desc
select
a.id,c.company ,a.title,b.price,a.uptime,c.phone
from
tb1 a,tb2 b,tb3 c
where
a.id=b.pid
and
a.user=c.user
and
a.title=(select top 1 t1.title from tb1 t1,tb2 t2 where t1.id=t2.pid and t1.user=a.user and t1.title like '123%' order by b.price desc,b.time,a.uptime desc)