DECLARE @ta TABLE([AID] int, [AVal] varchar(10))
INSERT INTO @ta
SELECT 1, '1.txt'
UNION ALL SELECT 2, '2.txt'
DECLARE @tb TABLE([BID] int, [TAID] int, [BVal] varchar(10))
INSERT INTO @tb
SELECT 100, 1, '100.txt'
UNION ALL SELECT 101, 2, '101.txt'
UNION ALL SELECT 102, 1, '102.txt'
UNION ALL SELECT 103, 2, '103.txt'
UNION ALL SELECT 104, 1, '104.txt'SELECT TOP 1 A.AID, A.AVal, B.BID, B.BVal FROM @ta A
INNER JOIN @tb B
ON A.AID = B.TAID
ORDER BY NEWID()--随即抽出一条
INSERT INTO @ta
SELECT 1, '1.txt'
UNION ALL SELECT 2, '2.txt'
DECLARE @tb TABLE([BID] int, [TAID] int, [BVal] varchar(10))
INSERT INTO @tb
SELECT 100, 1, '100.txt'
UNION ALL SELECT 101, 2, '101.txt'
UNION ALL SELECT 102, 1, '102.txt'
UNION ALL SELECT 103, 2, '103.txt'
UNION ALL SELECT 104, 1, '104.txt'SELECT TOP 1 A.AID, A.AVal, B.BID, B.BVal FROM @ta A
INNER JOIN @tb B
ON A.AID = B.TAID
ORDER BY NEWID()--随即抽出一条
解决方案 »
- 关于给 sql数据库加密码?急
- 高分求一段SQL代码 急!!!!!!!!
- [sql查询]华为的一个面试题
- 一个SQL语句的问题
- 怎样设置某一DateTime字段的默认值是特定的值,比如1900-1-1
- sql server 2008 链接服务器问题
- 求一个存储过程
- 求教Sql2000 UniCode问题
- sql server 2005数据库镜像问题
- 用InterDev 写Asp,用代码访问sql server没错,可是用DTC控件加入数据连接和数据集后,运行时就显示服务器内部错误为什么?
- 请问怎样导入test.dat文件到sql server 2000中啊?
- 关于自定义函数中加入GetDate()出错的问题,请高手指点
如果取最大则为:
(1,'1.txt',104,'104.txt')(2,'2.txt',103,'103.txt')
select a.AID,a.AVal,b.bid,b.bval from ta a,tb b,(
select taid , max(bid) as bid from tb group by taid) c
where a.aid = b.taid and a.aid = c.taid and b.bid = c.bid如果取最小则为:
(1,'1.txt',100,'100.txt')(2,'2.txt',101,'101.txt')
select a.AID,a.AVal,b.bid,b.bval from ta a,tb b,(
select taid , min(bid) as bid from tb group by taid) c
where a.aid = b.taid and a.aid = c.taid and b.bid = c.bid
而你的结果(1,'1.txt',102,'102.txt')(2,'2.txt',101,'101.txt')怎么取?
不好意思,我想你误会我的意思了,我的最终结果不是只取一条,而是对于每一个AID来说,只取一条记录。在本例中,返回结果应该是2条期待回帖:)
DECLARE @ta TABLE([AID] int, [AVal] varchar(10))
INSERT INTO @ta
SELECT 1, '1.txt'
UNION ALL SELECT 2, '2.txt'
DECLARE @tb TABLE([BID] int, [TAID] int, [BVal] varchar(10))
INSERT INTO @tb
SELECT 100, 1, '100.txt'
UNION ALL SELECT 101, 2, '101.txt'
UNION ALL SELECT 102, 1, '102.txt'
UNION ALL SELECT 103, 2, '103.txt'
UNION ALL SELECT 104, 1, '104.txt'SELECT BID, TAID, BVal, NEWID() AS NWID INTO #T FROM @tbSELECT A.AID, A.AVal, B.BID, B.BVal
FROM @ta A
INNER JOIN (SELECT BID, TAID, BVal FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE TAID = A.TAID AND NWID > A.NWID))B
ON A.AID = B.TAIDDROP TABLE #T不随机的话
--最大的BID
SELECT A.AID, A.AVal, B.BID, B.BVal
FROM @ta A
INNER JOIN (SELECT BID, TAID, BVal FROM @tb A WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE TAID = A.TAID AND BID > A.BID))B
ON A.AID = B.TAID
--最小的BID
SELECT A.AID, A.AVal, B.BID, B.BVal
FROM @ta A
INNER JOIN (SELECT BID, TAID, BVal FROM @tb A WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE TAID = A.TAID AND BID < A.BID))B
ON A.AID = B.TAID
我再研究一下,“WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE TAID = A.TAID AND BID < A.BID))”这句比较难懂
hhhdyj(萤火虫) ,高手啊!
对于同一个TAID,不存比BID还小的记录,就说明这个BID是这个TAID中最小的了。