查询100万数据去除重复排序问题

vjlin 2011-11-30 10:27:31
有三个表分别为tb1(产品资料),tb2(产品价格),tb3(公司信息),其中tb1.id=tb2.pid,tb1.user=tb3.user,tb1中有100万条数据,tb1中user有重复,tb2中pid无重复,tb3中user无重复。现在需要查询tb1中title包含字符“123”且user相同的只取按price desc,tb2.time,tb1.uptime desc排序后的第一条(即相同user只取一条),输出结果需要有tb1.id,company,title,price,uptime,phone,并按uptime desc,tb1.id desc 排序。请问怎样才能用一条语句快速查询出来?(SQL 2000)

tb1
------------
id user title uptime


tb2
------------
id pid price time


tb3
-------------
id user company phone
...全文
224 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2011-11-30
  • 打赏
  • 举报
回复
那个time是干什么用的,又不要查,还不知道它是哪个表中的,后来又说没有就用当前时间,可是,放在哪儿呢?

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

-晴天 2011-11-30
  • 打赏
  • 举报
回复
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)
快溜 2011-11-30
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 vjlin 的回复:]
谢谢大家的回复,有个情况忘记说明,就是tb1中的产品并不是在tb2中都有对应的记录(即在tb2中不一定有pid与tb1.id对应),但仍然需要把这部分数据也查询出来,当出现这种情况时该产品默认按price=0,time=当前时间来处理。
[/Quote]
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)

AcHerat 2011-11-30
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 vjlin 的回复:]
谢谢大家的回复,有个情况忘记说明,就是tb1中的产品并不是在tb2中都有对应的记录(即在tb2中不一定有pid与tb1.id对应),但仍然需要把这部分数据也查询出来,当出现这种情况时该产品默认按price=0,time=当前时间来处理。
[/Quote]

这时候把 tb1 join tb2 改为 tb1 a left join tb2 b 就可以了,tb3如果有类似情况也一样。
唐诗三百首 2011-11-30
  • 打赏
  • 举报
回复

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
vjlin 2011-11-30
  • 打赏
  • 举报
回复
谢谢大家的回复,有个情况忘记说明,就是tb1中的产品并不是在tb2中都有对应的记录(即在tb2中不一定有pid与tb1.id对应),但仍然需要把这部分数据也查询出来,当出现这种情况时该产品默认按price=0,time=当前时间来处理。
唐诗三百首 2011-11-30
  • 打赏
  • 举报
回复

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
PB菜鸟 2011-11-30
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 fredrickhu 的回复:]

SQL code
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……
[/Quote]
黄_瓜 2011-11-30
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 beirut 的回复:]

SQL code
--试试 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) f……
[/Quote]

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

dawugui 2011-11-30
  • 打赏
  • 举报
回复
如果上述语句不对,正参考如下的原理:

--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响)
*/
dawugui 2011-11-30
  • 打赏
  • 举报
回复
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 ... --你自己加上排序
AcHerat 2011-11-30
  • 打赏
  • 举报
回复

--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 !
AcHerat 2011-11-30
  • 打赏
  • 举报
回复

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)
AcHerat 2011-11-30
  • 打赏
  • 举报
回复

;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
黄_瓜 2011-11-30
  • 打赏
  • 举报
回复
--试试 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)
--小F-- 2011-11-30
  • 打赏
  • 举报
回复
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
--小F-- 2011-11-30
  • 打赏
  • 举报
回复
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)

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧