item oem oem_no
0001 a b
0001 c m
0001 a c
0002 q v
0002 q v
0003 r a
想得到,相同的item只取一条数据(可以任意一条)得到
0001 a b
0002 q v
0003 r a
0001 a b
0001 c m
0001 a c
0002 q v
0002 q v
0003 r a
想得到,相同的item只取一条数据(可以任意一条)得到
0001 a b
0002 q v
0003 r a
解决方案 »
- 请教一条SQL命令,请帮忙
- 如何快速的生成本年度的全部日期
- ireport 怎么连接 ms sql 数据库。出现如下问题:
- 如何实现某个列,可以为null,但是只要不为空,就必须是惟一的.如何设置呀?我无论如何也设置不出来.
- '2004-12'字符转日期类型比较
- SQL Server疑难问题
- 简单的多表查询 怎么会出错?请大家指教!!1
- 请问大家,在access中作查询,这么实现count distinct
- 请教:如何在存储过程控制产生的错误信息;gzhughie(hughie),wylyf(李寻欢)请进
- SQL SERVER中的怪问题
- 如何得到今天的00:00:00到今天的23:59:59秒?
- [急]关于SQL查询语句的问题
from
(select *,row=row_number()over(partition by item order by oem) from t1)t
where row=1
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?32698
create table tb(item varchar(10), oem varchar(10), oem_no varchar(10))
insert into tb values('0001' , 'a' , 'b')
insert into tb values('0001' , 'c' , 'm')
insert into tb values('0001' , 'a' , 'c')
insert into tb values('0002' , 'q' , 'v')
insert into tb values('0002' , 'q' , 'v')
insert into tb values('0003' , 'r' , 'a')
go
select distinct t.* from tb t where oem_no = (select min(oem_no) from tb where item = t.item)select distinct t.* from tb t where not exists (select 1 from tb where item = t.item and oem_no < t.oem_no)select distinct item,oem,oem_no from
(
select t.* , px = (select count(1) from tb where item = t.item and (oem_no < t.oem_no or (oem_no = t.oem_no and oem < t.oem) )) + 1 from tb t
) m where px = 1drop table tb /*
item oem oem_no
---------- ---------- ----------
0001 a b
0002 q v
0003 r a(所影响的行数为 3 行)
*/
distinct *
from
tb t
where
not exists (select 1 from tb where item = t.item and oem_no < t.oem_no)
DECLARE @t TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @t
SELECT 001,'a','b' UNION ALL
SELECT 001,'c','m' UNION ALL
SELECT 001,'a','c' UNION ALL
SELECT 002,'q','v' UNION ALL
SELECT 002,'q','v' union all
SELECT 003,'r','a'select id,tac,tbc
from
(select *,row=row_number()over(partition by id order by id) from @t)t
where row=3