请教一个query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

请教一个query

请教一个query# Database - 数据库

c*y2005-05-04 07:05

1 楼

有一个大table把数据都浑在一起了, 结构酱紫:
BookId DataName DataValue
1 Title t1
1 Author a1
1 Publisher p1
2 Title t2
2 Author a2
2 Publisher p2
3 Author a3
3 Publisher p3
如何把所有没有Title的BookId选出来, 不是选Title值为空的, 而是根本就没有这一行的
//bow

s*e2005-05-04 07:05

2 楼

(select distinct bookid from table) except
(select distinct bookid from table where dataname = 'title' )

【在 c***y 的大作中提到】

: 有一个大table把数据都浑在一起了, 结构酱紫:
: BookId DataName DataValue
: 1 Title t1
: 1 Author a1
: 1 Publisher p1
: 2 Title t2
: 2 Author a2
: 2 Publisher p2
: 3 Author a3
: 3 Publisher p3

c*y2005-05-04 07:05

3 楼

thanks!
hmm.. seems mysql doesn't support EXCEPT, doesn't support nested queries
neither

【在 s***e 的大作中提到】

: (select distinct bookid from table) except
: (select distinct bookid from table where dataname = 'title' )

s*e2005-05-04 07:05

4 楼

I know nothing about mysql. hehe.
Just checked its documents, it seems mysql supports subqueries now.
If so, you could write in this way:
select distinct bookid from table where
bookid not in ( select distinct bookid from table where dataname = 'title')

【在 c***y 的大作中提到】

: thanks!
: hmm.. seems mysql doesn't support EXCEPT, doesn't support nested queries
: neither

c*y2005-05-04 07:05

5 楼

thanks
it supports nested queries from 4.0, i'm using 3.21 :(
i figured it out by store the output from sub-query into an array

【在 s***e 的大作中提到】

: I know nothing about mysql. hehe.
: Just checked its documents, it seems mysql supports subqueries now.
: If so, you could write in this way:
: select distinct bookid from table where
: bookid not in ( select distinct bookid from table where dataname = 'title')