继续提问呀，求教！！！

[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

[worlddba]

顶楼上的

[worlddba]

顶楼上的

[liangCK]

SELECT DISTINCT B.*
FROM shop AS A
    CROSS APPLY(
        SELECT TOP 1 *
        FROM shop
        WHERE A.shopid=shopid
    ) AS B

[liangCK]

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-11-26 10:42:21
---------------------------------
 
--> 生成测试数据: @shop
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'

--SQL查询如下:

SELECT DISTINCT B.*
FROM @shop AS A
    CROSS APPLY(
        SELECT TOP 1 *
        FROM @shop
        WHERE A.shopid=shopid
    ) AS B


/*
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 行受影响)
*/

[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 行）
*/

[claro]

帮顶了。