SELECT TOP 6 et.name, quote.bid, quote.ask, et.PX, quote.datetime FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) ORDER BY quote.id DESC我的代码是这样的 现在要选择id不同的 应该怎么写?
用group by吧!不過不見得要是你上面這樣的話,不見得能找出唯一的6條id來
有没有唯一值字段.如果有可以用max来取
select distinct ID from Table
select top 6 et.name, quote.bid, quote.ask, et.PX, quote.datetime from FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) and et.id in(select distint(id) from et) ORDER BY quote.id DESC
SELECT TOP 6 et.name, quote.bid, quote.ask, et.PX, quote.datetime FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) group by et.name, quote.bid, quote.ask, et.PX, quote.datetime having count(1) = 1 ORDER BY quote.id DESC
SELECT TOP 6 et.name, quote.bid, quote.ask, et.PX, quote.datetime FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) group by quote.id, et.name, quote.bid, quote.ask, et.PX, quote.datetime having count(1) = 1 ORDER BY quote.id DESC
SELECT TOP 6 et.name, quote.bid, quote.ask, et.PX, quote.datetime FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) group by quote.id, et.name, quote.bid, quote.ask, et.PX, quote.datetime having count(1) = 1 ORDER BY quote.id DESC
还是不对! et.id还是有重复!!!
其实很简单:selct * from table where id in ( select ID from Table group by id having count(*) = 1 )
你取 数据的时候 遇到 重复的 ID 你怎么区别取哪一条? 你要的SQL语句的关键就是取哪一条这个条件
select TOP 6 name, quote.bid, quote.ask, PX, quote.datetime from quote join ( SELECT et.id,et.name,et.PX FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) group by et.id, et.name, et.PX having count(1) = 1 ) as T ORDER BY quote.bid DESC
select TOP 6 name, quote.bid, quote.ask, PX, quote.datetime from quote join ( SELECT et.id,et.name,et.PX FROM et INNER JOIN quote ON et.id = quote.code WHERE (quote.code = 72) OR (quote.code = 73) OR (quote.code = 216) OR (quote.code = 215) OR (quote.code = 217) OR (quote.code = 218) group by et.id, et.name, et.PX having count(1) = 1 ) as T on T.id = quote.code ORDER BY quote.bid DESC
select max(id),max(...),max(...) from 表 group by id
select distinct ID from Table
quote.datetime
FROM et INNER JOIN
quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
ORDER BY quote.id DESC我的代码是这样的 现在要选择id不同的 应该怎么写?
quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218) and et.id in(select distint(id) from et)
ORDER BY quote.id DESC
quote.datetime
FROM et INNER JOIN quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
group by et.name, quote.bid, quote.ask, et.PX, quote.datetime
having count(1) = 1
ORDER BY quote.id DESC
quote.datetime
FROM et INNER JOIN quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
group by quote.id, et.name, quote.bid, quote.ask, et.PX, quote.datetime
having count(1) = 1
ORDER BY quote.id DESC
quote.datetime
FROM et INNER JOIN quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
group by quote.id, et.name, quote.bid, quote.ask, et.PX, quote.datetime
having count(1) = 1
ORDER BY quote.id DESC
et.id还是有重复!!!
where id in
(
select ID from Table group by id having count(*) = 1
)
et.id不是唯一的他们是有重复
但是quote是唯一的
现在我想选出et.id为72,73,215,216,217,218的记录
(注意这些编号在et.id中是重复出现的)
我想选出最新的并且编号为72,73,215,216,217,218的记录
我的代码如何写才能答到那种效果?
from quote join
(
SELECT et.id,et.name,et.PX
FROM et INNER JOIN quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
group by et.id, et.name, et.PX
having count(1) = 1
) as T
ORDER BY quote.bid DESC
from quote join
(
SELECT et.id,et.name,et.PX
FROM et INNER JOIN quote ON et.id = quote.code
WHERE (quote.code = 72) OR
(quote.code = 73) OR
(quote.code = 216) OR
(quote.code = 215) OR
(quote.code = 217) OR
(quote.code = 218)
group by et.id, et.name, et.PX
having count(1) = 1
) as T
on T.id = quote.code
ORDER BY quote.bid DESC