今天碰到个自以为难以解决的难题

[cheniqit1]

今天碰到个自以为难以解决的难题
有下面表的结构

id  size
1   4.5x
2   5x
3   4.5x
4   5x
5   5.5x 
6   4.5xx 
7   6x
8   5x
9   5x
10  6x

怎样得到如下数据

id  size
1   4.5x
2   5x
5   5.5x 
7   6x

[cheniqit1]

这么快啊。
刷选条件就是根据id来排序找出size唯一的

[billpu]

呵呵 是这个意思?

select min(id),size from tb group by size

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, size varchar(8))
insert into #
select 1, '4.5x' union all
select 2, '5x' union all
select 3, '4.5x' union all
select 4, '5x' union all
select 5, '5.5x' union all
select 6, '4.5x' union all
select 7, '6x' union all
select 8, '5x' union all
select 9, '5x' union all
select 10, '6x'

select * from # t where not exists (select 1 from # where size=t.size and id<t.id)

/*
id          size
----------- --------
1           4.5x
2           5x
5           5.5x
7           6x
*/

[SQLCenter]

[Quote=引用 4 楼 billpu 的回复:]

呵呵 是这个意思?
SQL code
select min(id),size from tb group by size
[/Quote]

是这个意思了，没发现是两字段，可以group by。

[cheniqit1]

嗯。我也认为不可以

[cheniqit1]

[Quote=引用 6 楼 sqlcenter 的回复:]
引用 4 楼 billpu 的回复:

呵呵 是这个意思?
SQL code
select min(id),size from tb group by size


是这个意思了，没发现是两字段，可以group by。
[/Quote]

嗯，我也认为是

[SQLCenter]

6 4.5xx


这条数据不知道你是手误还是别有需求

[cheniqit1]

6 4.5xx也是其中的一条数据

[「已注销」]

[Quote=引用 5 楼 sqlcenter 的回复:]
SQL code
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, size varchar(8))
insert into #
select 1, '4.5x' union all
select 2, '5x' union all
select 3, '4.……
[/Quote]
GG们，中间有个4.5XX的数据呢！

[SQLCenter]

[Quote=引用 11 楼 zsh0809 的回复:]

引用 5 楼 sqlcenter 的回复:
SQL code
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, size varchar(8))
insert into #
select 1, '4.5x' union all
select 2, '5x' ……
[/Quote]

我在说呢，楼主结果里面没有这条啊，按照 min(id) group by 就应该有6 4.5xx这条数据。

[chen8410]

select MIN(id) id,size
from [表]
group by size
order by id

[cheniqit1]

不好意思哈。
各位怪我不小心重新复述
有下面表的结构

id     size 
1       4.5x 
2       5x 
3       4.5x 
4       5x 
5       5.5x   
6       4.5xx   
7       6x 
8       5x 
9       5x 
10     6x 

怎样得到如下数据

id     size 
1       4.5x 
2       5x 
5       5.5x  
6       4.5xx  
7       6x 

[「已注销」]

CREATE TABLE #tp
(
	id INT IDENTITY,
	SIZE VARCHAR(10)	
)

INSERT INTO #tp SELECT '4.5x'
INSERT INTO #tp SELECT '5x'
INSERT INTO #tp SELECT '4.5x'
INSERT INTO #tp SELECT '5x'
INSERT INTO #tp SELECT '5.5x'
INSERT INTO #tp SELECT '4.5xx'
INSERT INTO #tp SELECT '6x'
INSERT INTO #tp SELECT '5x'
INSERT INTO #tp SELECT '5x'
INSERT INTO #tp SELECT '6x'

SELECT * FROM #TP T 
WHERE NOT EXISTS (SELECT 1 FROM #TP WHERE SIZE=T.SIZE AND ID<T.ID)
AND NOT EXISTS(SELECT NULL FROM #TP WHERE ID<T.ID AND  LEFT(T.SIZE,1)<LEFT(SIZE,1))

id          SIZE
----------- ----------
1           4.5x
2           5x
5           5.5x
7           6x

(4 row(s) affected)

[billpu]

还有这样一条数据呀

select * from tb where id in (select min(id) from tb group by replace(size,'x',''))

[billpu]

[Quote=引用 14 楼 cheniqit1 的回复:]

不好意思哈。
各位怪我不小心重新复述
有下面表的结构

Java code

id size
1 4.5x
2 5x
3 4.5x
4 5x
5 5.5x
6 4.5xx
7 6x
8 5x
9 5x
10 6x



怎样得到……
[/Quote]
晕

[cheniqit1]

[Quote=引用 16 楼 billpu 的回复:]
还有这样一条数据呀

SQL code
select * from tb where id in (select min(id) from tb group by replace(size,'x',''))
[/Quote]
我觉得replace这里会有问题 因为size可能会有 4.5y 5y 等等等

[billpu]

[Quote=引用 18 楼 cheniqit1 的回复:]

引用 16 楼 billpu 的回复:
还有这样一条数据呀

SQL code
select * from tb where id in (select min(id) from tb group by replace(size,'x',''))

我觉得replace这里会有问题 因为size可能会有 4.5y 5y 等等等
[/Quote]
呵呵 那一开始的语句就都可以了

[「已注销」]

到底要什么结果啊？

[「已注销」]

结果一：

SELECT * FROM #TP T 
WHERE NOT EXISTS (SELECT 1 FROM #TP WHERE SIZE=T.SIZE AND ID<T.ID)

id          SIZE
----------- ----------
1           4.5x
2           5x
5           5.5x
6           4.5xx
7           6x

(5 row(s) affected)

结果二：

SELECT * FROM #TP T 
WHERE NOT EXISTS (SELECT 1 FROM #TP WHERE SIZE=T.SIZE AND ID<T.ID)
AND NOT EXISTS(SELECT NULL FROM #TP WHERE ID<T.ID AND  LEFT(T.SIZE,1)<LEFT(SIZE,1))

id          SIZE
----------- ----------
1           4.5x
2           5x
5           5.5x
7           6x

(4 row(s) affected)

[cheniqit1]

谢谢各位热心的解答。非常感谢。