SQL削除
69977 ワード
私の元の問題
Name Path Time 1 c:20:00 1 f:20:00 1 d:21:00 2 f:20:00戻ることを要求1 c:2 f:または1 f:2 f:戻ることができません1 c:1 f:2 f:この中にプライマリキーがなく、前の2列のselect Name、Pathを返すことを要求します.同じNameを要求するメタセットの中でタイムを取るのが一番小さくて、残りは取り出すことができません
検索して、以下のnewb codeを見つけて、特に秘蔵します
SQL code
リードhttp://znsw.blog.hexun.com/22802673_d.html
Name Path Time 1 c:20:00 1 f:20:00 1 d:21:00 2 f:20:00戻ることを要求1 c:2 f:または1 f:2 f:戻ることができません1 c:1 f:2 f:この中にプライマリキーがなく、前の2列のselect Name、Pathを返すことを要求します.同じNameを要求するメタセットの中でタイムを取るのが一番小さくて、残りは取り出すことができません
検索して、以下のnewb codeを見つけて、特に秘蔵します
SQL code
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
--
DB2 varchar int cast, SQL server .
select
t.
*
from
testsql t
where
cast
(hours
as
int
)
=
(
select
max
(
cast
(hours
as
int
))
from
testsql
where
name
=
t.name)
order
by
name
--
SQL server :
--
( )
( .
2007
-
10
-
23 )
/*
： name val memo a 2 a2(a ) a 1 a1--a a 3 a3:a b 1 b1--b b 3 b3:b b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5
*/
--
：
create
table
tb(name
varchar
(
10
),val
int
,memo
varchar
(
20
))
insert
into
tb
values
(
'
a
'
,
2
,
'
a2(a )
'
)
insert
into
tb
values
(
'
a
'
,
1
,
'
a1--a
'
)
insert
into
tb
values
(
'
a
'
,
3
,
'
a3:a
'
)
insert
into
tb
values
(
'
b
'
,
1
,
'
b1--b
'
)
insert
into
tb
values
(
'
b
'
,
3
,
'
b3:b
'
)
insert
into
tb
values
(
'
b
'
,
2
,
'
b2b2b2b2
'
)
insert
into
tb
values
(
'
b
'
,
4
,
'
b4b4
'
)
insert
into
tb
values
(
'
b
'
,
5
,
'
b5b5b5b5b5
'
)
go
--
、 name val 。
--
1：
select
a.
*
from
tb a
where
val
=
(
select
max
(val)
from
tb
where
name
=
a.name)
order
by
a.name
--
2：
select
a.
*
from
tb a
where
not
exists
(
select
1
from
tb
where
name
=
a.name
and
val
>
a.val)
--
3：
select
a.
*
from
tb a,(
select
name,
max
(val) val
from
tb
group
by
name) b
where
a.name
=
b.name
and
a.val
=
b.val
order
by
a.name
--
4：
select
a.
*
from
tb a
inner
join
(
select
name ,
max
(val) val
from
tb
group
by
name) b
on
a.name
=
b.name
and
a.val
=
b.val
order
by
a.name
--
5
select
a.
*
from
tb a
where
1
>
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
>
a.val )
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 3 a3:a b 5 b5b5b5b5b5
*/
--
、 name val 。
--
1：
select
a.
*
from
tb a
where
val
=
(
select
min
(val)
from
tb
where
name
=
a.name)
order
by
a.name
--
2：
select
a.
*
from
tb a
where
not
exists
(
select
1
from
tb
where
name
=
a.name
and
val
<
a.val)
--
3：
select
a.
*
from
tb a,(
select
name,
min
(val) val
from
tb
group
by
name) b
where
a.name
=
b.name
and
a.val
=
b.val
order
by
a.name
--
4：
select
a.
*
from
tb a
inner
join
(
select
name ,
min
(val) val
from
tb
group
by
name) b
on
a.name
=
b.name
and
a.val
=
b.val
order
by
a.name
--
5
select
a.
*
from
tb a
where
1
>
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
<
a.val)
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 1 a1--a b 1 b1--b
*/
--
、 name 。
select
a.
*
from
tb a
where
val
=
(
select
top
1
val
from
tb
where
name
=
a.name)
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 2 a2(a ) b 1 b1--b
*/
--
、 name 。
select
a.
*
from
tb a
where
val
=
(
select
top
1
val
from
tb
where
name
=
a.name
order
by
newid
())
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 1 a1--a b 5 b5b5b5b5b5
*/
--
、 name (N )val
select
a.
*
from
tb a
where
2
>
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
<
a.val )
order
by
a.name,a.val
select
a.
*
from
tb a
where
val
in
(
select
top
2
val
from
tb
where
name
=
a.name
order
by
val)
order
by
a.name,a.val
select
a.
*
from
tb a
where
exists
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
<
a.val
having
Count
(
*
)
<
2
)
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 1 a1--a a 2 a2(a ) b 1 b1--b b 2 b2b2b2b2
*/
--
、 name (N )val
select
a.
*
from
tb a
where
2
>
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
>
a.val )
order
by
a.name,a.val
select
a.
*
from
tb a
where
val
in
(
select
top
2
val
from
tb
where
name
=
a.name
order
by
val
desc
)
order
by
a.name,a.val
select
a.
*
from
tb a
where
exists
(
select
count
(
*
)
from
tb
where
name
=
a.name
and
val
>
a.val
having
Count
(
*
)
<
2
)
order
by
a.name
/*
name val memo ---------- ----------- -------------------- a 2 a2(a ) a 3 a3:a b 4 b4b4 b 5 b5b5b5b5b5
*/
--
， ， 。
/*
： name val memo a 2 a2(a ) a 1 a1--a a 1 a1--a a 3 a3:a a 3 a3:a b 1 b1--b b 3 b3:b b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5
*/
--
sql server 2000 ， ， val ， 。
--
：
create
table
tb(name
varchar
(
10
),val
int
,memo
varchar
(
20
))
insert
into
tb
values
(
'
a
'
,
2
,
'
a2(a )
'
)
insert
into
tb
values
(
'
a
'
,
1
,
'
a1--a
'
)
insert
into
tb
values
(
'
a
'
,
1
,
'
a1--a
'
)
insert
into
tb
values
(
'
a
'
,
3
,
'
a3:a
'
)
insert
into
tb
values
(
'
a
'
,
3
,
'
a3:a
'
)
insert
into
tb
values
(
'
b
'
,
1
,
'
b1--b
'
)
insert
into
tb
values
(
'
b
'
,
3
,
'
b3:b
'
)
insert
into
tb
values
(
'
b
'
,
2
,
'
b2b2b2b2
'
)
insert
into
tb
values
(
'
b
'
,
4
,
'
b4b4
'
)
insert
into
tb
values
(
'
b
'
,
5
,
'
b5b5b5b5b5
'
)
go
select
*
, px
=
identity
(
int
,
1
,
1
)
into
tmp
from
tb
select
m.name,m.val,m.memo
from
(
select
t.
*
from
tmp t
where
val
=
(
select
min
(val)
from
tmp
where
name
=
t.name) ) m
where
px
=
(
select
min
(px)
from
(
select
t.
*
from
tmp t
where
val
=
(
select
min
(val)
from
tmp
where
name
=
t.name) ) n
where
n.name
=
m.name)
drop
table
tb,tmp
/*
name val memo ---------- ----------- -------------------- a 1 a1--a b 1 b1--b (2 )
*/
--
sql server 2005 row_number ， 。
--
：
create
table
tb(name
varchar
(
10
),val
int
,memo
varchar
(
20
))
insert
into
tb
values
(
'
a
'
,
2
,
'
a2(a )
'
)
insert
into
tb
values
(
'
a
'
,
1
,
'
a1--a
'
)
insert
into
tb
values
(
'
a
'
,
1
,
'
a1--a
'
)
insert
into
tb
values
(
'
a
'
,
3
,
'
a3:a
'
)
insert
into
tb
values
(
'
a
'
,
3
,
'
a3:a
'
)
insert
into
tb
values
(
'
b
'
,
1
,
'
b1--b
'
)
insert
into
tb
values
(
'
b
'
,
3
,
'
b3:b
'
)
insert
into
tb
values
(
'
b
'
,
2
,
'
b2b2b2b2
'
)
insert
into
tb
values
(
'
b
'
,
4
,
'
b4b4
'
)
insert
into
tb
values
(
'
b
'
,
5
,
'
b5b5b5b5b5
'
)
go
select
m.name,m.val,m.memo
from
(
select
*
, px
=
row_number()
over
(
order
by
name , val)
from
tb ) m
where
px
=
(
select
min
(px)
from
(
select
*
, px
=
row_number()
over
(
order
by
name , val)
from
tb ) n
where
n.name
=
m.name)
drop
table
tb
/*
name val memo ---------- ----------- -------------------- a 1 a1--a b 1 b1--b (2 )
*/
リードhttp://znsw.blog.hexun.com/22802673_d.html