求一条查询语句

[zengzhimin1980]

我想写一条可以排除相同项的查询语句，当我这样写是没有问题：
select distinct shopid from shop

但当我这样写就失效了:
select distinct shopid,peason,viewdate from shop

这是为什么？我因该怎样写?用group by 好像也不行呀,peason,viewdate 都不唯一的

求教???????

[jiahao_li]

--查询shopid不重复的
select distinct shopid from shop
--查询shopid, peason, viewdate这些字段同时不重复的
select distinct shopid,peason,viewdate from shop

--两个肯定是不同的查询结果了

[dawugui]

--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响)
*/

[csdyyr]

很正常。最好给出数据我想要的结果。

[dawugui]

[Quote=引用楼主 zengzhimin1980 的帖子:]
我想写一条可以排除相同项的查询语句，当我这样写是没有问题：
select distinct shopid from shop

但当我这样写就失效了:
select distinct shopid,peason,viewdate from shop

这是为什么？我因该怎样写?用group by 好像也不行呀,peason,viewdate 都不唯一的

求教???????
[/Quote]
select * from tb t where peason = (select max(peason) from tb where shopid = t.shopid)
select * from tb t where peason = (select min(peason) from tb where shopid = t.shopid)

select * from tb t where viewdate = (select max(viewdate) from tb where shopid = t.shopid)
select * from tb t where viewdate = (select min(viewdate) from tb where shopid = t.shopid)

[zengzhimin1980]

表的结构是这样

表shop

shopid shopName peason viewdate
1 仓大分店1 张三 2008-12-14
1 仓大分店1 李四 2008-12-14
2 仓大分店2 黄五 2008-12-14
3 仓大分店3 张三 2008-12-14
1 仓大分店1 小明 2008-12-14
4 仓大分店4 小吉 2008-12-14
3 仓大分店3 小燕子 2008-12-14
2 仓大分店2 张三 2008-12-14

想得到唯一shopid 的记录，其它列没紧要,想得到以下结果

shopid shopName peason viewdate
1 仓大分店1 张三 2008-12-14
2 仓大分店2 黄五 2008-12-14
3 仓大分店3 张三 2008-12-14
4 仓大分店4 小吉 2008-12-14

[wang7655]

up

[dawugui]

DECLARE @shop TABLE (shopid INT,shopName VARCHAR(9),peason VARCHAR(6),viewdate DATETIME)
INSERT INTO @shop
SELECT 1,'仓大分店1','张三','2008-12-14' UNION ALL
SELECT 1,'仓大分店1','李四','2008-12-14' UNION ALL
SELECT 2,'仓大分店2','黄五','2008-12-14' UNION ALL
SELECT 3,'仓大分店3','张三','2008-12-14' UNION ALL
SELECT 1,'仓大分店1','小明','2008-12-14' UNION ALL
SELECT 4,'仓大分店4','小吉','2008-12-14' UNION ALL
SELECT 3,'仓大分店3','小燕子','2008-12-14' UNION ALL
SELECT 2,'仓大分店2','张三','2008-12-14'

select t.* from @shop t where peason = (select max(peason) from @shop where shopid = t.shopid) order by t.shopid
/*
shopid      shopName  peason viewdate                                               
----------- --------- ------ ------------------------------------------------------ 
1           仓大分店1     张三     2008-12-14 00:00:00.000
2           仓大分店2     张三     2008-12-14 00:00:00.000
3           仓大分店3     张三     2008-12-14 00:00:00.000
4           仓大分店4     小吉     2008-12-14 00:00:00.000

（所影响的行数为 4 行）
*/

select t.* from @shop t where peason = (select min(peason) from @shop where shopid = t.shopid) order by t.shopid
/*
shopid      shopName  peason viewdate                                               
----------- --------- ------ ------------------------------------------------------ 
1           仓大分店1     李四     2008-12-14 00:00:00.000
2           仓大分店2     黄五     2008-12-14 00:00:00.000
3           仓大分店3     小燕子    2008-12-14 00:00:00.000
4           仓大分店4     小吉     2008-12-14 00:00:00.000

（所影响的行数为 4 行）
*/