多表排除查询

Question

请看我的表cma表cid  sid ctitle
smart表sid stitleuhat表uid  sid cid uway  utime
我现在想调出cma表中的前10条数据，具体要求是1.这十条数据中的sid必须是都不相同的，相同的sid只能显示一条
2.这十条数据的cid在uhat表存在，并且满足utime>=getdate()或者uway=0最后显示的结果是cid  ctitle  stitle   utime请问这个该怎么做呢？谢谢

Answer 1

select top 10 b.cid, b.ctitle, a.stitle, c.utime
from smart a
join cma b on a.sid=b.sid
join uhat c on a.sid=c.sid and b.cid=c.cid
where c.uid = (select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0))

Answer 2

--where c.uid = (select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0))
  where c.uid = (select top 1 uid from uhat where sid=c.sid and (utime>=getdate() or uway=0))

Answer 3

好像不对，uway=0的数据没有读出来

Answer 4

请问如何才能吧这个uway=0的数据给读出来呢？谢谢

Answer 5

应该不是没有读出来吧,可能是前面的条件已经把uway=0的行排除了,所以最后的结果里没看到uway=0的

Answer 6

我现在想调出cma表中的前10条数据，具体要求是1.这十条数据中的sid必须是都不相同的，相同的sid只能显示一条
2.这十条数据的cid在uhat表存在，并且满足utime>=getdate()或者uway=0 
--------------------------------------注意是 OR ：
utime>=getdate()或者uway=0前10条数据就一定有uway=0的了？把 top 10 去掉看看有没有，假如还没有，那uhat根本就没有uway=0的纪录。

Answer 7

我把TOP 10去掉了，可惜是没有，但是我不加其它条件，这个里面肯定是有uway=0的数据的

Answer 8

而且最主要的是，所显示出来的数据中，全部是utime>getdate()，而且还不足10条，而uway=0是一条都没显示出来，（我确保数据库中有满足条件的数据）

Answer 9

[code=SQL]
/*
大概明白怎么会事：
select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0)
因为c.utime>=getdate()在前面，找到一条符合的就不会再找了
*/-->tryselect b.cid, b.ctitle, a.stitle, c.utime
from smart a
join cma b on a.sid=b.sid
join uhat c on a.sid=c.sid and b.cid=c.cid
where c.uid = (select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0) order by newid())
[/code]

Answer 10

这样修改导致的结果是数据不断变化，而且最主要的是，这里的sid有相同的，而且每次刷新后，显示满足条件的数据条数还不一样，有时候显示1条，有时候显示2条，有时候显示0条

Answer 11

-->try
select b.cid, b.ctitle, a.stitle, c.utime
from smart a
join cma b on a.sid=b.sid
join uhat c on a.sid=c.sid and b.cid=c.cid
and c.uid = (select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0) order by newid())

Answer 12

这样修改导致的结果是 数据不断变化，而且最主要的是，这里的sid有相同的，而且每次刷新后，显示满足条件的数据条数还不一样，有时候显示1条，有时候显示2条，有时候显示0条

Answer 13

楼上的好像是从表smart中取前10,而LZ的意图是从cma取前10

Answer 14

我了解你所说的情况
无解存储过程搞了，各取5条 UNION ALL

Answer 15

cma表 cid  sid ctitle 
smart表 sid stitle 
uhat表 uid  sid cid uway  utime 
我现在想调出cma表中的前10条数据，具体要求是 
1.这十条数据中的sid必须是都不相同的，相同的sid只能显示一条 
2.这十条数据的cid在uhat表存在，并且满足utime>=getdate()或者uway=0 
最后显示的结果是 cid  ctitle  stitle   utime 
---------------------------------------------------------------
注：cma.cid smart.sid uhat.uid  必须是主键
select top 10 cid,(select ctitle from cma where cid = a.cid) ctitle,
(select stitle from smart where sid = a.dis) stitle,utime
from uhat a  where uid =
(select top 1 uid from uhat where sid = a.sid and (utime>=getdate() or uway=0))

Answer 16

要求 cma.cid 必须为主键，其它不限
select top 10 cid,ctitle,(select top 1 stitle from smart where sid = a.sid) stitle,
(select top 1 utime from where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) utime
from cma a
where cid = (select top 1 cid from cma where sid= a.sid)
and exists(select 1 from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0))

Answer 17

要求 cma.cid 必须为主键，其它不限 select top 10 cid,ctitle,(select top 1 stitle from smart where sid = a.sid) stitle,
(select top 1 utime from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) utime
from cma a where cid = (select top 1 cid from cma where sid= a.sid)
and exists(select 1 from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0))

Answer 18

上面的要求不对，只要求cma.cid,cma.sid 在表中唯一就行，其它不限。

Answer 19

LZ的意图MS说sid在cma中不是唯一出现,然后只想显示重复出现中的一条,不理会其他的行?
/**从cma中取前10**/
select top 10 *
from cma
/**加入重复sid只显示一条的限制,待研究**//**将smart表中stitle链接到查询**/
select top 10 c.cid,c.ctitle,s.stitle
from cma c
left join smart s on s.sid=c.sid
/**将uhat表中的utime连接到查询**/
select top 10 c.cid,c.ctitle,s.stitle,u.utime
from cma c
left join smart s on s.sid=c.sid
left join uhat u on u.uid = c.cid
/**加入限制条件**/
select top 10 c.cid,c.ctitle,s.stitle,u.utime
from cma c
left join smart s on s.sid=c.sid
left join uhat u on u.uid = c.cid
where u.utime>=getdate() or uway = '0';

Answer 20

本题的思路很简单：从表中取出某字段第一次出现的记录。
针对该题目需要表的记录不能完全一样，如果完全一样，就需要增加临时表自动生成序号，通过序号获取。
分布实现：
--1、取每个sid的第一条记录,假定cid,sid组合在表中唯一，不唯一就要通过临时表实现,这个大家都会。
 select * from cma a where cid = (select top 1 cid from sid = a.sid)
--2、增加难度，只要前10条记录，并且记录必须符合一定的条件。在下面改了lz的意思，要求cid,sid组合必须存在。
--   我觉得这样才有意义，如果LZ坚持只要CID存在就行那就去掉 and sid = a.sid。
 select top 10 * from cma a  --取前10条
 where exists (select 1 from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) --增加条件 
 and cid = (select top 1 cid from sid = a.sid) --这个大家应该很熟悉
--3、再增加难度，我要求显示的字段还有其它表的，其他表可以认为是字典表。
 select cid,ctitle --原表字段，直接取
 ,(select top 1 stitle from smart where sid = a.sid) stitle --字典表，增加top 1 防止字典表不规范。
,(select top 1 utime from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) utime --视同字典表，同上。
 where exists (select 1 from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) --增加条件 
 and cid = (select top 1 cid from sid = a.sid) --这个大家应该很熟悉完整语句如下：
select top 10 cid,ctitle,(select top 1 stitle from smart where sid = a.sid) stitle,
(select top 1 utime from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0)) utime
from cma a where cid = (select top 1 cid from cma where sid= a.sid)
and exists (select 1 from uhat where cid = a.cid and sid = a.sid and(utime>=getdate() or uway=0))

Answer 21

现在问题不是取不到唯一sid的问题，而是：
select top 1 uid from uhat where sid=c.sid and (utime>=getdate() or uway=0)
在楼主的数据里，这个 top 1 uid 永远命中的是 c.utime>=getdate() 并且 uway<>0 所对应的 uid，所以取不到 uway=0 的数据，看下面的例子就明白了。if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (uid int identity, cid int, uway int, utime datetime)
insert #T select 1, 1, getdate()+1
insert #T select 1, 0, getdate()-1
insert #T select 2, 1, getdate()+2
insert #T select 2, 0, getdate()-2select * from #T a
where uid=(select top 1 uid from #T where cid=a.cid)
and exists (select 1 from #T where cid=a.cid and (utime>getdate() or uway=0))/*
uid         cid         uway        utime
----------- ----------- ----------- -----------------------
1           1           1           2008-04-21 17:59:47.997
3           2           1           2008-04-22 17:59:47.997
*/-->上面是老兄写的语句，与我写的效果是一样的
select * from #T a where uid=(select top 1 uid from #T where cid=a.cid and (utime>getdate() or uway=0))/*
uid         cid         uway        utime
----------- ----------- ----------- -----------------------
1           1           1           2008-04-21 17:59:47.997
3           2           1           2008-04-22 17:59:47.997
*/--楼主的这种需求，肯定要拆分成两条语句的，对两个 OR 条件分别处理

Answer 22

小楼糊涂了。
肯定是这样的，如果第一条数据是uway＝0就取得到呀, 反正是取满足条件的第一条记录。
楼主要的也是这个呀。

Answer 23

小楼参看下面：
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (uid int identity, cid int, uway int, utime datetime)
insert #T select 1, 1, getdate()-1
insert #T select 1, 1, getdate()+1 --m2 
insert #T select 2, 1, getdate()+1 --m3
insert #T select 2, 0, getdate()-1
insert #T select 3, 0, getdate()-1 --m5
insert #T select 3, 1, getdate()+1
insert #T select 4, 0, getdate()+1 --m7
insert #T select 4, 1, getdate()+1select * from #T a
where uid=(select top 1 uid from #T where cid=a.cid and  (utime>getdate() or uway=0))
    and exists (select 1 from #T where cid=a.cid and (utime>getdate() or uway=0))
/*
uid         cid         uway        utime                                                  
----------- ----------- ----------- ------------------------------------------------------ 
2           1           1           2008-04-21 18:21:42.597
3           2           1           2008-04-21 18:21:42.597
5           3           0           2008-04-19 18:21:42.597
7           4           0           2008-04-21 18:21:42.597
*/

Answer 24

呵呵，我没糊涂，你可能没有留意楼主的反馈信息：
由此我推断楼主的数据里，满足条件的第一条记录里，没有uway=0的纪录，即只有我举例的情况，而没有老兄你举例的情况。如果存在你举例的情况，去掉top 10结果集中肯定存在uway=0的数据，然否？而通过order by newid()随机取一条，是不准确的，只有在数据量非常小的情况下才基本上逼近正确：select count(*) from syscolumns t
where colid=(select top 1 colid from syscolumns where id=t.id order by newid())上面语句每次运行的结果都不同。

Answer 25

是否是因为uway=0的那些记录,被相同的sid屏蔽的呢?如果其他uway值大于零,是否可以考虑以 uway排个序,将uway=0的行置前,不就查得到了吗.
select top 10 b.cid, b.ctitle, a.stitle, c.utime
from smart a
join cma b on a.sid=b.sid
join uhat c on a.sid=c.sid and b.cid=c.cid
where c.uid = (select top 1 uid from uhat where sid=c.sid and (c.utime>=getdate() or uway=0)order by uway)

Answer 26

如果存在你举例的情况，去掉top 10结果集中肯定存在uway=0的数据，然否？
----------------------------------------------------------
不然，与top 10 无关，关键在下面：
--你的语句：
select * from #T a
where uid=(select top 1 uid from #T where cid=a.cid)
    and exists (select 1 from #T where cid=a.cid and (utime>getdate() or uway=0))
--我的语句：
select * from #T a
where uid=(select top 1 uid from #T where cid=a.cid and  (utime>getdate() or uway=0))
    and exists (select 1 from #T where cid=a.cid and (utime>getdate() or uway=0))
看到区别了吗？

Answer 27

其实在你举的例子中 
and exists (select 1 from #T where cid=a.cid and (utime>getdate() or uway=0))
是多余的。

Answer 28

1.这十条数据中的sid必须是都不相同的，相同的sid只能显示一条 
select top 10 t.* from cma t where cid = (select min(cid) from cma where sid = t.sid)
select top 10 t.* from cma t where cid = (select max(cid) from cma where sid = t.sid)其他方法见下:
--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 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
/*
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
/*
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')
goselect * , px = identity(int,1,1) into tmp from tbselect 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')
goselect 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 行受影响)
*/

Answer 29

2.这十条数据的cid在uhat表存在，并且满足utime>=getdate()或者uway=0 最后显示的结果是 cid  ctitle  stitle   utime select a.cid , a.ctitle , b.stitle , c.utime from
(
  select top 10 t.* from cma t where cid = (select min(cid) from cma where sid = t.sid) and cid in (select cid from what and utime >= getdate() and uway = 0)
) a , smart b , what c
where a.cid = b.cid and a.cid = c.cid

Answer 30

--问题可能在于在cma表中无法由值惟一的确定一条记录，
--即已知cid,sid,ctitle也可能找到多条记录
--所以应有惟一辨识列。这样才方便处理--建立内存表
declare @cma table (cid int,sid int,ctitle nvarchar(20) )
declare @smart table(sid int,stitle nvarchar(20))
declare @uhat table(uid int, sid int, cid int, uway int,  utime datetime)--插入数据
insert into @cma select 1,1,'1'
union all select 2,2,'2'
union all select 3,2,'3'
union all select 1,3,'2'insert into @smart select 1,'1'
union all select 2,'2'insert into @uhat select 1,1,1,1,getdate()
union all select 1,2,2,0,getdate()
union all select 1,4,2,1,dateadd(mm,1,getdate())
--现在我们在初步筛选
--这十条数据的cid在uhat表存在，并且满足utime>=getdate()或者uway=0
--的条件下建立具有惟一标志gid的临时表
select identity(int,1,1) as gid,
a.*,b.utime
into #tmp
from @cma a
inner join @uhat b on a.cid=b.cid and ( uway=0 or utime>=getdate())
select top 10 c.sid,c.cid,c.ctitle,c.utime,e.stitle 
from #tmp c 
inner join (select distinct sid,min(gid) as gid from #tmp group by sid) d on c.gid=d.gid--以min(gid)为依据选择惟一sid
inner join @smart e on e.sid=c.sid--要求sid存在于表smart中drop table #tmp